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!
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!