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]

Ah. In that case you need to add ,2 before closing your round clause brackets. Which says to round to 2 decimal places. Take a look at the original formula and you’ll see what I’m talking about.

 

[leopardhawk]

@Candyman8019 I tried that before I reached out to you regarding this latest issue. The formula works with no errors (albeit with the 10 decimal places), I added the ,2 in the designated place and I immediately get an error (There's a problem with this formula). I don't understand why... Must be something that I am missing???

 

[Candyman8019]

I’ll take a look at it closer in the morning. I’m sure it’s a combination of needing the ,2 and bracket placement.

 

[leopardhawk]

@Candyman8019 Thanks, I appreciate it...

 

[Candyman8019]

Give this one 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 $"&ROUND((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,2)&".
", ""&IF(personal_info!F9="M","His",IF(personal_info!F9="F","Her","Their"))&" annual PRB at age 68 is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),"$#,##0.00")&" + $"&ROUND((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,2)&" for a total of $"&ROUND((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+INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))*1.0471*(0.025*12*1.084),2)&".")

 

[leopardhawk]

@Candyman8019 How does that saying go? 'He couldn't see the forest for the trees...'

It's amazing how a simple thing like a bracket in the wrong place can wreak so much havoc!!! I see now how simple of a fix it was and again, I appreciate you helping me out.

 

[Candyman8019]

Any time. Glad I could help.