Using nested function with SUM & ROUND

[Burrgogi]

In one of the cells in my worksheet, I have entered the following formula:
=CONCATENATE("Qtr 1 Average: $"&SUM(B2+C2+D2)/3)&" ("&SUM(B3+C3+D3)&" gal used)"

which gives me the following:
Qtr 1 Average: $53.9066666666667 (64.39 gal used)

I'd like the number that follows the word "average" to be rounded off to 2 digits so that the result reads:
Qtr 1 Average: $53.91 (64.39 gal used)

I know there is such a thing as the ROUND function, but I can't figure out how to incorporate it into my current formula. If anyone has some alternative solutions, I'd be curious about that as well. Also, is there a way to bold just the average number instead of bolding the entire cell?

 

[nmr4u]

Try this out!
=CONCATENATE("Qtr 1 Average: $"&ROUND(SUM(B2+C2+D2)/3),2)&" ("&SUM(B3+C3+D3)&" gal used)"

 

[joeu2004]

There reason to use SUM in this case, in the first place. Simply write:

=CONCATENATE("Qtr 1 Average: $"&ROUND((B2+C2+D2)/3,2)&" ("&ROUND(B3+C3+D3,2)&" gal used)"

Or you might consider the following for large sets of data:

=CONCATENATE("Qtr 1 Average: $"&ROUND(SUM(B2:D2)/3,2)&" ("&ROUND(SUM(B3:D3),2)&" gal used)"


I suggest that you explicitly round even the simple sum (B3+C3+D3) because Excel arithmetic is notorious for producing "binary arithmetic anomalies". That is, we might expect 64.39 based on decimal arithmetic, but Excel returns 64.3900000000001 or 64.3899999999999 because more decimal fractions cannot be represented in 64-bit binary floating-point exactly.

 

[Fazza]

there are also other functions - DOLLAR and TEXT - that may suffice

="Qtr 1 Average: "&DOLLAR(AVERAGE(B2:D2),2)&" ("&TEXT(SUM(B3:D3),"#,##0.00")&" gal used)"

 

[joeu2004]

Or you might consider the following for large sets of data:
=CONCATENATE("Qtr 1 Average: $"&ROUND(SUM(B2:D2)/3,2)&" ("&ROUND(SUM(B3:D3),2)&" gal used)"

I should have written:

=CONCATENATE("Qtr 1 Average: $"&ROUND(AVERAGE(B2:D2),2)&" ("&ROUND(SUM(B3:D3),2)&" gal used)"

 

[Burrgogi]

there are also other functions - DOLLAR and TEXT - that may suffice

="Qtr 1 Average: "&DOLLAR(AVERAGE(B2:D2),2)&" ("&TEXT(SUM(B3:D3),"#,##0.00")&" gal used)"

Great combination there Fazza - I completely forgot about using the AVERAGE function and using that with the DOLLAR function did the trick. Thank you!

I should have written:=CONCATENATE("Qtr 1 Average: $"&ROUND(AVERAGE(B2:D2),2)&" ("&ROUND(SUM(B3:D3),2)&" gal used)"

Thank you joeu2004! Your formula worked as well. The worksheet that I created is a very simple one in that it tracks how much I've spent on car fuel each month (and gallon consumption). in the future, if I ever have to work with a larger data set, I'll keep your post in mind. By the way, Excel tells me you're missing a closing parenthesis at the end of your formula. The tail end of it should read:

&" gal used)")

You guys are great. In fact, all the people that have helped me in the past are so knowledgeable and kind that I can't thank you enough.

 

[joeu2004]

By the way, Excel tells me you're missing a closing parenthesis at the end of your formula.

I had meant to omit the leading "CONCATENATE(", which is redundant. You use "&" for concatenation.