Skip navigation

Formula results wrong when using units

1804 Views 11 Replies Latest reply: Mar 24, 2011 10:28 AM by jaxjason RSS
hoppah Level 1 Level 1 (0 points)
Currently Being Moderated
Mar 23, 2011 2:00 PM
I've not tried this on Numbers for OS X, but it's definitely broken on the iPad. When calculating results using a formula with units in the source data, the result is incorrect. An example: calculate the result of Cell A divided by Cell B when both cells contain a dollar amount. The result in Numbers will be a dollar amount - which is incorrect. The result should be a unitless scalar quantity. More detailed example - say you were trying to find out how many days it would take you to cost out a loss of $n if you saved $x/day. You should be able to create a cell with a formula dividing the $n by the $x and end up with a straight number you could then use in a date calculation. Instead the date function fails because of the dollar input.

This is causing me no end of headache, because removing the dollar format is not simple. Excel, in contrast, handles these calculations properly and returns straight scalars.
New (Aug 2010) Mac Pro, Mac OS X (10.6.4), 8-core Xeon, 16G ECC RAM, 0+1 RAID
  • Martin Pace Level 5 Level 5 (5,075 points)
    Currently Being Moderated
    Mar 23, 2011 2:43 PM (in response to hoppah)
    Is there a question here? If you are trying to provide Apple feedback you can do that at: http://www.apple.com/feedback/.

    I've not used numbers, but a work around might be to include the units in the cell's title and use unitless numbers in the cells. As a scientist/engineer I've always had to do this as Excel does not use kg, lbs, BTU, newtons, feet, etc.
  • McFool123 Level 1 Level 1 (55 points)
    Currently Being Moderated
    Mar 23, 2011 3:01 PM (in response to hoppah)
    I've not had a problem with this but money over money should still equal money and with it being an app rather than a computer program it may be simplified to do what it does rather than what you want. Also changing the cell isn't that hard so I don't see why just changing isn't that much of a problem.
    MBP 17", iPhone 3GS, iPad 2 32 Gig, Mac OS X (10.6.3), 8 Gig Ram Core i7 2.66
  • jaxjason Level 4 Level 4 (3,320 points)
    Currently Being Moderated
    Mar 23, 2011 6:47 PM (in response to hoppah)
    Hoopah
    numbers does exactly what I would expect, which is what you describe as proper behavior. It is up to you to decide and set format you want the result in.

    I just set two columns to dollars and the third to plain number. This isth e column that I place my formula in and it works fine. The reason excel seems to do this is that all cells in excel are in the general format. Which defaults to number format with the equation. It is NOT trying to do proper scientific units clculations, which is the behavior your describing. It's just following formatting, if the destination cell is formatted as dollars your equation will sow dollars.

    Jason
    Mac mini, iPhone, iPad, Mac OS X (10.6.4)
  • robdecline Calculating status...
    Currently Being Moderated
    Mar 23, 2011 8:53 PM (in response to hoppah)
    Worked for me.

    In B2 $1.00
    In C2 $2.00

    In D2 =B2/C2, which returns $0.50
    Format D2 as Number, D2 returns 0.50

    Now I can reference D2 as a number with functions, including DURATION, however I think the function you are looking for in Numbers is BONDDURATION (equivalent to DURATION in Excel).

    As for the discussion. I don't have strong feelings, but I think I tend to agree with you that $/$ should return a scalar. Maybe. Working around either seems to be simple enough, however.
    All Kinds
  • jaxjason Level 4 Level 4 (3,320 points)
    Currently Being Moderated
    Mar 24, 2011 9:50 AM (in response to hoppah)
    Excel formats only on results displayed, not the underlying data itself, thereby seeming to do what your saying it does correctly. It actually incorrectly Drops "units" you have specified. (you can prove this by formatting a cell as dollar or currency and typing in 12. It will display as $12.00 but when you click on the cell the value shown in above is 12 unitless)

    I *do agree* that the formula Should not kick out if the result of the internal calculation would result in a unitless number. Any valid number should be accepted.

    Please go to htp://www.apple.com/feedback and let them know your issue, thats the only way to have them look it over and fix it. They do not watch these forums.

    Thanks
    Jason
    Mac mini, iPhone, iPad, Mac OS X (10.6.4)
  • jaxjason Level 4 Level 4 (3,320 points)
    Currently Being Moderated
    Mar 24, 2011 10:28 AM (in response to hoppah)
    I tend to agree with the unitless concept.

    Interesting thing is this though. I just got a chance to break out my ipad during lunchtime and try the duration issue itself out (I dont think I had the duration portion known when i first posted).

    I dont get an error when using the division of two dollar values in the duration equation. I got 2w as my answer. (btw, duration in numbers is for formatting as a duration of weeks, months days... etc. didnt know if you realized that)

    I do get the error if I try to reference one fo the values without division, just the cell ref itself. I used "Value(B3)" and it converted to a base number and gave me my proper answer.

    Can you verify for me again please? I think you might be having another issue possibly.
    Thanks
    Jason
    Mac mini, iPhone, iPad, Mac OS X (10.6.7)

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.