Excel: Value shown isnt value used in formula...

[tektipsFriend]

I am dividing two numbers in a cell and then using the result of the division in another formula in another cell. The problem I'm having is that I am formatting the cell to only show 2 decimal places, however when i reference the cell from another cell I get the unformatted version.

For example let's say the result is 6.7568852 and the format i apply to the cell shows 6.76. I want to use 6.76 in the formula in another cell and not 6.7568852. Why doesnt the formatting of the cell carry over?

I hope I wasnt too unclear about what I was asking.

 

[AnotherHiggins]

Formatting a cell only changes how the number is DISPLAYED, not how it is stored.

If you want the formula in another cell to only consider two decimal places, you need to tell it that.

So use the ROUND function - either in the original cell or in the formula in another cell. ROUND(A1,2)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlennUK]

If you want the result to be 2 decimal places then round it using =ROUND(num1/num2,2)

Formatting just affects how a number looks and not how many decimal places there really are. If on the other hand you always want calculations to only use what is showing in the formatting you can set Tools/Options/Calculation/Precision As Displayed option on.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Welshbird]

If you only want 2 decimal places it may be a plan to 'round' the calculation then you'll not have the formatting issue.

=round(c2/b2,2) would probably give youthe answer you look for.

Formatting doesn't change the number - it only changes the view you see of the number.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[AnotherHiggins]

Wow - three responses in the same minute.... I don't see that very often.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Welshbird]

(And we all agreed! Must be a record)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SkipVought]

Where was I???

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Welshbird]

(helping everyone else. It's rare the rest of us get a look-in! )

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[AnotherHiggins]

Where was I???

I assumed you were finally taking a nap.

More likely what Fee said.

[small]*grumble grumble* Skip and his #$*&ing nimble fingers *grumble grumble*[/small]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

I get round that by using as few words as possible and no tgml ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question