Complicated formula

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!
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Thank you so much for your time. Can't tell you how much I appreciate it. The #REF errors are gone but now the formula is returning zero no matter what year I enter as a DOB. I have tried at least a dozen different DOB's from 1938 to 1988.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
Thank you so much for your time. Can't tell you how much I appreciate it. The #REF errors are gone but now the formula is returning zero no matter what year I enter as a DOB. I have tried at least a dozen different DOB's from 1938 to 1988.

Please copy and paste the formula from your spreadsheet to here
The formula I sent before is working for me fine, but I manually added the references
So for instance where i have 'Personal Information'!E12, you may want to delete that out, and grab the cell it's pointing to with your mouse, to make sure the syntax is correct
and same for rrif!
you see in the formula you had before it shows a ' around personal information because of the space in the title, and for rrif there is no ', so update it to match that syntax into the new formula i sent and it should work
=IF('personal information'!E12="",0,OFFSET(rrif!$G$16,YEAR(NOW())-rrif!$B$16,0)
sorry about that, I wasn't paying great attention to the syntax on the ranges i sent

like this in the new formula
=IFERROR(IF('personal information'!E12="",0,VLOOKUP(YEAR(TODAY())-YEAR('personal information'!E12),rrif!$C$16:$G$56,5,0)),0)

Just make sure to check it with the ranges in your sheet to make sure the reference is correct

When I'm having trouble with a formula I like to highlight a part of the forumla and select F9 to calculate that cell and see what the value is'(Make sure after hitting F9 to ctrl z to return back to the references instead of the value)
Calculate the fields in the formula
Highlight these and select calculate to see if the values are what you would expect
'personal information'!E12 ->For Instance pressing F9 when highlighting this should return the value displaying in cell E12
YEAR(TODAY()) -> Year today should return 2019 cause that is the current year
YEAR('personal information'!E12) -> Should return the date of birth year(year of birth)
YEAR(TODAY())-YEAR('personal information'!E12) -> This should return the current age of the retiree
VLOOKUP(YEAR(TODAY())-YEAR('personal information'!E12),rrif!$C$16:$G$56,5,0) ->This should return the monthly withdrawal

Does that make sense? I cannot test because I would have to set up all the pages and ranges exactly the same so you want to just test those things with your data
alternatively you can go to the formula tab and click evaluate formula (you have less control of what you can see here)


 
Last edited:

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,353
Office Version
  1. 365
Platform
  1. Windows
This formula seems to work. It reports 268.5642 when the DOB is 5/6/1944 for a 75 year old man.

=IFERROR(IF('personal information'!E12="",0,VLOOKUP(YEAR(TODAY())-YEAR('personal information'!E12),rrif!C16:G56,5,1)),0)

Notice that in formulas, worksheet names are wrapped in apostrophes when there is a space in the worksheet name (like 'personal information') and are not wrapped when there is no space (as in rrif). My recommendation is that you all form a new policy of never having spaces in worksheet names. I also think shorter is better. Try things like: PersonalInfo, or Personal_info, or personal-info, or PInfo, or Bio.
 
Last edited:

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Many thanks to twollaston and drsteele, it looks like the solutions you proposed are identical and they work great so far. I am continuing to run tests to make 100% sure but wanted to reach out to both of you to say thanks!

kind regards
 

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235

ADVERTISEMENT

Many thanks to twollaston and drsteele, it looks like the solutions you proposed are identical and they work great so far. I am continuing to run tests to make 100% sure but wanted to reach out to both of you to say thanks!

kind regards

No Problem, I'm happy to help you
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Almost forgot, for DRSteele, thank you so much for that tip about the worksheet names, I did not know that. Makes perfect sense though to do it as you suggest. I'm reluctant to do it on this workbook though because of the effort involved and the risk of missing a formula here or there. But for sure, I will keep it in mind for next time.

Cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,906
Messages
5,621,577
Members
415,846
Latest member
nigeywigey

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