Ignoring blank cells using Average & Vlookup formulas.

[erinbe]

Hi, hope someone can help...

I have multiple sheet (April, May, June, July...etc) and want to average values on each of the sheets.

Ex:

Sheet: April

Joe 10 54
John 15 64
Sarah 25 18

All sheets are the same. I've been using the average formula and vlookups to find the corresponding number on each sheet.

But some sheets have blank cells (giving #value errors) and I want to ignore these cells, not change them to 0's which would affect the overall average. I don't want to ignore 0's either because there are valid 0's on these sheets.

I've looked everywhere and am newer to the advanced formulas in excel. Hopefully someone can help me out here.

Thank You!

 

[rdw72777]

Are you averaging just one column on each or 2? In your sample data there are 2 columns of numbers, do they both get averaged?

 

[erinbe]

I'm taking one cell (based on the vlookup) from each sheet and average those values in another sheet (Agent Summary). Is that what you're asking?

 

[rdw72777]

So your formula currently looks something like the following (as seen on "agent Summary" tab)

=Average(April!A1,May!A1,JuneA1)

Is that correct?

 

[erinbe]

=Average(April!A1,May!A1,JuneA1)

Yes, but they are averages of vlookups...

Ex:

=AVERAGE(VLOOKUP($A4,'April 2011'!$A$3:$P$141,6,FALSE),VLOOKUP($A4,'May 2011'!$A$3:$P$141,6,FALSE),VLOOKUP($A4,'June 2011'!$A$3:$P$141,6,FALSE),VLOOKUP($A4,'July 2011'!$A$3:$P$141,6,FALSE),VLOOKUP($A4,'August 2011'!$A$3:$P$141,6,FALSE))

 

[rdw72777]

Hmm, that is tricky. I've heard something about AVERAGEIFS in Excel 2007 but haven't used it, that might help. Other wise you might have to a do a really long formula where you sum the values on all 12 tabs and divide by the count of non-blank values on each tab.

EDIT:

i tried this ugly formula and got the desired result:

=SUM(VLOOKUP("Jan",$F$5:$G$8,2,0),VLOOKUP("Feb",$F$5:$G$8,2,0),VLOOKUP("Mar",$F$5:$G$8,2,0),VLOOKUP("Apr",$F$5:$G$8,2,0))/SUM(IF(ISBLANK(VLOOKUP("Jan",$F$5:$G$8,2,0)),0,1),IF(ISBLANK(VLOOKUP("Feb",$F$5:$G$8,2,0)),0,1),IF(ISBLANK(VLOOKUP("Mar",$F$5:$G$8,2,0)),0,1),IF(ISBLANK(VLOOKUP("Apr",$F$5:$G$8,2,0)),0,1))

 

[erinbe]

That might be tough because there will be a couple thousands of this formula in a sheet...might take a while to compute. How would I write that kind of formula?

Any other ideas?

 

[T. Valko]

That might be tough because there will be a couple thousands of this formula in a sheet...might take a while to compute. How would I write that kind of formula?

Any other ideas?

Yeah, that's gonna be tough!

What version of Excel are you using?

 

[erinbe]

Excel 2002

....1012 formulas to be exact...guess a couple thousand was a bit over exaggerated.

 

[T. Valko]

Excel 2002

....1012 formulas to be exact...guess a couple thousand was a bit over exaggerated.

That's still a bunch!

Try something like this monster:

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"april","may","june","july"}&" 2011'!A3:A10"),A4,INDIRECT("'"&{"april","may","june","july"}&" 2011'!F3:F10")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"april","may","june","july"}&" 2011'!A3:A10"),A4))

How many months do you need to calculate, all 12? Jan thru Dec?