Formatting a number within concatenation

[aaronmberg]

Here is part of an If statement that I am using.

=IF(AND(B45<0,B45<(E45+F45)),"BUY"&" "&ROUND(ABS($B45-(E45+$F45)),0)

If this is true and it returns "BUY ####", how can I format the number to have a comma separating thousands (#,###)?

Thanks in advance!

 

[barry houdini]

Try something like

=IF(AND(B45<0,B45< E45+F45),"BUY "&FIXED(ABS($B45-E45-$F45),0),0)

 

[Artemus]

You could also use

=IF(AND(B45<0,B45< E45+F45),"BUY "&TEXT(ABS($B45-E45+$F45),"#,###"),0)

 

[aaronmberg]

Thanks! That should work nicely!

 

[barry houdini]

Given your conditions $B45-E45-$F45 will always be negative when the IF condition is true so you don't really need ABS, you could use

=IF(AND(B45<0,B45< E45+F45),"BUY "&FIXED(E45+$F45-$B45,0),"")