Formula relating to a date of birth and a retirement date

[leopardhawk]

Hello forum friends, I need help with a formula (actually, three formulas) that will return data from two other worksheets. I will paste in the sections of my worksheet and try to explain as best as I can.

This first section is on the 'income' worksheet and contains two columns with dates in one column and the other contains a dollar amount. I have only pasted a small section here, it actually extends out to row 500.

Excel 2016 (Windows) 32 bit
	F	G	H
11	Date paid		Amount (gross)
12	yyyy-mm-dd		$ -
13	yyyy-mm-dd		$ -
14	yyyy-mm-dd		$ -
15	yyyy-mm-dd		$ -
16	yyyy-mm-dd		$ -
17	yyyy-mm-dd		$ -
18	yyyy-mm-dd		$ -
19	yyyy-mm-dd		$ -
20	yyyy-mm-dd		$ -
21	yyyy-mm-dd		$ -
income

The second section is on the 'personal_info' worksheet and contains a date of birth for the user of the workbook.

Excel 2016 (Windows) 32 bit
	C	D	E
7	First	Last	Date of birth
8
9
personal_info

The section from the worksheet where I need the formula to reside (cell F24) is shown below.

Excel 2016 (Windows) 32 bit
	E	F
24	Average Gross Payroll (if applicable)	$ -
income_analysis

To explain... first and foremost, if column H on the 'income' worksheet is blank, the formula should always return zero (0). If there is anything in column H on the 'income' worksheet, the formula will return the AVERAGE of any dollar amounts in column H that are earned while the user is under 65 years of age.

So, assuming I can get a formula working in F24, and this is where it might get a bit complicated, if the user is between 65 and 70 years of age, I need another formula in F50 (see below) that will return the AVERAGE of any dollar amounts in column H that were earned during that time period.

Excel 2016 (Windows) 32 bit
	E	F
50	Average Gross Payroll (if applicable)	$ -
income_analysis

And, if the user is over 70 years of age, I need another formula in F77 (see below) that will return the AVERAGE of any dollar amounts that are earned after the user turns 70 years old.

Excel 2016 (Windows) 32 bit
	E	F
77	Average Gross Payroll (if applicable)	$ -
income_analysis

I hope this makes sense and that someone is willing to help me out.

Cheers!

 

[jorismoerings]

Re: Need help with formula relating to a date of birth and a retirement date

Hi,

Try this:

Formula in F24: =IFERROR(AVERAGEIFS(Income!H12:H500,Income!F12:F500,"<"&EDATE(personal_info!E8,(65*12))),0)
Formula in F50: =IFERROR(AVERAGEIFS(Income!H12:H500,Income!F12:F500,">="&EDATE(personal_info!E8,(65*12)),Income!F12:F500;"<"&EDATE(personal_info!E8,(70*12))),0)
Formula in F77: =IFERROR(AVERAGEIFS(Income!H12:H500,Income!F12:F500,">="&EDATE(personal_info!E8,(70*12))),0)

 

[leopardhawk]

Re: Need help with formula relating to a date of birth and a retirement date

Thank you so much for your efforts. The first formula and the third seem to work okay but the second one is giving me an error (There's a problem with this formula.) When I copy/paste it into the cell and hit enter. Any ideas?

Thanks!

 

[leopardhawk]

Re: Need help with formula relating to a date of birth and a retirement date

Okay, I found the problem, the second formula had a semicolon ( instead of a comma (,). It is now working as intended.

Cheers!