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

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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")&".")
 
Upvote 0
@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.
 
Upvote 0
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)&".")
 
Upvote 0
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.
 
Upvote 0
@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. o_O
 
Upvote 0
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.
 
Upvote 0
@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.")
 
Upvote 0
I’m not in front of my computer at the moment but I feel like that has to do with bracket placement.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top