AVERAGEIFS not calculating correctly

[Maxwelljohn]

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)

 

[steve the fish]

Is a Wk 21 21 a formatted date or is it text? It needs to be a proper date if used like that.

 

[Maxwelljohn]

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]

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]

Thank you, clearly I feel a bit stupid now.

Kind Regards, Maxwell

 

[steve the fish]

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.