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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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
72
Replies
12
Views
355
Replies
26
Views
314
Replies
18
Views
229
Replies
7
Views
83

1,128,107
Messages
5,628,729
Members
416,333
Latest member
Time2Learn

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.

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