Getting commas and two place cents from my IF statement

[BrianHall]

I have the following IF statement that is not producing commas or two place cents.

=IF(AND(B88>-100,B88<100),"No Difference",IF(AND(B88>-100000,B88<100000),"Immaterial Difference",IF(AND(B88<-1000000,B88>1000000),B88&"Difference",B88&"Difference")))

This is producing a number like -27651083.8250041 Difference

I need it to produce a number like
-27,651,083.83 Difference

Any help would be greatly appreciated.

Thank you

 

[Joe4]

Change these parts:

[COLOR=#333333]B88&"Difference"

to
[/COLOR]

[COLOR=#333333]TEXT(B88,"#,##0.00") & "Difference"[/COLOR]

 

[godsaaint]

You have to format the cells, however, don't choose "Number" directly, you have to click "More number formats" and in the number category, check the "Use 1000 Separator" and type 2 in the box for decimal places.

 

[Joe4]

You have to format the cells, however, don't choose "Number" directly, you have to click "More number formats" and in the number category, check the "Use 1000 Separator" and type 2 in the box for decimal places.

That won't work. The issue is his formula returns Text, not numbers. So you need to format the numeric part of the text right in the formula.
You can do that with the TEXT function, like I showed above.

 

[godsaaint]

Oh I see, you are right. I didn't notice the "Difference" in the result he was getting.

 

[Scott Huish]

Since the last two results are the same, the only things that need to be checked are the first two conditions which can be shortened with the use of ABS.

Does this work for you?

=IF(ABS(B88)<100,"No Difference",IF(ABS(B88)<100000,"Immaterial Difference",TEXT(B88,"#,###.00")&" Difference"))

 

[BrianHall]

Works great.

Thank you