Identify the highest value over a range of cells and classify the result

Hi all,

I have a test form which requires the user to conduct 3 identical tests and record the results. with the 3 readings they need to have an average value and classify the result based on the highest value over the 3 tests.

The parameters of classification are as follows..

above 100 = Fail

Equal to or below 100 but equal to or above 51 = Class 1

Equal to or below 50 but equal to or above 26 = Class 2

Equal to or below 25 but equal to or above 11 = Class 3

Equal to or below 10 but equal to or above 6 = Class 4

Equal to or below 5 = Class 5

The formula I have is =IF(N85:V85="","",IF(N85:V85="N/A","No Requirements",MAX(IF(N85:V85>100,"Fail",IF(N85:V85>=51,1,IF(N85:V85>=26,2,IF(N85:V85>=11,3,IF(N85:V85>=6,4,5))))))))

But doesn't work. Anyone have anything up their sleeve that will work for all of the above?

Help, as always, is very highly appreciated

Not sure why you r are testing a range (3 results in this range?) plus as a possible results is "FAIL" (text) but testing for MAX (Numeric).

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Try this ...

=IF(COUNT(N85:V85)=0,"",IFERROR(LOOKUP(MAX(N85:V85),{0,6,11,26,51,101},{5,4,3,2,1,"Fail"}),"No Requirements"))

Originally Posted by JohnTopley

Not sure why you r are testing a range (3 results in this range?) plus as a possible results is "FAIL" (text) but testing for MAX (Numeric).

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Basically we have to conduct several tests 3 times, record the results and then work out the average of the 3 results which then we can classify based on the ranges specified above. Managed to work that out no problem, however one of the tests we conduct in the series asks for the same classification to be applied but instead of the classification being based on the average of the 3 results, it asks for the highest value of the results to be classified if that makes sense?

so my working formula for the classification based on the average result is =IF(N85="","",IF(N85="N/A","No Requirements",IF(W85>100,"Fail",IF(W85>=51,1,IF(W85>=26,2,IF(W85>=11,3,IF(W85>=6,4,5))))))) which works perfectly

So I am looking for a solution which does the same job as the above formula, but uses the highest value out of the 3 results

I hope this better explains my problem

Dont worry, I have managed to figure a working formula out... Thanks for the effort tho guys

=IF(N88="","",IF(N88="N/A","No Requirements",IF(MAX(N88:V88)>100,"Fail",LOOKUP(MAX(N88:V88),{5,10,25,50,100},{5,4,3,2,1}))))