Maintaining Format in a Cell when using a formula

[Cardinal_2001]

I just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.

Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)

The data looks like this:

A1 = formula resulting in 1.76%
B1 = formula resulting in 1.90%
C1 = formula resulting in 1.69%

D1 = C1*B1/A1 resulting in 1.8218147382920100%

The desired result is 1.82%.

 

[Smitty]

Welcome to the Board!

If you reapply the % format after putting the formula in D1 it should stick.

No idea why it loses its format though.

 

[Cardinal_2001]

Thanks for the quick reply and the welcome!

It does stick afterwards, but it is rather annoying to redefine the format to each cell where I have to apply this calculation.

 

[Cardinal_2001]

One more note: the final formula calculation will work with number inputs (i.e. instead of 1.76% use .0176) but if I have to display those other numbers as a percent, it just creates a problem where I have to change the format on the inputs manually anyhow.

I am sure there is a reason Excel cares about the format of a cell when it regards percentages instead of utilizing the underlying data within a formula - I just cannot think of one at the moment.

 

[Smitty]

I am sure there is a reason Excel cares about the format of a cell when it regards percentages instead of utilizing the underlying data within a formula - I just cannot think of one at the moment.

I asked someone else, and we both agreed that it is default behavior. It's just that neither of us could remember why. I'll post back if I do recollect.