max number of a spreadsheet but exclude 3 numbers

hi

i have a worksheet filled with numbers in each cell (the worksheet might have a total of 150 rows and 150 columns of numbers).

the numbers typically range from 1-3 or 1-9 or 1-150.

however, there are also multiple 997, 998, and 999 numbers in the cells.

i need to identify the highest number in the worksheet that is not 997, 998, or 999.

can someone help?

You can use an array formula like this

=MAX(IF(range<997,range))

confirmed with CTRL+SHIFT+ENTER

or a non-array version

=LARGE(range,COUNTIF(range,">=997")+1)

either way replace range with your specific range......

shoot. i'm kind of a newbie so i'm clearly doing something wrong.

here's the current code (but it returns the maximum value which will be 997, 998 or 999):

Please Login or Register  to view this content.

here are the ways i tried to implement your suggestion (replacing 'get max above); all of them show as red font meaning there is an error in them:

Please Login or Register  to view this content.

hmm. maybe the problem is on the worksheet itself.

this is a complicated spreadsheet that two different people have worked on prior to/in addition to me.

when i select what should be r4c4:r10c10, i get this:

Please Login or Register  to view this content.

i was able to do this on the worksheet itself but i still don't see how to do it excel vba code.

i have pasted in the code as you explain and it doesn't work. i get error messages. i tried in multiple ways to adjust it and none worked.

in the previously attached spreadsheet, which i have changed on my computer to an .xlsm file--i get a runtime 13 error, type mismatch.

i suppose i could do this as a formula on the page but i am worried (in fact, i'm almost certain) that other code in the program erases all data/functions on the worksheet (when you begin working on it, before you enter the data).

thank you.

i'm still having issues getting this working in the spreadsheet itself but this gives me a good start.

sorry for the hassle.

the problem appears to be because the spreadsheet uses the r1c1 format.

i've tried to change the formula to Cells (2,2),Cells(30,30) and i just get errors.

can you repost the file with the code but in r1c1 format?

(why r1c1 format? the person who originally did the spreadsheet uses that to do the most complex parts of the spreadsheet and so i need to stick with it).

well, i need something in the code itself since i need to make these kind of calculations (max excluding 3 numbers) in various parts of the code.

one way i now see to do it is (where i switch to a1 style, do the code, and then switch back to the r1c1 style at the end of the code):

Please Login or Register  to view this content.