Hi forum,
I have five columns D3 to H3 all with different amounts/currency value.
I'm looking to get a formula or use conditional formatting to scan those five cells and highlight one of them green when Excel has determined which one is the cheapest out of the five.
Your help is appreciated!
Much thanks!
Last edited by HHR; 01-31-2013 at 02:49 PM.
I put in fake values in D3:H3 and I tried highlighting the cells, opening the Conditional Formatting and putting a this formula: =min(d3:h3) and get it to turn green under the format section, but all this is doing is making all the cells from D3:H3 all green.
Try this (Procedure on a Mac, but it should be similar on PC):
1. Select D3:H3
2. Click on Conditional Formatting
3. Select 'Top/Botom Rules'
4. Select 'Bottom 10 Items...'
5. Change the value of 10 in the text box to 1
6. Choose your formatting, and click OK
- Moo
Trying to use a formula you would have to highlight D3:H3, then use this:
=D3=MIN($D$3:$H$3)
- Moo
Moo,
The first thing almost worked, except, I guess I should have explained that this needs to apply from D3:H30 (a block of cells that will have values in them). I thought if I could get it to work for just one row (D3:H3) that it would work for the whole sheet, not the case.
Each row has different information and I want ONLY between D and H to (each seperate row) to turn green when a value is lowest.
Example: in row A3 I have an item name, in B3 I have a description, in C3 I have a quantity and between D3:H3 I have different values.
In row A4, I will again have a different item name, in B4, a different description and C3 a diff quantity, with completely different values between D4:H4.
One each line, I need whichever value is the lowest to turn green.
so, whichever is lowest between D3:H3, I need it to turn green. On the next row/line, I need D4:H4 to again, have the same rules apply.
Just realized, the formula above only highlights the lowest value for the entire range -what was I thinking?... gah
Highlight D3:H30, and change the formula to:
=D3=MIN($D3:$H3)
- Moo
Last edited by Moo the Dog; 01-31-2013 at 12:25 PM. Reason: Updated formula... hopefully THIS one works!
Moo, it works great; however, until I fill in the rest of the sheet, all the cells are green just waiting for values. How can I get them to stay clear until there are values in there?
Here is a copy of the excel sheet.
Try this (also see attached modified sheet)...
Select D3:H30 again, and add an additional CF formula to that selection (above previous rule!): =D3=0
Clear any formats (font / border/ fill) so they are at normal settings.
Be sure that it is above the earlier formula, so that it is evaluated first.
- Moo
Thanks Moo, however, it still doesn't solve my problem. If there are ANY values at all in one row, $3.00, $2.50, $0.00, $1.20, $5.60, I'd need the value of $1.20 to be highlighted only. With your excel sheet, if one cell is left at zero, nothing goes green in that whole row. I may have zero's in there and would need the zero's to stay clear, while the lowest number in that row goes green only.
OK, try this then:
=D3=MIN(IF(D3:H3>0,D3:H3))
- Moo
Technically the $0.00 is the lowest value, I know, but the entire sheet will all be zero's until I get numbers to fill in there.
This excel sheet will be used for Quotes received from vendors. If I do not receive a quote from a vendor, then it'll stay at zero, but is not necessarily the lowest cost.
See my new formula above in post #11
Moo, I put that in there and its working (for the zeros), but its highlighting multiple cells in the same row not just the one for some reason.
Gah, I forgot the $ for absolute referencing... this seems to work:
=D3=MIN(IF($D3:$H3>0,$D3:$H3))
Yeah!! It worked!!!!!
Thank you! Thank you! Thank you!!!!!!!!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks