Excel 2007 : ignore #NAME?

[baek2]

Hello

I have 25 cells with values in 8 of them and #NAME? in the rest.

Is it possible to make a formular which takes the average of the given values but ignores cells with #NAME? in them?

Thanks

[baek2]

I have something like this in mind

=AVERAGE.IF(AJ5:AJ13;X;AJ5:AJ13)
Where the X is a criteria which ignores cells with #NAME?

[Andy Pope]

Are you not concerned about the #NAME cells?

Try this array formula, use CTRL+SHIFT+ENTER to commit formula


=AVERAGE(IF(NOT(ISERROR(A1:A25)),A1:A25))

[baek2]

Are you not concerned about the #NAME cells?

No because the #Name? cells is only temporary as i get new data for them every couple of days so their time is limited.

[baek2]

The formular works but it gives a result that is quite a bit off, the average is -4,65 when not including the #NAME?'s and with your formular it is 9,24?

[baek2]

Er, well this is quite emberrasing, but i made quite a rookie mistake and forgot to change which cells the formular should take the average of

Thank you very much Andy it works perfectly now

[DonkeyOte]

Given use of XL2007 you should find the below will work (assumes no other errors obviously):

Please Login or Register  to view this content.