Excel formula

[lamaar]

OK, here goes. To simplify things, on one sheet, I have a row which contains the numbers "01" to "12" to represent the month of the year. Also on this sheet is other data which is relevant to a particular month.

On my second sheet, I use a formula to look a the row which contains the month, and, if it is say "01" - for Jan, it averages up the data for January.

I use the formula "=AVERAGE(IF('Sheet1'!$N2:$N251="01",'Sheet1"!$J$2:$J251))

If the Cells containing the date have not been entered yet (as we are only in April so I only have data relevant to "04&quot then I get the error "#DIV/0!. So basically, I am trying to divide by numbers which don't yet exist.

So, to combat this, I change my formula to this: =IF('Sheet1'!N2:N252="01",(AVERAGE('Sheet1'!J:J,"0&quot)). Therefore, if the cell is equal to "01", average the numbers, otherwise the answer is "0". However, If I change the "01" in the first part to "02", "03" etc, I get a FALSE error message! e.g =IF('Sheet1'!N2:N252=" 02 ",(AVERAGE('Sheet1'!J:J,"0&quot)).

Also, If I go to the first worksheet and find the average of the cells n2:n251 I get 22.06. If i reference then from the secong worksheet, I get 21.09!!!

Any help would be appreciated as I am going mad!

Lamaar75@hotmail.com

 

[KenWright]

Have no idea why you should get different answers just because you are on a different sheet unless you are somehow referencing incorrectly, but the following should fix the #DIV/0 problem:-

=IF(ISERROR(AVERAGE(IF(Sheet2!$N2:$N251="01",Sheet2!$J$2:$J251))),0,AVERAGE(IF(Sheet2!$N2:$N251="01",Sheet2!$J$2:$J251)))

Regards
Ken..................