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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Dan_WI

New Member
Joined
Sep 1, 2004
Messages
18
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

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

New Member
Joined
Sep 1, 2004
Messages
18

ADVERTISEMENT

It should have

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

and post the result
 

rc39452

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

New Member
Joined
Sep 1, 2004
Messages
18
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
 

Forum statistics

Threads
1,148,055
Messages
5,744,544
Members
423,882
Latest member
Seeham

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
Top