# formula & formatting help

#### rc39452

##### Board Regular
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."

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Dan_WI

##### New Member
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
thanks for the reply I'm trying it now.

#### rc39452

##### Board Regular
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

It should have

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

and post the result

#### rc39452

##### Board Regular
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
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

##### Board Regular
thanks Dan...!!! that works perfectly.

Replies
4
Views
158
Replies
3
Views
97
Replies
68
Views
598
Replies
0
Views
77
Replies
3
Views
90

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,844
Messages
5,766,748
Members
425,378
Latest member
kapoor2892

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

### Which adblocker are you using?

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

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