Complicated formula

[leopardhawk]

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.

Excel 2016 (Windows) 32 bit
	E
10	Date of birth
11
12
personal information

Below is the 'rrif' sheet where the data resides.

Excel 2016 (Windows) 32 bit
	B	C	D	E	F	G
14	Estimated value of RRSP at 69 years of age				$ 10,375.00
15	Year	Age *	Percentage **	Amount	RRIF Value ***	Monthly Withdrawals
16		70	0.05	$ 518.75	$ 10,400.94	$ 43.23
rrif

Cell Formulas
Range	Formula
B14	=IF('personal information'!$E$12=0,"Estimated value of RRSP at 69 years of age","Estimated value of RRSP at end of "& YEAR('personal information'!$E$12)+69)
B16	=IF('personal information'!$E$12=0,"",YEAR('personal information'!$E$12)+70)
F14	=IFERROR(INDEX(investments!$F$18:$F$102,MATCH(69,investments!$B$18:$B$102,0)),investments!F18)
F16	=SUM(F14+(F14*0.0525)-E16)
E16	=SUM(F14*D16)
G16	=SUM(E16/12)

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

Excel 2016 (Windows) 32 bit
	B	C	D
97	Monthly Withdrawals		$ -
income analysis

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

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]

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.

 

[Twollaston]

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)

 

[DRSteele]

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.

 

[leopardhawk]

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]

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]

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!

 

[DRSteele]

You're welcome.