Average, excluding zeros, non-contiguous range

[ramswin]

Hi, I am trying to find the average of this row (skipping the greyed columns-- so skipping columns F,G,M,N) and also excluding the zeros (columns H to L). I know the AverageIf cannot skip columns so that didn't work, please help. Please assist. Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
Are you trying to ignore weekends & if do is row 1 actual dates formatted to show m/d?

 

[ramswin]

Yes, correct. Trying to ignore weekends and row 1 is the date.

 

[Fluff]

Ok, how about

=AVERAGE(FILTER(B2:S2,(B2:S2<>0)*(WEEKDAY($B$1:$S$1,2)<6)))

 

[ramswin]

That worked! Thank you, couple questions:
1. what does the 2 and the <6 represent?
2. for the weekends I'm assuming just replace Weekday with Weekend?
3. How would I do this if there weren't any dates?

Thanks for the quick help!

 

[Fluff]

1) the 2 stipulates that Monday is 1 Tuesday is 2 etc, so the <6 returns columns that are before Saturday.
2) No you would >5 rather than <6
3) You need some criteria to determine which cells to exclude.

 

[ramswin]

Thank you Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.