Excel Conditional Formatting - Lowest value if unmerged cell above meets criteria

0
votes

Screenshot of spreadsheet illustrating problem with conditional formatting

I'm trying to use conditional formatting to highlight the lowest value in each row green as long as the unmerged cell above it does not read "N" (as I want that to disqualify the value in that case.) If the lowest value has the "N" (for No) above, I need to have the next lowest value highlighted green that doesn't have "N" listed. I have conditional formatting to highlight a value red if "N" is selected/entered. In my screenshot, the value in cell F11 is the lowest, but because it has an "N" above, the value in cell D11 should highlight green (with F11 staying red- If I change that N to Y, the cell turns green.)

Each item's "row" is made of two rows. On the upper row, the cells are unmerged. On the lower row, both cells are merged. The range of columns that I want to evaluate is D:CY (with CX being the last for the lower row since it has merged cells.)

Here is where I'm at with the custom formula:

=D11=IF(E10="N",SMALL($D11:$CX11,2),MIN($D11:$CX11))

I will be using this on versions as old as Excel 2013, in case that affects code support. I want to apply this to a range or rows below so I'm not sure if I've got my absolute $ addressing correct. The columns won't change except that E10 since I'd then want it to move down the row...

1
excelformattingconditional-statementsformulahighlight

1 Answers

0
votes

Perhaps a formula like this for your conditional formatting, based on COUNTIFS with staggered ranges to identify the lowest cell with a Y one row above and one column to the right:

=AND(D11<>"",E10<>"N",COUNTIFS($D11:$H11,"<"&D11,$E10:$I10,"Y")=0)

Change the H to CX and the I to CY to apply to your range.

enter image description here