BUG?! Equation not working after row insert

[teatimecrumpet]

Hi,

I got this formual working:

=AVERAGE(IF(Raw!Z3:Z502="In",IF(ISNUMBER(Raw!I$3:I$502),Raw!I$3:I$502)))/100

Which looks a portion of the Z column for the value "In" and averages the I column if the cells in I are numbers.

This formula is working in a row of cells. However, when I add a row above this row of equations all the cells change value. And the formula above won't work in any other cell but the one I put it in.

Any idea?

Thanks,
Mike

 

[teatimecrumpet]

CTRL + shift + enter makes the formula return an error. But I can enter the formula and press enter all day and the value comes through. I'm using excel 2007 if that makes a difference.


There are error values in column Z but this column I copy pasted the data from somewhere else so the formula is just looking for the text "In".

Another bit I just discovered. If I add two or more lines above the row with formulas in it. The values kick in. And again the formula still won't narrow it down to "In" values and is instead looking at all values.

 

[teatimecrumpet]

jonmo1 is right about the first part of the formula in column z needing an iserror for it to work.

I tested it out by chaninging all the #value! to some other word and the formula worked as a CSE formula.

Still a mystery on adding that extra line though.

Could someone help me with the iserror portion?

Thanks,

 

[teatimecrumpet]

Got it,

My formula should look like this now;
=(AVERAGE(IF(IF(ISERROR(Raw!$Z$3:$Z$502),0,Raw!$Z$3:$Z$502)="In",IF(ISNUMBER(Raw!I$3:I$502),Raw!I$3:I$502))))/100

Thanks for all the help!