Formating with a formula

L

loulou

I have a formula and then a label concatenated onto the end which works fine
but now I realize I have to format the numeric portion since it is working
with too many decimals. I know how to do this in access but not excel.

The formula is:
=IF(F10-H10>0,(F10-H10) &" LOSS",(H10-F10) & " PROFIT")

In msaccess, I would just add:
=IF(F10-H10>0,format((F10-H10),"fixed") &" LOSS",format((F10-H10),"Fixed") &
" PROFIT")

How do I do this in excel??
 
K

Kevin B

Convert the subtraction to a string and then format the string using a
format mask.

Your True/False statements currenlty look like this:

(F10-H10) &" LOSS",(H10-F10) & " PROFIT"

Change them to:

TEXT((F10-H10),"$#,##0.00") &" LOSS",TEXT((H10-F10),"$#,##0.00") & " PROFIT")
 
D

Dave O

Try this one:
=TEXT(F10-H10,"$0.00")&IF(F10-H10>0," Loss"," Profit")

How likely is it that F10-H10 will equal zero? In this scenario, a
result of zero will be labeled as "Profit", just fyi.
 
A

Andrew Taylor

Loulou, try
=TEXT(ABS(F10-H10),"0.00") & IF(F10-H10>0," LOSS"," PROFIT")

(this gives 2 decimal places)

Andrew
 
Top