Excel AVERAGEIFS Date discrepancy

0 votes

Based on two factors, I would like the average of Column B. that it occurred the previous year and text requirements from another column. by year, the average For testing purposes, I've included a Year column in the example, but I don't want to add it to every datasheet.

=AVERAGEIFS(Table1[Unit], Table1[Date], "="&YEAR(TODAY())-1, Table1[Text], "Up") 

Throws a DIV/0 error.

I believe I need to define the Date range by year.. like (YEAR(Table1[Date]) but it doesn't work.

=AVERAGEIFS(Table1[Unit], (YEAR(Table1[Date]), "="&YEAR(TODAY())-1, Table1[Text], "Up")

I can get an IF statement to work on a single cell but is there is way to get this to work in a column?

Jan 22 in Others by Kithuzzz
• 27,740 points
23 views

1 answer to this question.

0 votes

When defining a range, you are unable to use formulas; therefore, you must either use a helper column or something similar:

=AVERAGEIFS(Table1[Unit],Table1[Date],">="&(DATE(YEAR(TODAY())-1,1,1)),Table1[Date],"<="&(DATE(YEAR(TODAY())-1,12,31)),Table1[Text],"Up")

It checks if date is less than 2022/12/31 (DATE(YEAR(TODAY())-1,12,31))and more than 2022/01/01 (DATE(YEAR(TODAY())-1,1,1))

Result ((3+7)/2=5): enter image description here

answered Jan 22 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
659 views
0 votes
1 answer
0 votes
1 answer

Sort Excel worksheets based on name, which is a date

Sorting sheets of a workbook are rather ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 51,240 points
89 views
0 votes
1 answer

Excel plot against a date time x series [closed]

Try using an X-Y Scatter graph with ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 51,240 points
44 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
205 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
364 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
165 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
144 views
0 votes
1 answer

Excel automatically converting 7 digit CAS number to another number (date?)

Looks like you could use: The formula in D2: =SUBSTITUTE(F ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 51,240 points
111 views
0 votes
1 answer

Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 51,240 points
161 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP