Formula doesn't return same result as calculator

**OWhatAThrill** · 09-21-2010, 12:53 PM

I have a simple formula.

Where formula is: =T27/U28

T27 = 1195000
U28 = 9.9

The formula gives me: 120360
A calculator gives me: 120707

Difference of: 347

Another example of same formula:

Formula =W27/X28

W27 = 64060
X28 = 2.7

The formula gives me: 23916
A calculator gives me: 23725

Difference of 191.

As this is the column for average impressions a day, that's a whole lot of difference. What am I not understanding?

**zbor** · 09-21-2010, 01:05 PM

See is that 9.9 actually 9.92854 but showed with one decimal.

In another words - format it as number with several decimal places

**TMS** · 09-21-2010, 01:09 PM

Works for me.

**Richard Buttrey** · 09-21-2010, 01:09 PM

Hi,

I think you'll find U28 contains 9.92854769 and not 9.9. Look in the formula bar (next to the fx symbol) to see what's actually in the cell.

Format U28 to display more than 1 decimal place and you'll see what I mean. Ditto with X28

Rgds

**OWhatAThrill** · 09-21-2010, 02:12 PM

Figured that was it, but didn't want the long number to show on the spreadsheet. What would you do if it was your report? Leave it as is, manually calculate it and put that in?

**zbor** · 09-21-2010, 02:18 PM

No need... Just format cell as number with 1 decimal place...
You+ll see only one but whole number will be taken for calculation.

**OWhatAThrill** · 09-21-2010, 03:30 PM

Then why isn't it calculating right in the first place? The cell U28 is the result of a formula (=U27/7) but it is formatted for one decimal place...

**zbor** · 09-21-2010, 03:34 PM

You made some mistake...

If you have in some cell number formated with 1 decimal place.
And in next you make 1*that number... you+ll get whole number...

Please Login or Register  to view this content.

Formatting just shows as you like.. But it takes whole number...

For example, format cell as "ABC-"0.0 and it will still calculate it as 1,2131321321321 (instead of error that it's true text)

**OWhatAThrill** · 09-21-2010, 05:01 PM

Originally Posted by zbor

You made some mistake...

If you have in some cell number formated with 1 decimal place.
And in next you make 1*that number... you+ll get whole number...

Please Login or Register  to view this content.

Formatting just shows as you like.. But it takes whole number...

For example, format cell as "ABC-"0.0 and it will still calculate it as 1,2131321321321 (instead of error that it's true text)

I am a dunce. College degree and Marketing/IT background yet I don't understand a bit what you wrote...is there another way to explain that maybe I can grasp?

**shg** · 09-21-2010, 06:11 PM

The point is, Excel displays values with whatever precision you like, but always uses the underlying complete (15-digit) value for calculation.

The exception is if you select the option for Precision as displayed. I have never heard of a situation where it was correct to do it.

**Richard Buttrey** · 09-21-2010, 06:59 PM

I suppose the answer to your question lies in what the readers of your report will observe and question.

If you think they will make the same calculation as you did, either mentally or explicitly and worry about an apparent difference, then format the cell to show several decimal places so that they may understand.

If not then leave it as it is.

In the latter case it's then usual to add a note somewhere to tell the reader that 'some cells are only displaying one decimal place although calculations are made with the whole decimal.

HTH

**zbor** · 09-22-2010, 02:00 AM

Hi.. Check this example.. Hope it will help.

In A column you have different numbers (click select the cell and in Formula bar you will see full value)
But they all formated to one decimal place so they look same

B column is that number ^2
Therefore you get different results because there is different input (no matter that they look same).

In column D is opposite...

Same number differently formated will result with same output in column E.