Excel MS Excel, Average or Non-consecutive columns excluding zero's

[Johnnie321]

I see a lot of posts about how to solve the issue of averageing items in Rows excluding zeros, but I am using vlookup to sort items by a specific day of the week, I would like the average of that information excluding zero's

My current formula is:

=AVERAGE((HLOOKUP(0,$C$2:$I$140,49,FALSE)),(HLOOKUP(0,$J$2:$P$140,49,FALSE)),(HLOOKUP(0,$Q$2:$W$140,49,FALSE)),(HLOOKUP(0,$X$2:$AD$140,49,FALSE)),(HLOOKUP(0,$AE$2:$AK$140,49,FALSE)),(HLOOKUP(0,$AL$2:$AR$140,49,FALSE)),(HLOOKUP(0,$AS$2:$AY$140,49,FALSE)))


John
THANKS

 

[Stoneboysteve]

John,

My simple understanding of lookup is Hlookup(what you are looking for, where you are looking, what position is your data, and is the list sorted).

Using that, it looks like your lookups are looking for 0.

When you run the formula, what does it produce. Also, you said you are using vlookup but the formula is hlookup. vlookup looks down a column, hlookup looks across a row.

Maybe a screen clip or example of your data would be helpful.

Steve

 

[Johnnie321]

I am certainly glad to provide a screen shot.

I only pasted my formula to show that I was using non-contigious cells and that the 'COUNTIF' function will not work. I am looking at ranges of 7 numbers in columns, and using 0 to designate Saturday, there are 49 columns, representing 7 weeks, I need to average the saturday for all the weeks, but I would liek to exclude 0's from my average.