Need help with #REF! error

[leopardhawk]

Hello forum friends, I could sure use some help here. Two separate tabs from my workbook are included in this thread. The formula in question is on the ‘income analysis’! tab and the data is all located on the ‘rrif’! tab.

The OFFSET formula on the ‘income analysis’! tab works as intended which is, that over time, the value returned to D107 will change each year to the corresponding value in the next row of column G on the ‘rrif’! tab. I have tested this by changing the system date on my computer, all the way up to the year 2050.

I need users to be able to enter any year in ‘rrif’!B18.

As long as the value in 'rrif'!B18 is from 2019 up to 2036, there are no issues. If I enter 2037 or higher, then I am getting a #REF! error in D107.
And, I’m not sure if this means anything or not but if I enter 2020 in 'rrif'!B18, the value being returned to D107 is ‘Monthly Withdrawals’ which you can see is just text located in ‘rrif’!G17. I went so far as to put the word ‘Test’ in ‘rrif’!G16 and changed the year in ‘rrif’!B18 to 2021 and now the formula is returning the word ‘Test’ to cell D107. So, the formula seems to be moving(looking?) up the sheet instead of down the sheet. Very strange.

I have been unable to figure this one out. Please help if you might have some ideas. Thank you!

Excel 2016 (Windows) 32 bit
	B	C	D
107	RRIF Monthly Withdrawals		$ 662.50
income analysis

Cell Formulas
Range	Formula
D107	=OFFSET(rrif!$G$18,YEAR(NOW())-rrif!$B$18,0)

Below is from the rrif tab.

Excel 2016 (Windows) 32 bit
	B	C	D	E	F	G
16				Estimated value of RRSP at end of YYYY	$ 159,000.00
17	Year	Age *	Percentage **	Amount	RRIF Value ***	Monthly Withdrawals
18	2019	70	0.05	$ 7,950.00	$ 159,397.50	$ 662.50
19	2020	71	0.0528	$ 8,416.19	$ 159,349.68	$ 701.35
20	2021	72	0.054	$ 8,604.88	$ 159,110.66	$ 717.07
21	2022	73	0.0553	$ 8,798.82	$ 158,665.15	$ 733.23
22	2023	74	0.0567	$ 8,996.31	$ 157,998.75	$ 749.69
23	2024	75	0.0582	$ 9,195.53	$ 157,098.16	$ 766.29
24	2025	76	0.0598	$ 9,394.47	$ 155,951.34	$ 782.87
25	2026	77	0.0617	$ 9,622.20	$ 154,516.59	$ 801.85
26	2027	78	0.0636	$ 9,827.26	$ 152,801.46	$ 818.94
27	2028	79	0.0658	$ 10,054.34	$ 150,769.20	$ 837.86
28	2029	80	0.0682	$ 10,282.46	$ 148,402.12	$ 856.87
29	2030	81	0.0708	$ 10,506.87	$ 145,686.36	$ 875.57
30	2031	82	0.0738	$ 10,751.65	$ 142,583.24	$ 895.97
31	2032	83	0.0771	$ 10,993.17	$ 139,075.69	$ 916.10
32	2033	84	0.0808	$ 11,237.32	$ 135,139.85	$ 936.44
33	2034	85	0.0851	$ 11,500.40	$ 130,734.29	$ 958.37
34	2035	86	0.0899	$ 11,753.01	$ 125,844.83	$ 979.42
35	2036	87	0.0955	$ 12,018.18	$ 120,433.50	$ 1,001.52
36	2037	88	0.1021	$ 12,296.26	$ 114,460.00	$ 1,024.69
37	2038	89	0.1099	$ 12,579.15	$ 107,890.00	$ 1,048.26
38	2039	90	0.1192	$ 12,860.49	$ 100,693.73	$ 1,071.71
39	2040	91	0.1306	$ 13,150.60	$ 92,829.55	$ 1,095.88
40	2041	92	0.1449	$ 13,451.00	$ 84,252.10	$ 1,120.92
41	2042	93	0.1634	$ 13,766.79	$ 74,908.54	$ 1,147.23
42	2043	94	0.1879	$ 14,075.32	$ 64,765.93	$ 1,172.94
43	2044	95+	0.2	$ 12,953.19	$ 55,212.95	$ 1,079.43
44					Total	$ 285,035.85
rrif

Cell Formulas
Range	Formula
E18	=SUM(F16*D18)
F18	=SUM(F16+(F16*0.0525)-E18)
G18	=SUM(E18/12)

 

[pgc01]

Hi

Your formula

=OFFSET(rrif!$G$18,YEAR(NOW())-rrif!$B$18,0)

Let's see for the current date, year 2019, it's

=OFFSET(rrif!$G$18,2019-rrif!$B$18,0)


If you use B18, the value 2019, you get

=OFFSET(rrif!$G$18,2019-2019,0)

=OFFSET(rrif!$G$18,0,0)

which is G18, the value $ 662.50

If you use 2020

=OFFSET(rrif!$G$18,2019-2019,0)

=OFFSET(rrif!$G$18,-1,0)

this the the cell 1 row above G18, G17 "Monthly Withdrawals"

If you use 2037

=OFFSET(rrif!$G$18,2019-2037,0)

=OFFSET(rrif!$G$18,-18,0)

This would be 18 rows above G18, doesn't exist, there is no row 0 or negative, so you'll get the reference error.

Seems OK to me.

Maybe you want to switch the operands of the subtraction?

=OFFSET(rrif!$G$18,rrif!$B$18-YEAR(NOW()),0)

 

[leopardhawk]

PGC, thank you for your time and effort. I think your suggestion is a step in the right direction but not quite there yet. When I enter 2020 in now in 'rrif'!B18, the amount returned to 'income analysis'!D107 is $701.35 which as you can see in the table is the amount from 'rrif'!G19.

At least it's moving down the table BUT, I need to be able to enter ANY year in 'rrif'!B18 and the amount returned to 'income analysis'!D107 will be whatever is in 'rrif!G18, even if I enter for example the year 2065. The amount in 'rrif'!G18 is dependent on whatever amount the user enters into 'rrif'!F16 and the user will also be entering the year that they turn 70 years old in 'rrif'!B18.

Still need help with this.

Thanks!