Averageif and vlookup

[jgedwardsv]

Hi all!

I'm trying to pull averages and exclude any zeros from the calculation. What I have right now is:

=AVERAGE(VLOOKUP([@SeriesName],FundData[[#Headers],[#Data],[FullName]:[2000]],{4,5,6},FALSE))

Unfortunately this formula treats anything in that array that comes back that is blank as a zero. I have tried AVERAGEIF with the "<>0" criteria, however, excel keeps giving me an error. Is there something I am doing wrong or does excel averageif not work with a vlookup array?

Thanks for your help!

John

 

[FDibbins]

Have you tried using =averageifS()?

 

[jgedwardsv]

I have - averageifs says too few criteria - I only want to average if there is something in the cell, not if it is blank or 0.

averageif gives me the error that I either need to remove quotes from "<>0" or add them if I don't put them in the formula.

 

[BarryL]

Either of the below work?

=AVERAGEIF([#Data],"<>"&0)

or

=AVERAGE(IF(ISBLANK([#DATA]),"",[#DATA]))

entered with ctrl+shift+enter

 

[jgedwardsv]

the first option returns the same error.

The second puts a value error where there used to be zeros.

Is this an issue because of the vlookup returning null cells as 0's?

 

[BarryL]

are they indeed null or formatted 0's? is the format of the cells accounting?

 

[jgedwardsv]

Yes, the cells are left blank. Everything is formatted as a % including the ones that are empty. Would that be the issue?

John