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, 2023 in Others by Kithuzzz
• 38,000 points
342 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, 2023 by narikkadan
• 63,600 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
• 23,260 points
1,634 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,600 points
3,555 views
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
• 63,600 points
561 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
• 63,600 points
612 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,690 points
4,075 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,690 points
2,003 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,690 points
983 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
• 23,260 points
3,707 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
• 63,600 points
1,037 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
• 63,600 points
1,970 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