# 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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### 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
279
Replies
3
Views
195
Replies
0
Views
94
Replies
2
Views
239
Replies
68
Views
1K

1,181,257
Messages
5,928,977
Members
436,637
Latest member
pcpc

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