Need advice on division of two INDEX/MATCH amounts

[leopardhawk]

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")&"

=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).")

 

[jasonb75]

You need to do the calculation proper not as text, then format the result with the text function if necessary.

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]

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:

=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]

@jasonb75 okay, that works just fine! Thank you so much.