MAX function 1

[ab123456]

I have a sheet that performs calculations on a series of data and then uses the MAX function to identify the highest result.

My problem is that in the range of data in which im trying to find the max I have cells containing #value and #ref. Is there any way that i canignore (or treat as zero) these cells and return the highest value in the other cells?

 

[IRstuff]

You can create a surrogate column that only contains valid numbers, filled with something like:
=IF(ISNUMBER(D17),D17,0)

TTFN

FAQ731-376

 

[ab123456]

Thanks for the speedy reply.

Its a sheet that will be used by others in the company so i want to make it as simple to look at and understand as possible so i want to avoid unnecessary columns if i can.

I could always hide the surrogate column but is there a more elegant solution that anybody knows of?

 

[yakpol]

Use a array function, it allows to enter neccessary conditions inside. Like:

=MAX(IF(ISNUMBER(B6:B10),B6:B10,0))

Enter it by finishing with ctr-shift-enter key.

 

[ab123456]

Thats what im looking for.

I think i need to do a bit of research about array functions.

Thanks