Adding Commas and rounding percentages WITHIN a CONCATENATE formula?

[jeffcoleky]

I use the following formula in a real estate settlement statement to display the real estate agent's sales commission, and what percentage that commission is of the sales price. It is calculated based upon two values in Sheet1.

=CONCATENATE("Total Sales/Broker's Commission based on price:"," ","$",'Sheet1'!C23, "     ", "@","    ",'Sheet1'!G51,"%")

C23's code just has the real estate sales price listed. Eg. 100000.
G51's code calculates the percent commission using the following formula which formula: =G47*100/C23 (G57 is the dollar amount that the agent is being paid)

My problem is that my current CONCATENATE formula will NOT:
1) Round to the nearest tenth decimal place.
2) Add a "," in the dollar amounts.

Currently, a purchase price (Sheet1!C23) of $80,000 with a commission of $1,350 (Sheet1!G51) would result in:

"Total Sales/Broker's Commission based on price: $80000 @ 1.6875%"

Instead, I would want it to look like this:

"Total Sales/Broker's Commission based on price: $80,000 @ 1.7%"

Is this possible to do within the same formula? Or, what must I change in Sheet1 to make this happen?

 

[rallcorn]

The key is using "Text" to format:

="Total Sales/Broker's Commission based on price: "&TEXT(Sheet1!C23,"$#,###")&" @ "&TEXT(Sheet1!G51,"0.0%")

You'll want to edit your G51 formula to be:
=G47/C23

 

[Marcol]

Or, without changing G51

=CONCATENATE("Total Sales/Broker's Commission based on price:"," ",TEXT(Sheet1!C23,"$#,###"), "     ", "@","    ",ROUND(Sheet1!G51,1),"%")

Alternatively write it this way.

="Total Sales/Broker's Commission based on price: "&TEXT(Sheet1!C23,"$#,###")&"     @    "&ROUND(Sheet1!G51,1)&"%"

 

[jeffcoleky]

All of those formulas worked great! Thank you both for teaching and creating those for me!