# AVERAGEIFS not calculating correctly

#### Maxwelljohn

##### New Member
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
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
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.

#### steve the fish

##### Well-known Member
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
Thank you, clearly I feel a bit stupid now.

Kind Regards, Maxwell

#### steve the fish

##### Well-known Member
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.

Replies
2
Views
239
Replies
1
Views
124
Replies
5
Views
244
Replies
5
Views
398
Replies
7
Views
2K

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

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.

### Which adblocker are you using?

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

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