Complicated formula

leopardhawk

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends!

Okay, this one is complicated (at least for me). However, I am hopeful that someone will be able to help me. There are three sheets involved and the formula in question resides on the ‘income analysis’ sheet in cell D97. The formula works fine for all intents and purposes however, during testing two separate and very strange problems have surfaced. I have tested two ways, by changing the DOB and by changing the system clock in my computer. Both ways of testing have revealed the exact same error. To explain:

On the first sheet (‘personal information’), cell E12 is where the user enters their DOB. In my example, I have intentionally left this field blank.

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #201116"><th></th><th>E</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">10</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;">Date of birth</td></tr><tr ><td style="color: #201116;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: rgb(248, 203, 173);;"></td></tr><tr ><td style="color: #201116;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td></tr></tbody></table><p style="width:12em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0;color: #201116">personal information</p><br /><br />Below is the 'rrif' sheet where the data resides.

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #201116"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(189, 215, 238);;">Estimated value of RRSP at 69 years of age</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(189, 215, 238);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(189, 215, 238);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(189, 215, 238);;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(189, 215, 238);;"> $ 10,375.00 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(226, 239, 218);;"></td></tr><tr ><td style="color: #201116;text-align: center;">15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Year</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Age *</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Percentage **</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Amount</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">RRIF Value ***</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Monthly Withdrawals</td></tr><tr ><td style="color: #201116;text-align: center;">16</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">70</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.05</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 518.75 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 10,400.94 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 43.23 </td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0;color: #201116">rrif</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #B6AAA6"><thead><tr style=" background-color: #E0E0F0;color: #201116"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">B14</th><td style="text-align:left">=IF(<font color="rgb(255">'personal information'!$E$12=0,"Estimated value of RRSP at 69 years of age","Estimated value of RRSP at end of "& YEAR(<font color=" 0">'personal information'!$E$12</font>)+69</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">F14</th><td style="text-align:left">=IFERROR(<font color="rgb(255">INDEX(<font color=" 0">investments!$F$18:$F$102,MATCH(<font color=" 0)">69,investments!$B$18:$B$102,0</font>)</font>),investments!F18</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">B16</th><td style="text-align:left">=IF(<font color="rgb(255">'personal information'!$E$12=0,"",YEAR(<font color=" 0">'personal information'!$E$12</font>)+70</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">E16</th><td style="text-align:left">=SUM(<font color="rgb(255">F14*D16</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">F16</th><td style="text-align:left">=SUM(<font color="rgb(255">F14+(<font color=" 0">F14*0.0525</font>)-E16</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">G16</th><td style="text-align:left">=SUM(<font color="rgb(255">E16/12</font>)</td></tr></tbody></table></td></tr></table><br />On another sheet ('income analysis') cell D97 looks at the DOB and if the current year is the same as what is in 'rrif' B16, it will return the amount in 'rrif'G16 as long as the user is 70 years of age or more this year (2019). If the user is less than 70 years of age, the field is left blank because the 'first' age on the 'rrif' sheet is 70 (cell C16).

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #201116"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">97</td><td style="text-align: right;border-left: 1px solid black;background-color: rgb(226, 239, 218);;">Monthly Withdrawals</td><td style="text-align: right;border-bottom: 1px solid black;background-color: rgb(226, 239, 218);;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: rgb(226, 239, 218);;"> $ - </td></tr></tbody></table><p style="width:9em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0;color: #201116">income analysis</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #B6AAA6"><thead><tr style=" background-color: #E0E0F0;color: #201116"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">D97</th><td style="text-align:left">=IF(<font color="rgb(255">'personal information'!E12="",0,OFFSET(<font color=" 0">rrif!$G$16,YEAR(<font color=" 0)">NOW(<font color="rgb(0"></font>)</font>)-rrif!$B$16,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />So this is where the two weird errors have manifested themselves. If I change the DOB so that the user is 69 years old now, the formula is returning "Monthly Withdrawals" which resides in cell 'rrif'G15. After that, if the DOB is 70+ years, no errors, the formula returns the expected amount from column G. The other part that's strange is that if I enter a DOB from 1965 or later ('66, '67', '68 etc.), the cell returns a #REF error. I can't see anything that might cause either error and that is why I am asking for help. Sometimes it really helps to get another set of eyes on a problem. Thanks!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
DRSteele

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,375
Office Version
  1. 365
Platform
  1. Windows
Re: Need help with complicated formula

The #REF error occurs when DOB is greater than 12/31/1964. The reason is that function OFFSET is being asked to start at G16 and slide a certain number of rows. In the case of a DOB after 12/31/1964, your formula in the ROWS argument in function OFFSET evaluates to -16 or worse, which forces Excel to go to Row-0 or Row-negative, rows which don't exist.
 
leopardhawk

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hi and thanks! Any chance you can help me with the syntax. I think I understand what you are saying but I am drawing a blank when I try to make it work.

You should know that there are 40 rows below Row 16, from age 70 - 110. I need the formula to work for basically any age of person, from 18 - 110. I don't want to limit it to a subset of that (if this is even possible).

Thanks!
 
T

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
leopardhawk said:
Hi and thanks! Any chance you can help me with the syntax. I think I understand what you are saying but I am drawing a blank when I try to make it work.

You should know that there are 40 rows below Row 16, from age 70 - 110. I need the formula to work for basically any age of person, from 18 - 110. I don't want to limit it to a subset of that (if this is even possible).

Thanks!
Click to expand...

where does this lead?
OFFSET(rrif!$G$16,YEAR(NOW())-rrif!$B$16,0)
(G16, (2019-""),0)
So 2019-"" = value error for me, from the b16 formula i'm guessing the value in b16 =""
If it was working though, wouldn't this offset 2019 rows below G16?
And then let's say you make it a negative value where the current year is less than the Retirement year
For example if someone was going to retire 17 years from now then you have:
2019-2036=-17
So the cell is going to point to the value 17 cells above B16 and that cell doesn't exist because there aren't negative cells on the spreadsheet.

I'm not sure anyone can tell you how to fix this without knowing the purpose of the offset function

Can you share what table the offset is pointing too, or what the desired range of results is.

If you have a range of cells you want to output from it, then we can add more conditions to the if statement accordinly based off of those ranges
 
Last edited:
leopardhawk

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks for jumping in. The OFFSET idea came from another forum user and I am not married to it. I would like some help with the formula even if it means a completely different approach.

If it will help, I can use the forum tools to paste some more screen shots from my worksheets like I did above, just let me know.

The range of cells is all on the 'rrif' sheet and I put a bit of it in my original post above. Basically, when a user enters their DOB, column B on the 'rrif' sheet fills in from age 70-100 with the actual years that the person will be those ages. Even B14 will change to show the year that the person turns 69 (see formula above).

The problem formula is on the 'income analysis' sheet in cell D97.

What I am trying to do is have the formula return an amount from 'rrif' column G starting when the person turns 70 and for every year after that by moving down the range of cells in column G each year. This has to work no matter how old they are today, as young as 18, as old as 100. Obviously, if they are past age 70, the formula would move down the range of cells and find the appropriate amount for the age that they are at that time.

If I change the DOB so that the user is 69 years old now, the formula is returning "Monthly Withdrawals" which resides in cell 'rrif'G15. After that, if the DOB is 70+ years, no errors, the formula returns the expected amount from column G. The other part that's strange is that if I enter a DOB from 1965 or later ('66, '67', '68 etc.), the cell returns a #REF error. I can't see anything that might cause either error and that is why I am asking for help. Sometimes it really helps to get another set of eyes on a problem.

Thanks
 
T

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
leopardhawk said:
The other part that's strange is that if I enter a DOB from 1965 or later ('66, '67', '68 etc.), the cell returns a #REF error. I can't see anything that might cause either error and that is why I am asking for help. Sometimes it really helps to get another set of eyes on a problem.

Thanks
Click to expand...

This is because the date of birth field is used to populate the retirement date(DOB+70), and the difference from current(todays date year) year minus retirement date is populating the offset function, and the younger you make the date of birth, it will be referencing a cell that doesn't exist.
(like my above example, for someone that will retire 16+ years from now will all return error because the offset is going off the page from G16 to G1 with 15 years left until retirement and then 16 years puts it at G0 which doesn't exist)

I would recommend that you give the ranges for what is an acceptable return in cell D97, specifically the cell that is being referred to by the offset. for example, you only want that offset to be able to look at G20:G60 or else have it return 0 instead, or you can just let it go through the entire column
basically what numbers do you want to populate in the red portion of this formula, which cells specifically do you want it to be able to see

=IF('personal information'!E12="",0,OFFSET(rrif!$G$16,YEAR(NOW())-rrif!$B$16,0))

<tbody>
</tbody>

Yeah for me, I can be more helpful likely if i have the data set or if you can share that table you want it to point to, and tell me what ranges you want that to populate

off the top of my head i would say a vlookup based on the age is probably a better way to return this value, but I'm guessing there is probably a barrier for using that method since an offset was used instead.
 
Last edited:
DRSteele

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,375
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Paste more sample data, especially the table of RRIF values.
 
Last edited:
leopardhawk

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Here are the pertinent data sets from my workbook. This one is from a sheet called 'personal information'. Here you can see that I entered May 6, 1954 which puts the user at 65 years of age.

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #201116"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">10</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;">First</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;">Last</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;">Date of birth</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;">Company</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;"> Retirement Date</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;">Pension Type</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;">Pension Provider</td></tr><tr ><td style="color: #201116;text-align: center;">11</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: rgb(248, 203, 173);;"></td></tr><tr ><td style="color: #201116;text-align: center;">12</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;">Name</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;">1954-05-06</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td></tr><tr ><td style="color: #201116;text-align: center;">13</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(248, 203, 173);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(248, 203, 173);;"></td></tr><tr ><td style="color: #201116;text-align: center;">14</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(198, 224, 180);;">Spouse/Partner</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(112, 173, 71);;"></td></tr></tbody></table><p style="width:12em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0;color: #201116">personal information</p><br /><br />This next one is from a sheet called 'rrif'. This is the sheet where all the data resides that the problem formula is trying to return. The formulas on this sheet are fairly straight forward and are all working as intended.

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #201116"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: rgb(189, 215, 238);;">Estimated value of RRSP at end of 2023</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(189, 215, 238);;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(189, 215, 238);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;font-style: italic;background-color: rgb(189, 215, 238);;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(189, 215, 238);;"> $ 60,104.99 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(226, 239, 218);;"></td></tr><tr ><td style="color: #201116;text-align: center;">15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Year</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Age *</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Percentage **</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Amount</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">RRIF Value ***</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(255, 242, 204);;">Monthly Withdrawals</td></tr><tr ><td style="color: #201116;text-align: center;">16</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2024</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">70</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.05</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,005.25 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 59,353.68 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 250.44 </td></tr><tr ><td style="color: #201116;text-align: center;">17</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2025</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">71</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0528</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,133.87 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 58,445.57 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 261.16 </td></tr><tr ><td style="color: #201116;text-align: center;">18</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2026</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">72</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.054</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,156.06 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 57,481.21 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 263.01 </td></tr><tr ><td style="color: #201116;text-align: center;">19</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2027</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">73</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0553</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,178.71 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 56,458.05 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 264.89 </td></tr><tr ><td style="color: #201116;text-align: center;">20</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2028</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">74</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0567</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,201.17 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 55,374.05 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 266.76 </td></tr><tr ><td style="color: #201116;text-align: center;">21</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2029</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">75</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0582</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,222.77 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 54,227.81 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 268.56 </td></tr><tr ><td style="color: #201116;text-align: center;">22</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2030</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">76</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0598</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,242.82 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 53,018.53 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 270.24 </td></tr><tr ><td style="color: #201116;text-align: center;">23</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2031</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">77</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0617</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,271.24 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 51,735.48 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 272.60 </td></tr><tr ><td style="color: #201116;text-align: center;">24</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2032</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">78</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0636</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,290.38 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 50,385.19 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 274.20 </td></tr><tr ><td style="color: #201116;text-align: center;">25</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2033</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">79</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0658</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,315.35 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 48,959.29 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 276.28 </td></tr><tr ><td style="color: #201116;text-align: center;">26</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2034</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">80</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0682</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,339.02 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 47,456.24 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 278.25 </td></tr><tr ><td style="color: #201116;text-align: center;">27</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2035</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">81</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0708</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,359.90 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 45,875.94 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 279.99 </td></tr><tr ><td style="color: #201116;text-align: center;">28</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2036</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">82</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0738</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,385.64 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 44,210.65 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 282.14 </td></tr><tr ><td style="color: #201116;text-align: center;">29</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2037</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">83</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0771</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,408.64 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 42,459.91 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 284.05 </td></tr><tr ><td style="color: #201116;text-align: center;">30</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2038</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">84</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0808</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,430.76 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 40,621.39 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 285.90 </td></tr><tr ><td style="color: #201116;text-align: center;">31</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2039</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">85</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0851</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,456.88 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 38,687.81 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 288.07 </td></tr><tr ><td style="color: #201116;text-align: center;">32</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2040</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">86</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0899</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,478.03 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 36,660.57 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 289.84 </td></tr><tr ><td style="color: #201116;text-align: center;">33</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2041</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">87</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.0955</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,501.08 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 34,534.26 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 291.76 </td></tr><tr ><td style="color: #201116;text-align: center;">34</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2042</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">88</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.1021</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,525.95 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 32,303.35 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 293.83 </td></tr><tr ><td style="color: #201116;text-align: center;">35</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2043</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">89</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.1099</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,550.14 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 29,964.58 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 295.84 </td></tr><tr ><td style="color: #201116;text-align: center;">36</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2044</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">90</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.1192</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,571.78 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 27,516.48 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 297.65 </td></tr><tr ><td style="color: #201116;text-align: center;">37</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2045</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">91</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.1306</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,593.65 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 24,954.69 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 299.47 </td></tr><tr ><td style="color: #201116;text-align: center;">38</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2046</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">92</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.1449</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,615.93 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 22,274.56 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 301.33 </td></tr><tr ><td style="color: #201116;text-align: center;">39</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2047</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">93</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.1634</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,639.66 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 19,470.19 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 303.31 </td></tr><tr ><td style="color: #201116;text-align: center;">40</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2048</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">94</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.1879</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,658.45 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 16,541.87 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 304.87 </td></tr><tr ><td style="color: #201116;text-align: center;">41</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2049</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">95</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,308.37 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 13,853.82 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 275.70 </td></tr><tr ><td style="color: #201116;text-align: center;">42</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2050</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">96</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 2,770.76 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 11,602.57 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 230.90 </td></tr><tr ><td style="color: #201116;text-align: center;">43</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2051</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">97</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 2,320.51 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 9,717.16 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 193.38 </td></tr><tr ><td style="color: #201116;text-align: center;">44</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2052</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">98</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 1,943.43 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 8,138.12 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 161.95 </td></tr><tr ><td style="color: #201116;text-align: center;">45</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2053</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">99</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 1,627.62 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 6,815.67 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 135.64 </td></tr><tr ><td style="color: #201116;text-align: center;">46</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2054</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">100</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 1,363.13 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 5,708.13 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 113.59 </td></tr><tr ><td style="color: #201116;text-align: center;">47</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2055</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">101</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 1,141.63 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 4,780.56 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 95.14 </td></tr><tr ><td style="color: #201116;text-align: center;">48</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2056</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">102</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 956.11 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 4,003.72 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 79.68 </td></tr><tr ><td style="color: #201116;text-align: center;">49</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2057</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">103</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 800.74 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 3,353.11 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 66.73 </td></tr><tr ><td style="color: #201116;text-align: center;">50</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2058</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">104</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 670.62 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 2,808.23 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 55.89 </td></tr><tr ><td style="color: #201116;text-align: center;">51</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2059</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">105</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 561.65 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 2,351.89 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 46.80 </td></tr><tr ><td style="color: #201116;text-align: center;">52</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2060</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">106</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 470.38 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 1,969.71 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 39.20 </td></tr><tr ><td style="color: #201116;text-align: center;">53</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2061</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">107</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 393.94 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 1,649.63 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 32.83 </td></tr><tr ><td style="color: #201116;text-align: center;">54</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2062</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">108</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 329.93 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 1,381.57 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 27.49 </td></tr><tr ><td style="color: #201116;text-align: center;">55</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2063</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">109</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 276.31 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 1,157.06 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 23.03 </td></tr><tr ><td style="color: #201116;text-align: center;">56</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;color: rgb(255, 255, 255);background-color: rgb(47, 117, 181);;">2064</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">110</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">0.2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 231.41 </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 969.04 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 19.28 </td></tr><tr ><td style="color: #201116;text-align: center;">57</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;">Total withdrawals</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: rgb(217, 225, 242);;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: rgb(217, 225, 242);;"> $ 87,841.53 </td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0;color: #201116">rrif</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #B6AAA6"><thead><tr style=" background-color: #E0E0F0;color: #201116"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">B14</th><td style="text-align:left">=IF(<font color="rgb(255">'personal information'!$E$12=0,"Estimated value of RRSP at 69 years of age","Estimated value of RRSP at end of "& YEAR(<font color=" 0">'personal information'!$E$12</font>)+69</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">F14</th><td style="text-align:left">=IFERROR(<font color="rgb(255">INDEX(<font color=" 0">investments!$F$18:$F$102,MATCH(<font color=" 0)">69,investments!$B$18:$B$102,0</font>)</font>),investments!F18</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">B16</th><td style="text-align:left">=IF(<font color="rgb(255">'personal information'!$E$12=0,"",YEAR(<font color=" 0">'personal information'!$E$12</font>)+70</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">E16</th><td style="text-align:left">=SUM(<font color="rgb(255">F14*D16</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">F16</th><td style="text-align:left">=SUM(<font color="rgb(255">F14+(<font color=" 0">F14*0.0375</font>)-E16</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">G16</th><td style="text-align:left">=SUM(<font color="rgb(255">E16/12</font>)</td></tr></tbody></table></td></tr></table><br />This next sheet is the 'income analysis' sheet and this is where cell D97 resides. Because the user in this example is not 70 yet, the formula is returning 0, but only because it is looking in the wrong direction on ‘rrif’ as you said in your last post. So the formula does not have to use OFFSET, it can be anything but what I need it to do is return from column G on ‘rrif’ sheet the amount from the cell in the same row as the users age if they are 70+ and return zero (0) if they are less than 70 years of age.

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #201116"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">96</td><td style="text-align: center;border-left: 1px solid black;background-color: rgb(191, 191, 191);;">Total Income</td><td style="text-align: center;background-color: rgb(191, 191, 191);;"></td><td style="text-align: center;background-color: rgb(191, 191, 191);;"></td><td style="text-align: center;background-color: rgb(191, 191, 191);;"></td><td style="text-align: center;border-right: 1px solid black;background-color: rgb(191, 191, 191);;"></td></tr><tr ><td style="color: #201116;text-align: center;">97</td><td style="text-align: right;border-left: 1px solid black;background-color: rgb(226, 239, 218);;">RRIF Monthly Withdrawals</td><td style="text-align: right;border-bottom: 1px solid black;background-color: rgb(226, 239, 218);;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: rgb(226, 239, 218);;"> $                 -   </td><td style="text-align: right;background-color: rgb(226, 239, 218);;">Annual  Withdrawals</td><td style="text-align: right;border-right: 1px solid black;background-color: rgb(226, 239, 218);;"> $                    -   </td></tr></tbody></table><p style="width:9em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0;color: #201116">income analysis</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #B6AAA6"><thead><tr style=" background-color: #E0E0F0;color: #201116"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">D97</th><td style="text-align:left">=IF(<font color="rgb(255">'personal information'!E12="",0,OFFSET(<font color=" 0">rrif!$G$16,YEAR(<font color=" 0)">NOW(<font color="rgb(0"></font>)</font>)-rrif!$B$16,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">F97</th><td style="text-align:left">=SUM(<font color="rgb(255">D97</font>)*12</td></tr></tbody></table></td></tr></table><br />
 
T

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
try this one and let me know what you think
=IFERROR(IF('personal information'!E12="",0,VLOOKUP(YEAR(TODAY())-YEAR('personal information'!E12),$C$16:$G$56,5)),0)

Note, if the age is less than 70 then the formula will return 0
If the age is above 110 formula will populate the lowest amount 19.28,

If you change the formula to
=IFERROR(IF('personal information'!E12="",0,VLOOKUP(YEAR(TODAY())-YEAR('personal information'!E12),$C$16:$G$56,5,0)),0)
by adding in the 0 to the end of the vlookup, then age less than 70 or greater than 110 will both return 0
 
Last edited:
T

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
IFERROR(IF('personal information'!E12="",0,VLOOKUP(YEAR(TODAY())-YEAR('personal information'!E12),’rrif’!$C$16:$G$56,5,1)),0)

Added this part

’rrif’! To reference the page the range is coming from
 
Last edited:
You must log in or register to reply here.

Similar threads

M
  • Question
left algin table by pixels
Replies
0
Views
124
MinMax
M
G
  • Question
Data validation drop down list source to lookup a value and list unused items ?
Replies
0
Views
449
gaftalik
G
N
  • Question
Dynamic Reporting Calendar in Excel
Replies
5
Views
955
Fluff
Fluff
leopardhawk
  • Question
Formula mod needed for age, year, DOB
Replies
9
Views
764
DanteAmor
DanteAmor
leopardhawk
  • Question
Formula relating to a date of birth and a retirement date
Replies
3
Views
408
leopardhawk
leopardhawk

Watch MrExcel Video

Excel Pick From Dropdown List - 2327

Forum statistics

Threads
1,129,421
Messages
5,636,170
Members
416,904
Latest member
jaesun0802

Share this page

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