Highlighting first value that is just above cut-off and returning a different cell

Hello all,

I struggling with generating a formula or multiple formulas to return what I need.

Essentially I would like a formula that runs though cells B26 to B33 (bottom table), looks for the cell that is just above but not equal the cut-off value (in J35) and highlights the box in yellow. e.g. in B26 to B33 we the first value 0.091 is one just above the cut-off, the one below is 0.090 which is equal to the cut-off.

Exception: if the values never go =< cut-off, then to highlight cell in the last row (e.g. H33 where the values remained above cut-off

2) Once highlighted, i'd like to have an additional formula that returns the equivalent cell in the table above. e.g. B26 to B33, it the top value (0.091) in row A of the bottom table, so i'd like to return the value equivalent to row A in the top table i.e. 100 in cell B10.

I have tried many formulas with little success particularly with 1).





Capture 2.PNG

A picture is worth 1000 words, a sample sheet is worth 1000 pictures!!

You have attached a non-editable picture of an Excel sheet. That's not very easy to work with. Also... I'm lazy. I have to re-type your information before I can begin to address your problem. That puts me off completely.

So.... Do yourself a favour and please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough). However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever).

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Make sure confidential information is removed first!!!!

So.. How much above the cut-off is "just above the cutoff"?? 0.095, 0.1, 0.2???

... or are you looking for the FIRST value in EACH column that exceeds the cut-off

Ahhh. Measurement of p-nitrophenol in microtitre plates... That takes me back a few years!!

OK. First part (which caused me enough problems because of the two values of 0.091 in the 8th column):

use this as the CF formula:

=B26+ROW(B26)/10^10=SMALL((B$26:B$33+ROW($B$26:$B$33)/10^10),COUNTIF(B$26:B$33,"<"&$J$35)+1)

Next thing. Explain (slowly) what you want for your exception...

applied to the whole range.

See sheet.

So, I begin to follow you. Do you mean if ALL the cells in the PLATE remain above the C/O highlight the entire last ROW

or do you mean

if ALL the cells in any individual column(s) remain above the C/O, highlight the bottom row of THAT column??

To return the matching value from the sample table:

=IFERROR(1/(1/INDEX(B$10:B$17,MATCH(SMALL(B$26:B$33,COUNTIF(B$26:B$33,"<"&$J$35)+1),B$26:B$33,0))),"")

OK. Firstly, answer the Q in Post 10 that you didn't address.

Secondly, the precision of the Absorbance readings from your plate is 2 dp. The precision of your cut-off is 15 dps (Excel's default). What degree of rounding, if any, do you wish to apply to the cut-off? This affects, for example, the answer for column 1, as the value (0.09) is being compared with 0.0902222523976801.

Explain the result you want for column 6, where one value (E6) exceeds the cut-off.

I have it done... on my laptop, which I will be beside in about 30 mins. So, look back then!!

It was still wrong, hence the delay!!

GREEN - your desired shading (note that rounding the C/O means that 0.085 is the first value < cut-off and so 0.90 is the value in the row above.

Orange - last row highlight

Yellow - matching values from Table 1.

Hopefully that's it!!

OK. But one last question from me. What should happen in lane 7 where NO values are < C/O?

OK. we'll get there!! Here's a version that I think covers everything.