Automatically Highlight Minimum Value

[drewbagel423]

I have a spreadsheet where I would like to subtract two numbers (as shown below), then find the minimum value in that column (column D) and highlight it in red.

A1 B1 C1 (C1-B1)
A2 B2 C2 (C2-B2)
A3 B3 C3 (C3-B3)

Then, elsewhere in the spreadsheet, I would like it to do basically the same thing. Except, instead of highlighting the cell, in a completely different cell (D4, for instance), have it call out the row header in column A corresponding to the row which contains the minimum value. For instance, if D2 is the minimum, D4 will say "Apples" or whatever the text contained in A2 is.

TIA

[shg]

Select the cells in column D and, assuming they start in D2, do Format > Conditional Formatting, Formula is: =D2=min(D:D) and format as you wish.

[drewbagel423]

yeah i was going to do that but it only lets you do 3 rules and i need to do this in several areas and it's more than CF can do

[shg]

To have more than three rules (which can include many more than three conditions), you need VBA, in which case you should be posting in the Programming forum.

You can find many examples by searching.

[drewbagel423]

i wasn't sure what functions would give me what i wanted... can this thread be moved for me?

thanks

[shg]

Moved to programming forum.

[mikerickson]

The array formula
=INDEX(A1:A10,MATCH(MIN(C1:C10-B1:B10),(C1:C10-B1:B10),0),1)
will return the A value of matching the minimum of C-B. The rows can be adjusted as needed, but all the rows should be filled otherwise an empty row returns a difference of 0.
Dynamic ranges could be used to remove the need to adjust the formula to new data rows.

If that is in D1, the Conditional formatting formula
=($A1=$D$1)

Will highlight the row on which that minimum is found.

I don't understand why you need more that one CF condition. There is only one minimum.

Array formulas are confirmed with Ctl-Shift-Enter. (Cmd+Return for Mac)

[drewbagel423]

I don't understand why you need more that one CF condition. There is only one minimum.

I thought that it was only 3 conditions for the whole sheet. I didn't realize you could set rules for individual cells (or groups of cells).


What if instead of highlighting the minimum value in column D, I wanted to highlight the header in column A corresponding to the minimum value in column D?

[mikerickson]

The absolute column references in that formula means that it can be used to highlight cells in any column.
Putting that CF formula in any cell will highlight the cell if the Column A entry for that row is the same as $D$1.

If you have duplicates in A:A this may cause a problem.