Formatting Number in Excel

[rjm65]

In Cell B1, my formula is ="Traditional Bookcase (Flush Cut $"&(ROUND(F1*10),2)&" per side)")

F1 has a multiplier the user enters, F1 is formatted as a number with 2 decimal places.

Depending on the number I enter in F1, I get answers that vary in length, I would like them to always appear formatted as $22.40, $22.00, etc. Currently, the answer would be appear as $22.4 or $22. I thought setting the NUM_DIGITS in ROUND to 2 would fix that, but it made no difference.

I have tried changing the format of B1 from text to number with no affect. Any ideas?

Thanks,
Raymond

 

[ManxDom]

try using this formula instead in cell B1

="Traditional Bookcase (Flush Cut  $"&(FIXED(ROUND(F1*10,2),2,FALSE)&"  per side)")

Dom

 

[KenWright]

="Traditional Bookcase (Flush Cut  $"&TEXT(ROUND(F1*10,2),"0.00")&"  per side)"

The value in F1 is supposed to be multiplied by 10 yes??

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[HDRules]

Try this instead:

="Traditional Bookcase (Flush Cut $"& FIXED(F1*10,2)&" per side)

 

[ManxDom]

That simplifies it a lot HDRules ... nice one

it could be simplified even more though

="Traditional Bookcase (Flush Cut $"& FIXED(F1*10)&" per side)"

as the decimal places in the FIXED function defaults to 2 anyway

Dom

 

[rjm65]

THANK YOU!!!!!

All 3 posted solutions work!!!

Raymond

 

[KenWright]

One advantage of the TEXT function is that it allows you to use a variety of different formats.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------