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


Joined
Oct 4, 2011
Messages
2
Reaction score
0
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:bow:
 
Ad

Advertisements

Joined
Sep 3, 2008
Messages
164
Reaction score
5
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
 
Ad

Advertisements

Joined
Oct 4, 2011
Messages
2
Reaction score
0
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.
 

Attachments


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top