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!

 

[DRSteele]

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]

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!

 

[Twollaston]

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!

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

 

[leopardhawk]

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

 

[Twollaston]

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

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.

 

[DRSteele]

Paste more sample data, especially the table of RRIF values.

 

[leopardhawk]

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.

Excel 2016 (Windows) 32 bit
	B	C	D	E	F	G	H	I
10		First	Last	Date of birth	Company	Retirement Date	Pension Type	Pension Provider
11
12	Name			1954-05-06
13
14	Spouse/Partner
personal information

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.

Excel 2016 (Windows) 32 bit
	B	C	D	E	F	G
14	Estimated value of RRSP at end of 2023				$ 60,104.99
15	Year	Age *	Percentage **	Amount	RRIF Value ***	Monthly Withdrawals
16	2024	70	0.05	$ 3,005.25	$ 59,353.68	$ 250.44
17	2025	71	0.0528	$ 3,133.87	$ 58,445.57	$ 261.16
18	2026	72	0.054	$ 3,156.06	$ 57,481.21	$ 263.01
19	2027	73	0.0553	$ 3,178.71	$ 56,458.05	$ 264.89
20	2028	74	0.0567	$ 3,201.17	$ 55,374.05	$ 266.76
21	2029	75	0.0582	$ 3,222.77	$ 54,227.81	$ 268.56
22	2030	76	0.0598	$ 3,242.82	$ 53,018.53	$ 270.24
23	2031	77	0.0617	$ 3,271.24	$ 51,735.48	$ 272.60
24	2032	78	0.0636	$ 3,290.38	$ 50,385.19	$ 274.20
25	2033	79	0.0658	$ 3,315.35	$ 48,959.29	$ 276.28
26	2034	80	0.0682	$ 3,339.02	$ 47,456.24	$ 278.25
27	2035	81	0.0708	$ 3,359.90	$ 45,875.94	$ 279.99
28	2036	82	0.0738	$ 3,385.64	$ 44,210.65	$ 282.14
29	2037	83	0.0771	$ 3,408.64	$ 42,459.91	$ 284.05
30	2038	84	0.0808	$ 3,430.76	$ 40,621.39	$ 285.90
31	2039	85	0.0851	$ 3,456.88	$ 38,687.81	$ 288.07
32	2040	86	0.0899	$ 3,478.03	$ 36,660.57	$ 289.84
33	2041	87	0.0955	$ 3,501.08	$ 34,534.26	$ 291.76
34	2042	88	0.1021	$ 3,525.95	$ 32,303.35	$ 293.83
35	2043	89	0.1099	$ 3,550.14	$ 29,964.58	$ 295.84
36	2044	90	0.1192	$ 3,571.78	$ 27,516.48	$ 297.65
37	2045	91	0.1306	$ 3,593.65	$ 24,954.69	$ 299.47
38	2046	92	0.1449	$ 3,615.93	$ 22,274.56	$ 301.33
39	2047	93	0.1634	$ 3,639.66	$ 19,470.19	$ 303.31
40	2048	94	0.1879	$ 3,658.45	$ 16,541.87	$ 304.87
41	2049	95	0.2	$ 3,308.37	$ 13,853.82	$ 275.70
42	2050	96	0.2	$ 2,770.76	$ 11,602.57	$ 230.90
43	2051	97	0.2	$ 2,320.51	$ 9,717.16	$ 193.38
44	2052	98	0.2	$ 1,943.43	$ 8,138.12	$ 161.95
45	2053	99	0.2	$ 1,627.62	$ 6,815.67	$ 135.64
46	2054	100	0.2	$ 1,363.13	$ 5,708.13	$ 113.59
47	2055	101	0.2	$ 1,141.63	$ 4,780.56	$ 95.14
48	2056	102	0.2	$ 956.11	$ 4,003.72	$ 79.68
49	2057	103	0.2	$ 800.74	$ 3,353.11	$ 66.73
50	2058	104	0.2	$ 670.62	$ 2,808.23	$ 55.89
51	2059	105	0.2	$ 561.65	$ 2,351.89	$ 46.80
52	2060	106	0.2	$ 470.38	$ 1,969.71	$ 39.20
53	2061	107	0.2	$ 393.94	$ 1,649.63	$ 32.83
54	2062	108	0.2	$ 329.93	$ 1,381.57	$ 27.49
55	2063	109	0.2	$ 276.31	$ 1,157.06	$ 23.03
56	2064	110	0.2	$ 231.41	$ 969.04	$ 19.28
57				Total withdrawals		$ 87,841.53
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.0375)-E16)
E16	=SUM(F14*D16)
G16	=SUM(E16/12)

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.

Excel 2016 (Windows) 32 bit
	B	C	D	E	F
96	Total Income
97	RRIF Monthly Withdrawals		$ -	Annual Withdrawals	$ -
income analysis

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

 

[Twollaston]

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

 

[Twollaston]

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