AVERAGEIFS not calculating correctly

Maxwelljohn

New Member
Joined
May 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi,
I’m really struggling with my AVERAGEIFS formula, as it’s not calculating correctly and not sure where I’m going wrong.

I have a table of data with week numbers above in the row above. I was trying to use a drop down criteria to average set weeks. My data goes from wk 40 20 - wk 33 21, I was trying to use the drop downs to select the average based on the average wks I require. i.e Wk 21 21 - Wk 33 21.

My formula was
=AVERAGEIFS(F13:BI13,F12:BI12,”>=“&A4,F12:BI12,”<=“&B4)

A4 Is from week (drop down)
B4 is to week (drop down)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
Office Version
  1. 365
Platform
  1. Windows
Is a Wk 21 21 a formatted date or is it text? It needs to be a proper date if used like that.
 

Maxwelljohn

New Member
Joined
May 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Is a Wk 21 21 a formatted date or is it text? It needs to be a proper date if used like that.
Hi,
These are the wk numbers we use at work, so it comes out in that format when down loaded from the server. My drop downs are in the exact same format.

Not sure if that helps answer your question.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
Office Version
  1. 365
Platform
  1. Windows
You just cant do it like that. For example excel sees Wk 52 20 as greater than Wk 01 21. Thats just the way alphanumerics work. You need to transform them into numbers (true dates are numbers by the way).
 

Maxwelljohn

New Member
Joined
May 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you, clearly I feel a bit stupid now.

Kind Regards, Maxwell
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
Office Version
  1. 365
Platform
  1. Windows
Only other thing i can think you can do is to use a spare row to index your Wk numbers (1,2,3,4,5 etc) then use a lookup and test that for greater then or less than.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,576
Messages
5,765,196
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top