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

 

[xenou]

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

Try using the same relative/absolute addressing as the rest of your formula:
=AVERAGE(IF(Raw!Z$3:Z$502="In",IF(ISNUMBER(Raw!I$3:I$502),Raw!I$3:I$502)))/100

 

[konew1]

I think this is meant to be a CSE formula. Select the cell, press F2 on the keybboard, the hold CTRL+SHIFT+ENTER.

 

[AMAS]

This may be a dumb question, but if its a CSE formula, then what is the purpose of holding down the F2 button? Isn't Ctrl+Shift+Enter enough or is this a special case?

AMAS

 

[xenou]

I guess F2 is just to put the cell in edit mode (in other words, re-enter the formula). It's not meant to be done simultaneously with Control-Shift-Enter, but first, before CSE. I'm not sure if it's the problem per se but yes it does seem like a CSE formula.

 

[AMAS]

Thanks for the explanation Xenou.

AMAS

 

[teatimecrumpet]

Hi All,

I've tried entering it as a CSE formula and adding the "$" as xenou suggested but no luck.

When I enter holding shift and ctrl I the value turns into a "#value!" which is the same as if I added a row above it.

Also the formula (before the line add) seems to be averaging properly as I entered a very large number in a cell in column "I".

Is there another formula I should try using instead?

 

[Jonmo1]

I think we need more specific information.

1. Where are you entering the formula (what sheet / what cell address)?
2. What does the formula look like BEFORE inserting a row?
3. WHERE are you inserting a row (On what sheet, between row xx and yy) ?
4. What does the formula look like AFTER inserting the row?
5. WHY are you inserting a row?

Instead of inserting a row, perhaps just paste the new data to the bottom of the range and sort the data.

 

[teatimecrumpet]

Hi Jon,


1. I have a sheet called "Raw" where all the raw data resides and a sheet called "Summary" where the formulas and graphs/charts are. The formulas in Summary run from G5:T5.

2. G5 displays a value and the formual is
=AVERAGE(IF(Raw!Z$3:Z$502="In",IF(ISNUMBER(Raw!I$3:I$502),Raw!I$3:I$502)))/100

---However, I noticed that the formula isn't averaging values that correspond to "In" values in Column "Z". It's just averaging everything up in column "I". Entering the formula as Array produces a "#value!" but just simply pressing enter returns a value.

3. I'm inserting a row above row 5 (row with formulas) and row 4.

4. After row insertion the formula reads the same:
=AVERAGE(IF(Raw!Z$3:Z$502="In",IF(ISNUMBER(Raw!I$3:I$502),Raw!I$3:I$502)))/100

but the cells have either #value! errors or values or are zero

5. I'm inserting a row for a nother formula that I want to use that summarizes different information that is more aesthetic towards the top. (as the following rows summarizes more foucsed data/ is a breakdown of the whole).

I guess I could paste values and then add my row and new formulas. However, the formulas should be working regardless of a new row?

Thanks for the help!
Mike

 

[Jonmo1]

Fist, the formula must be entered with CTRL + SHIFT + ENTER.

Are there any Error values in column Z ? If so, you'll need to add the IF(ISERROR for that column as well.