# Need advice on division of two INDEX/MATCH amounts

#### leopardhawk

##### Well-known Member
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
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.

#### Norie

##### Well-known Member
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).")``

#### leopardhawk

##### Well-known Member
@jasonb75 okay, that works just fine! Thank you so much.

Replies
3
Views
79
Replies
12
Views
378
Replies
26
Views
330
Replies
18
Views
243
Replies
7
Views
91

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.

### Which adblocker are you using?

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

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