formula & formatting help

[rc39452]

I have a couple of questions regarding the formula below.
1- Is there anyway I can format the numbers? ex. ##,### (i need to add
the commas).
2- Is there anyway to streamline the formula?

Any help is appreciated!........

This is the results:

Outcollect Revenue increased $564K from November 04 ($3077K) to December 04 ($3641K). The MOUs increased 9180K from November 04 (35360K) to December 04 (44540K) resulting in an increase of $799K. The rate decreased $0.01 resulting in a decrease of $235K.

This is the formula used to get these results:

="Outcollect Revenue"&" "&W151&" "&"$"&ROUND(ABS(H151),-3)/1000&"K"&" "&"from"&" "&$E$7&" "&"("&"$"&ROUND(ABS(E151),-3)/1000&"K)"&" "&"to"&" "&$F$7&" "&"($"&ROUND(ABS(F151),-3)/1000&"K)."&" "&"The MOUs"&" "&X151&" "&ROUND(ABS(M151),-3)/1000&"K"&" "&"from"&" "&$E$7&" "&"("&ROUND(ABS(K151),-3)/1000&"K)"&" "&"to"&" "&$F$7&" "&"("&ROUND(ABS(L151),-3)/1000&"K)"&" "&"resulting in"&" "&IF(X151="increased","an increase","a decrease")&" "&"of"&" "&"$"&ROUND(ABS(U151),-3)/1000&"K"&"."&" "&"The rate"&" "&Y151&" "&"$"&ROUND(ABS(R151),2)&" "&"resulting in"&" "&IF(Y151="increased","an increase","a decrease")&" "&"of"&" "&"$"&ROUND(ABS(T151),-3)/1000&"K."

 

[Dan_WI]

1 -

Try either Dollars() or fixed() So replace round with Fixed(abs(etc),0,false) or dollars(abs(etc),0)

2 - as for cleaning it up, you could try breaking it into multiple cells?

 

[rc39452]

thanks for the reply I'm trying it now.

 

[rc39452]

RE:
1 -Try either Dollars() or fixed() So replace round with Fixed(abs(etc),0,false) or dollars(abs(etc),0)

2 - as for cleaning it up, you could try breaking it into multiple cells?

Dan, I tried both of your suggestions and neither added the needed comma.

 

[Dan_WI]

It should have

post what you have between one of the "& and &"

and post the result

 

[rc39452]

The outcome didn't change. i think something like "##,###" has to be there somewhere. Just a guess?

="Outcollect Revenue"&" "&W151&" "&"$"&fixed(ABS(H151),-3)/1000&"K"&" "&"from"&" "&$E$7&" "&"("&"$"&dollar(ABS(E151),-3)/1000&"K)"&" "&"to"&" "&$F$7&" "&"($"&ROUND(ABS(F151),-3)/1000&"K)."&" "&"The MOUs"&" "&X151&" "&ROUND(ABS(M151),-3)/1000&"K"&" "&"from"&" "&$E$7&" "&"("&ROUND(ABS(K151),-3)/1000&"K)"&" "&"to"&" "&$F$7&" "&"("&ROUND(ABS(L151),-3)/1000&"K)"&" "&"resulting in"&" "&IF(X151="increased","an increase","a decrease")&" "&"of"&" "&"$"&ROUND(ABS(U151),-3)/1000&"K"&"."&" "&"The rate"&" "&Y151&" "&"$"&ROUND(ABS(R151),2)&" "&"resulting in"&" "&IF(Y151="increased","an increase","a decrease")&" "&"of"&" "&"$"&ROUND(ABS(T151),-3)/1000&"K."

 

[Dan_WI]

the /1000 is what is wrong

Move the /1000 to inside the abs() formula. Otherwise you are undoing the formating with the /1000 ( I think??)

So for example &DOLLAR(ABS(B1/1000),0)&"K" should now work

 

[rc39452]

thanks Dan...!!! that works perfectly.