Need help with age related formula

[leopardhawk]

Hello forum friends,

I have a worksheet called 'income_analysis', with three separate sections (groups of cells). Each section is related to the current or future age of the user. Section 1 is less than 60 years, Section 2 is 60-70 years and Section 3 is over 70 years of age. There is a single cell within each of these three sections that contains the relevant dollar amount that I need in the formula (they are F34, F62 and F92 respectively).

There is a worksheet called 'personal_info' (cell E9) where the user enters their DOB.

A worksheet called 'tax rates', is where I need the actual formula (cell C5). The formula should return a dollar amount from one of the three cells on the 'income_analysis' worksheet based on the current age of the user.

Not sure if this is possible but thought I would throw it out there.

Thanks!

 

[Toadstool]

Hi LeopardHawk,

I think this does what you want.

income_analysis

LeopardHawk.xlsx
	A					F	G
1
33						Section 1
34						$ 12,000
35
61						Section 2
62						$ 25,000
63
91						Section 3
92						$ 62,000
93
income_analysis

personal_info

LeopardHawk.xlsx
	A				E	F
1
8					DoB
9					1/1/1949
10
personal_info

tax rates

LeopardHawk.xlsx
	A		C	D
1
4			Result
5			62000
6
tax rates

Cell Formulas
Range	Formula
C5	C5	=IF(DATEDIF(personal_info!E9,TODAY(),"Y")<60,income_analysis!F34,IF(DATEDIF(personal_info!E9,TODAY(),"Y")<71,income_analysis!F62,income_analysis!F92))

 

[Peter_SSs]

Here is another way too.

My 'income_analysis' & 'personal_info' sheets are the same as Toadstool's

Note though that this will not adjust automatically if rows are subsequently inserted or deleted between rows 34 and 92 in sheet 'income_analysis'

leopardhawk 2020-04-03 1.xlsm
	C
4	Result
5	62000
tax rates

Cell Formulas
Range	Formula
C5	C5	=INDEX(income_analysis!F34:F92,LOOKUP(DATEDIF(personal_info!E9,TODAY(),"y"),{0,60,71},{1,29,59}))

 

[leopardhawk]

Thank you both so much, they both work fantastically!

 

[Peter_SSs]

Thank you both so much, they both work fantastically!

You're welcome.

Here is another, intermediate, way that will allow the formula auto-adjustment if rows are added/removed

leopardhawk 2020-04-03 1.xlsm
	C
4	Result
5	62000
tax rates

Cell Formulas
Range	Formula
C5	C5	=CHOOSE(MATCH(DATEDIF(personal_info!E9,TODAY(),"y"),{0,60,71}),income_analysis!F34,income_analysis!F62,income_analysis!F92)