Need advice on division of two INDEX/MATCH amounts

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, this is a request for advice on how to best divide two amounts that are both calculated as dollar amounts and then display the result as a percentage. The two Index/Match functions are together within a CONCATENATE formula and they both work fine. Below are the two Index/Match functions that I am trying to divide and below that is the entire CONCATENATE formula. The two index/match functions I'm trying to divide begin right after the words "which is" in the CONCATENATE formula. I have tried several different approaches but keep running into errors. Any advice is much appreciated. Thanks!

"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("Average CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&"

divided by

"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&"

Code:
=CONCATENATE("For "&YEAR(TODAY())&", the maximum CPP monthly benefit is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&" (calculated as 25% of 1/12 of the average year's maximum pensionable earnings (YMPE) for the last 5 years).  For "&YEAR(TODAY())&", the average monthly amount that Canadians are receiving is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("Average CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&" which is "&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("Average CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&"/"&TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")&" of the maximum monthly payment.","  While this percentage may seem low, the good news is that there are a couple of provisions which may increase your CPP payments, in some cases substantially.","  The general drop-out provision allows up to eight years of your lowest earnings to be dropped from the calculation and is a benefit to all CPP contributors.","  Another piece of good news is that the CPP is indexed to inflation.  CPP monthly benefits are adjusted every year in January based on the Consumer Price Index (CPI).")
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,642
Office Version
  1. 365
Platform
  1. Windows
You need to do the calculation proper not as text, then format the result with the text function if necessary.
Excel Formula:
TEXT(INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("Average CPP",CPP_OAS[Federal Programs (dollars)],0))/INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0)),"$#,##0.00")
Although personally I would add a new column to the CPP_OAS table, do the division there for every row then refer to that in the formula instead.
 
Solution

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Create 2 new names that refer to the 2 INDEX/MATCH formulas and use those names in the formula you posted.

For example you could create a name AverageCPP and set it to refer to this formula:

=INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("Average CPP",CPP_OAS[Federal Programs (dollars)],0))

Similarly yo could create a name CPP and set it to refer to this formula:

=INDEX(CPP_OAS[Maximum Monthly Amounts],MATCH("CPP",CPP_OAS[Federal Programs (dollars)],0))

Alternatively put the formulas in (hidden?) cells and name those cells AverageCPP and CPP respectively.

Whichever you use you can them shorten your formula to this:
Excel Formula:
=CONCATENATE("For "&YEAR(TODAY())&", the maximum CPP monthly benefit is "&TEXT(CPP,"$#,##0.00")&" (calculated as 25% of 1/12 of the average year's maximum pensionable earnings (YMPE) for the last 5 years).  For "&YEAR(TODAY())&", the average monthly amount that Canadians are receiving is "&TEXT(AverageCPP,"$#,##0.00")&" which is "&TEXT(AverageCPP/CPP,"$#,##0.00")&" of the maximum monthly payment.","  While this percentage may seem low, the good news is that there are a couple of provisions which may increase your CPP payments, in some cases substantially.","  The general drop-out provision allows up to eight years of your lowest earnings to be dropped from the calculation and is a benefit to all CPP contributors.","  Another piece of good news is that the CPP is indexed to inflation.  CPP monthly benefits are adjusted every year in January based on the Consumer Price Index (CPI).")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,863
Messages
5,638,742
Members
417,049
Latest member
baka416

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