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

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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

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

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@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
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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

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

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
I’m not in front of my computer at the moment but I feel like that has to do with bracket placement.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,593
Members
417,154
Latest member
gm_jagath

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
Top