How to make AVERAGE function ignore blank cells that are the result of LOOKUP?

[neiljuk]

Hi guys,

I'm a total newbie and looking for some help for a spreadsheet I'm trying to make for work.

It's a scorecard and I need to get the average value of 8 cells in one row, where each cell contains text apart from one which contains a number. So I'm using LOOKUP where No = 0 and Yes = 100. This works fine when there is Yes or No in each of the cells, however on some rows I only want the average of 5 cells, and do not want it to average cells containing N/A.

My formula (there is probably a far more efficient way of doing this, but that's beyond me)

=AVERAGE(LOOKUP(B3,{"No","Yes"},{"0","100"}),LOOKUP(D3,{"No","Yes"},{"0","100"}),E3,LOOKUP(F3,{"No","Yes"},{"0","100"}),LOOKUP(G3,{"No","Yes"},{"0","100"}),LOOKUP(H3,{"No","Yes"},{"0","100"}),LOOKUP(I3,{"N/A","No","Yes"},{"","0","100"}),LOOKUP(J3,{"N/A","No","Yes"},{"","0","100"}),LOOKUP(K3,{"N/A","No","Yes"},{"","0","100"}))

I hoped that by having LOOKUP see N/A as a blank cell it would ignore it, but it does not.

B3, D3, F3, G3, and H3 are drop down boxes with the options of Yes and No
E3 is a value between 0-100

I3, J3, K3 are drop down boxes with the options of Yes, No and N/A

This works perfectly as long as N/A isn't selected for a cell, but I get a #VALUE! error when N/A is selected. When I click show calculation steps, it looks like the formula is struggling to divide by a blank cell.


Any help would be greatly appreciated!

 

[sheetspread]

Why not use averageif without a lookup?

 

[mole999]

if you have 2010 onwards then have a look at AGGREGATE and its options

 

[admiral100]

Hi,

For Excel 2007 you can try

=(COUNTIF(B3:K3,"Yes")*100)+(COUNTIF(B3:K3,"No")*0)+(E3)/(COUNTA(B3:K3)-SUMPRODUCT((--ISNA(B3:K3))))