MAX function
MAX function
(OP)
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?
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?





RE: MAX function
=IF(ISNUMBER(D17),D17,0)
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: MAX function
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?
RE: MAX function
=MAX(IF(ISNUMBER(B6:B10),B6:B10,0))
Enter it by finishing with ctr-shift-enter key.
RE: MAX function
I think i need to do a bit of research about array functions.
Thanks