Trying to SUM two amounts in a formula that are calculated in the same formula

[leopardhawk]

Hello forum friends, I have a complex formula that works well for what I am trying to do but I would like to be able to SUM two amounts that are calculated within the same formula. I have pulled out a small section of the formula below:

=CONCATENATE(""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&" for a total of $750.00.")

What I end up with is...

"Their annual PRB at age 67 is $391.29 + $382.38 for a total of $750.00."

What I would like to do is replace the $750.00 with the SUM of the first two dollar amounts (which is $773.67). The first two dollar amounts are also dynamic, depending on what is in the power query (CPP_OAS).

Appreciate any ideas or suggestions!

 

[Candyman8019]

try the following:

=CONCATENATE(""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&" for a total of $"& (INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"$#,##0.00") + INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&".")

 

[leopardhawk]

@Candyman8019 thanks for the idea. I copied your formula into a blank cell to test it and while I didn't receive an error, all that is displayed in the cell is the formula, not the result.

 

[Candyman8019]

Oh...I failed to removed the text format information in the previous formula...try this one:

=CONCATENATE(""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&" for a total of $"& (INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084)) + INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084)&".")

 

[leopardhawk]

@Candyman8019 same result. I appreciate your efforts though! I was thinking the solution would be easier.

 

[Candyman8019]

Rather than just paste it into a cell, paste it into the edit bar at the top of the screen. Sometimes when pasting this sort of thing it takes it as text for some reason.

 

[leopardhawk]

@Candyman8019 wow, now I am getting an error ("There's a problem with this formula."), you know, the one that asks you if you're actually trying to type a formula? I can see that you have removed the TEXT portion in your suggestion but for some reason, it doesn't like it.

 

[Candyman8019]

You may need to play around with the formula depending on data formats, you might need to add the TEXT clauses back in. Essentially all we're doing is taking the parts of the formula that originally displayed the two values as text and add them together at the end.

Maybe give something like this a try:
=CONCATENATE(""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 67 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084),"$#,##0.00")&" + "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084,"$#,##0.00")&" for a total of "& text((INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0233*(0.025*12*1.084)) + INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*0.025*12*1.084),"$#,##0.00")&".")

Here I wrapped the calculation in a TEXT clause.

 

[leopardhawk]

@Candyman8019 I certainly will play with it a bit, thanks for your efforts. Your latest formula gives a different error ("You've entered too few arguments for this function.")

 

[Candyman8019]

I’m not in front of my computer at the moment but I feel like that has to do with bracket placement.