formula & formatting help

rc39452

Board Regular
Joined
Nov 9, 2004
Messages
77
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!........ :p

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."
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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?
 
Upvote 0
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.
 
Upvote 0
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."
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top