# decimal places problem in excel

jonnyg111 16:52 11 Apr 07
Locked

I can't get a cell to give me the answer to a formula to 2 dec places. I go to Format > Cells > Number > 2 decimal places, but it has no effect. The sample in Format Cells is still to 10 dp, and I keep getting the answer on the spreadsheet also to 10 dec places. Neither does clicking the 000 up or down buttons on the toolbar help. I would be v. grateful for advice.

Jonny

PA28 16:58 11 Apr 07

Just tried it in 2000 and no problems. Is it happening on the answer box or on all? If the former, the equation cell isn't overwriting the formatting is it (by being copied from somewhere else, for example)? Try specifically formatting just that one cell to 2 places as the very last thing you do on the spreadsheet.

jonnyg111 18:01 11 Apr 07

Thanks PA28. The formula in the offending cell is this:

=CONCATENATE("=",(B10*I10+(100.5+23*C10)))

If I remove the CONCATENATE bit and reduce it to:

=B10*I10+(100.5+23*C10)

the 2dp request works fine. I am fairly happy with this rewrite, but I would ideally like to able to use CONCATENATE if anyone knows a way round this problem. I am in Excel 2003.

VoG II 18:08 11 Apr 07

CONCATENATE produces a Text value. Changing the number of decimal places will have no effect on how it appears.

I fail to see why you are using CONCATENATE with the remainder of that formula.

An example of using CONCATENATE would be

=CONCATENATE(TEXT(B10*I10+(100.5+23*C10),"0.00"), " kilograms per fortnight")

Simsy 18:35 11 Apr 07

what you're after...

Do you want the equals sign to be dislayed?

Anyway, I've got an older Excel so I can't be sure, but look in Tools>Options>Edit and play with the fixed decimal places option. Perhaps this is having an effect...

Also, check Tools>Options>Calculation and look at the "precision as displayed" and see if that is having an effect.

(But from what you describe I suspect not!)

Good luck,

Regards,

Simsy

VoG II 18:38 11 Apr 07

To display the equals sign together with the result to 2 decimal places:

=CONCATENATE("=",TEXT(B10*I10+(100.5+23*C10),"0.00"))

jonnyg111 23:16 11 Apr 07

Thanks everybody: yes, I did want that equals sign to be displayed, and VoG's solution is great.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac