dismissing errors

[silentcoates]

Hello,

I have quite a few probs that I need to resolve I will try to list them all. I just want to thank you all for your help as this website has been invaluable to me.

anyway back to business.

I have a table and in one of the columns I have some percentage scores (this column has a formula in each cell =VLOOKUP(B4,'Input Sheet'!H2:L130,5,0) )some of the cells show DIV/0! (an error value or something like it) How do I get the cells to show a 0 value so whe I add a total cell at the bottom of the column it works as at the moment it shows DIV/0!

Also I have a list of names in alphabetical order next to which I have a list of scores (a percentage) on sheet two I have a list of 5 names from that original list. I need a formula to show the average score for the 5 names.

hope these all make sense

thanks

 

[NBVC]

Why are you getting the Div/0 error? You should really try to fix that first. If you can't, then try:

If(iserror(VLOOKUP(B4,'Input Sheet'!H2:L130,5,0) ),0,VLOOKUP(B4,'Input Sheet'!H2:L130,5,0))).


For the second question Try:

=Sumif(range to check,criteria,range to sum)/Countif(range to check,criteria)

 

[Zack Barresse]

Hi,

To dismiss a #DIV/0! error you can use something like htis ..

=Numerator/MAX(1,Denomenator)

And it sounds like you want an IF/AVERAGE formula. You can use a CSE formula for this (assuming your data ranges ..

=AVERAGE(IF(Sheet1!$A$1:$A$20=A1,Sheet1!$B$1:$B$20))

This is assuming your range of names to check is in Sheet1!$A$1:$A$20 and your name to check is in A1. Also that your range of figures associated with your names are in the neighboring B column of Sheet1.

The Avg formula(s) must be confirmed with Ctrl + Shift + Enter, not just enter; hence them being called CSE formulas, or array formulas.

Is this what you were looking for?

 

[silentcoates]

the only reason I can think of that I am getting this error is because the data that the link links to is entered over a period of time so some of the cells are blank untill I need to add data to them.

can you help?

 

[NBVC]

Where is the division being done?

Perhaps at that point, you can say something like, =If(cell1="",0,cell2/cell1) or whatever your division statement might be. The important thing is to replace the error with something more tangeable.

Then you could use your original vlookup statement:

=VLOOKUP(B4,'Input Sheet'!H2:L130,5,0)))