leopardhawk

Well-known Member
Hello forum friends, this is probably a long shot but I am hoping that there is a way, even if it involves some VBA code, to link adjacent cells within two columns so that they are always side-by-side but ONLY after the first column has a value in it and ONLY if necessary.

To (hopefully) explain... the two columns in question are AGE and Percentage* shown below. As you can see from the formulas in the AGE column, the column won't contain any values until the user enters a date in "personal_info!\$E\$9". As long as the user enters a Date of Birth making them less than 71 years of age the two columns (AGE and Percentage) the AGE column will always start with 70 (in C14) and the Percentage * column will always start with 0.05 (in D14) so they coincide with each other. The percentages shown in Column D are 'fixed' in that they each relate to a particular age, from 70 years old and up. i.e. the percentage for a person who is 80 years old should ALWAYS be 0.0682.

My issue arises when, if the user enters a date in "personal_info!\$E\$9" making them 80 years old today, the way it is now, the AGE column will show 80 in C14 but D14 will still show 0.05 and I would like D14 to then show 0.0682 which is the correct percentage for a person that old. Of course, all the other cells in Column D would also show the correct percentage relative to the AGE of the user. I hope this makes sense and that there is a solution. Let me know if you need any further information. Appreciate any and all suggestions. Thanks!

Cell Formulas
RangeFormula
B14B14=IF(personal_info!\$E\$9="","",MAX(YEAR(TODAY()),YEAR(personal_info!\$E\$9)+70))
C14C14=IF(personal_info!\$E\$9="","",MAX(70,YEAR(TODAY())-YEAR(personal_info!\$E\$9)))
D14D14=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("70",RRIF_Factors[Age (at start of year)],0))
B15:C25B15=IFERROR(B14+1,"")
D15D15=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("71",RRIF_Factors[Age (at start of year)],0))
D16D16=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("72",RRIF_Factors[Age (at start of year)],0))
D17D17=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("73",RRIF_Factors[Age (at start of year)],0))
D18D18=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("74",RRIF_Factors[Age (at start of year)],0))
D19D19=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("75",RRIF_Factors[Age (at start of year)],0))
D20D20=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("76",RRIF_Factors[Age (at start of year)],0))
D21D21=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("77",RRIF_Factors[Age (at start of year)],0))
D22D22=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("78",RRIF_Factors[Age (at start of year)],0))
D23D23=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("79",RRIF_Factors[Age (at start of year)],0))
D24D24=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("80",RRIF_Factors[Age (at start of year)],0))
D25D25=INDEX(RRIF_Factors[Minimum (percentage)],MATCH("81",RRIF_Factors[Age (at start of year)],0))

leopardhawk

Well-known Member
@Fluff I changed the RRIF_Factors[Minimum (percentage) column in my Power Query to 'Whole Number'and then went back to your formula in post #11 and everything is working now. Of course, I had to swap the two ranges around as you alluded to in post #14. Anyway, all is good, thanks so much for your help!!!

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Dave Patton

Well-known Member
Your commentary and expected results may not be consistent with CRA rules.

leopardhawk

Well-known Member
@Dave Patton with all due respect, I'm not sure what you mean. What commentary and what results?

Last edited:

Fluff

MrExcel MVP, Moderator
Glad you sorted it & thanks for the feedback.

Dave Patton

Well-known Member
Your commentary is post #1 including "My issue arises when, if the user enters a date in "personal_info!\$E\$9" making them 80 years old today, the way it is now, the AGE column will show 80 in C14 but D14 will still show 0.05 and I would like D14 to then show 0.0682 which is the correct percentage for a person that old. Of course, all the other cells in Column D would also show the correct percentage relative to the AGE of the user."

1. Can you show why 0.0682 is correct if the person is 80 today. How is that number used?
2. Did you factor in the optional rate reduction for 2020.

Many formulas can yield a number. If one wants to use the a correct number or factor, the calculation would follow the defined rules.

I will assume that somewhere in your exercise you determine the correct factors and adjust to your actual requirements.

leopardhawk

Well-known Member
@Dave Patton

1. When an individual in Canada owns a RRIF and the individual is 80 years old, they are required by law to withdraw an amount equal to 0.0682 percent of their RRIF the following year.

Your own post #13 has the exact same amount... see Named aRRIF.
If the rates are for residents of Canada, the rates that I used are from CRA.
I extended the table to age 110. I took a second and put the rates on the sheet.
Why not use numbers instead of text?
You can convert the range F9 and name the information say aRRIF (see example).

T202012c.xlsm
ABCDE
1DOBYearAge Jan 1RRIF %
25-May-392020806.82%6.82%
35-May-14202010520.00%20.00%
1d
Cell Formulas
RangeFormula
C2:C3C2=DATEDIF(A2,DATE(B2,1,1),"Y")
D2:D3D2=IF(AND(A2<>"",A2<DATE(YEAR(TODAY())-65,1,1)),LOOKUP(C2,\$M\$2:\$N\$47),"")
E2:E3E2=LOOKUP(C2,aRRIF)

Named aRRIF ={65,0.04;66,0.0417;67,0.0435;68,0.0455;69,0.0476;70,0.05;71,0.0528;72,0.054;73,0.0553;74,0.0567;75,0.0582;76,0.0598;77,0.0617;78,0.0636;79,0.0658;80,0.0682;81,0.0708;82,0.0738;83,0.0771;84,0.0808;85,0.0851;86,0.0899;87,0.0955;88,0.1021;89,0.1099;90,0.1192;91,0.1306;92,0.1449;93,0.1634;94,0.1879;95,0.2;96,0.2;97,0.2;98,0.2;99,0.2;100,0.2;101,0.2;102,0.2;103,0.2;104,0.2;105,0.2;106,0.2;107,0.2;108,0.2;109,0.2;110,0.2}

2. No. There is no law requiring me to do that.

Your commentary and expected results may not be consistent with CRA rules.

3. Your comments about 'following defined rules' and 'may not be consistent with CRA rules' are both unfounded, uncalled for, and rude. You know nothing about me or my workbook or the use that it is intended for, nor do I feel the necessity to explain it to you. It feels like you have singled me out for this attack and for reasons that remain a mystery. Perhaps it's because I didn't immediately jump on your suggestion and implement it within my workbook?

Replies
8
Views
282
Replies
2
Views
407
Replies
4
Views
124
Replies
9
Views
759
Replies
1
Views
348

1,127,768
Messages
5,626,754
Members
416,202
Latest member
donya ba

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