Formula results wrong when using units

Question

Level 1

14 points

Formula results wrong when using units

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

Posted on Mar 23, 2011 2:00 PM

Reply

Answer 1

Best reply

Martin Pace

Level 5

5,115 points

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.

Reply

Answer 2

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.

Reply

Answer 3

hoppah Author

Level 1

14 points

Mar 23, 2011 3:11 PM in response to McFool123

Dollars divided by dollars does not equal dollars. 10 dollars divided by 5 dollars is 2, not 2 dollars. Unit math all works this way - like 20 dollars divided by 2 days equals 10 dollars per day (dollars/day), et cetera. And it's not easy to strip - the workaround of not using dollars as a cell format is currently the only possibility I can see.

As for "where is the question", this is a discussion forum. So I started a discussion, to see what opinions others have on this topic and see if there might be something I've missed.

Reply

Answer 4

jaxjason

Level 5

5,047 points

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

Reply

Answer 5

hoppah Author

Level 1

14 points

Mar 23, 2011 8:17 PM in response to jaxjason

So what is the "default cell format" in Numbers? The result of the $/$ calculation is clearly defaulting to currency. No matter what it is, it shouldn't assume $/$=$.

I found the format setting and changed it for the result cell - thanks for the info!

H.

Reply

Answer 6

hoppah Author

Level 1

14 points

Mar 23, 2011 8:22 PM in response to jaxjason

I spoke too soon - although the result can be shown as a number by changing the cell format, the result itself has an inherent format (which it does not in Excel), which keeps me from using it directly in another formula. For example, if I use the result of the calculation (a currency/currency calculation that results in a number of days for a payoff) in a DURATION formula, the DURATION formula errors out and states: "Argument # of DURATION can't be a currency."

So again, Numbers is behaving incorrectly - and differently than the proper behavior in Excel.

H.

Reply

Answer 7

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.

Reply

Answer 8

hoppah Author

Level 1

14 points

Mar 24, 2011 8:45 AM in response to robdecline

I don't want to create a second results cell referencing D2. I want D2 to be the results cell containing something like:

DURATION(B2/C2)

which fails with the error I listed regardless of D2's format, showing that the result of B2/C2 is a currency, which it is not in Excel.

Indeed I can utilize another cell as you state - but I shouldn't have to.

H.

Reply

Answer 9

jaxjason

Level 5

5,047 points

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

Reply

Answer 10

hoppah Author

Level 1

14 points

Mar 24, 2011 10:12 AM in response to jaxjason

OK - thx again for all the info, everyone. I'll leave feedback at apple.com.

I realize after re-reading the thread I sounded like a broken record on Excel. I am not a fanboy, just trying to point out the difference I noticed. I'd have to say that absent proper unit handling, handling all data as unitless is probably the way to go.

Thanks again,

H.

Reply

Answer 11

jaxjason

Level 5

5,047 points

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

Reply