Format multiple rows to show lowest in each

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:37 PM GMT

Question

QUESTION: How do I do a conditional format to show the lowest vendor in this case out of a long list per row. I can do it for one row but I am having trouble copying the formula for multiple rows. I want to evaluate a bid result and highlight the lowest bid per row. each row will have it's own lowest value.

ANSWER: thomas feely,

since you didn't give specifics, I will assume a hypothetical:

If I had numbers in the range C4:M30, and I wanted to highlight the lowest number in each row, I would select C4:M30 with C4 being the active cell and put in the formula

=AND(MIN($C4:$M4)=C4,ISNUMBER(C4))


In97 to2003, you would go to conditional formatting and change Cell value is to Formula is and put in the above formula. (then set your format and so forth - I assume you know all that).


in Excel 2007 and later, you would go to conditional formatting in the home tab and select New Rule, then select "Use a formula to deetermine which cells to format". Then put in the above formula.

Always best to do the conditional formatting on multiple cells all at the same time.


Tested and that formula/method worked fine for me.

Note that it was tested in a range with all numbers and with a range that also contained some text and blank cells and worked well in both cases. Multiple occurances of the low number in a single row will have all the occurances of that low number in that row highlighted i.e. where there are ties for lowest bid, all the occurances that tied will be highlighted.


---------- FOLLOW-UP ----------

QUESTION: I am using2007 and the rows would be numbers as follows as a simple part but the final evaluation will be about 50 columns and 3000 rows so the need for the formatting is important. example 1 2 3 4 15 12 18 19 25 24 22 20

so that I get the lowest per row for evaluation

Answer

Highlight lowest per row

thomas freely

You haven't told me anything that would change my answer.

If the range was C4:AZ3003, then I would select C4:AZ3003 with C4 being the activecell and put in the same formula in the conditional formatting dialog as described for2007.

I have attached a picture of the worksheet - Obviously it is a small area of the big range C4:AZ3003, where I have applied the conditional formatting formula.

Let me know if you don't understand something or I need to elaborate.

Advertisement

©2021 eLuminary LLC. All rights reserved.