Formating with a formula
 

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-H100,(F10-H10) &" LOSS",(H10-F10) & " PROFIT")

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

How do I do this in excel??

Formating with a formula
 

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")
--
Kevin Backmann


"loulou" wrote:

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-H100,(F10-H10) &" LOSS",(H10-F10) & " PROFIT")

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

How do I do this in excel??

Formating with a formula
 

Try this one:
=TEXT(F10-H10,"$0.00")&IF(F10-H100," 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.

Formating with a formula
 

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

(this gives 2 decimal places)

Andrew


loulou wrote:
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-H100,(F10-H10) &" LOSS",(H10-F10) & " PROFIT")

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

How do I do this in excel??