Re: Highlight lowest values based on criteria
Thanks a lot for your help, working like a dream now!
Re: Highlight lowest values based on criteria
Thanks a lot for your help, working like a dream now!
Re: Highlight lowest values based on criteria
Quote from NBVC;783328Then, as per your original formula...
=AND(B2=MIN(IF(A$2:A$3000=A2,IF(B$2:B$3000<>"",B$2:B$3000))))
Oh, last thing, how do I make the hotel name highlight aswell as the cost?
Re: Highlight lowest values based on criteria
Quote from NBVC;783328Then, as per your original formula...
=AND(B2=MIN(IF(A$2:A$3000=A2,IF(B$2:B$3000<>"",B$2:B$3000))))
Genius!! Thanks a lot!
Re: Highlight lowest values based on criteria
Quote from Logit;783322Display More.
.
Highlight both columns
Click SORT in menu on DATA tab
First rule: HOTEL / VALUES / A to Z
Second rule: COST / VALUES / SMALLEST TO LARGEST
The highlighting (Conditional Formatting) is up to you as desired.
(See image)
[ATTACH=CONFIG]71009[/ATTACH]
Hi
that wont work as the list is ever expanding, so i'd have to sort it every time I fill it in
Re: Highlight lowest values based on criteria
Quote from NBVC;783324Try using this as your conditional formula:
=AND(B2=MIN(IF(A$2:A$3000=A2,B$2:B$3000)))
That seems to be working! but... where I have hotels in column A and no costs in column B (I have Hotel 1, 2, 3 copied down so I can just fill in the figures when I search) it stops the conditional formatting from working, but if I remove all the "template" hotel names in column A, it works fine. Any ideas? Ideally I don't want to copy the hotel names each time I fill it in.
Re: Highlight lowest values based on criteria
Quote from Logit;783316.
.
Try this :
[ATTACH=CONFIG]71008[/ATTACH]
Hi, that's just highlighting all the lowest 3 prices, all the £600, £700 and £800 values. It isn't matching the lowest prices per hotel e.g. it's showing 3 prices for Hotel 2, whereas it should only be showing £600 for Hotel 2.
Re: Highlight lowest values based on criteria
Quote from Logit;783261Display MoreUsing a helper column C1 : C3
In C1 paste this formula
In C2 paste this formula
In C3 paste this formula
Then you could use Cond Format to highglight those cells in Col A
Hi, all that's doing it showing the lowest 3 values, it isn't matching it up to the particular hotels
Hi all
I'm working on a spreadsheet to help with holiday searching. Each day I search holidays and enter the hotel and cost into a spreadsheet. I want the spreadsheet to highlight the cheapest cost (lowest value) for each hotel I input. I've put together the below example, Column A is the hotel and Column B is the Cost. I want the spreadsheet to automatically highlight the lowest cost for each hotel, so for example in the below list, the cheapest cost for "Hotel 1" is £800, "Hotel 2" is £700, and "Hotel 3" is £600, so it's these cells i'd want highlighting. I'm guessing conditional formatting is the answer, but I can't figure out the formula to get it to work.
I've got it to work to show the value when I put this formula in a cell, but I want the cells themsevles to highlight. This is what I have (end value set at 3000 since the list will be ever expanding as I complete it each day):
=MIN(IF(A$2:A$3000=A2,IF(B$2:B$3000<>"",B$2:B$3000)))
Any help is much appreciated!
[TABLE="width: 240"]
(A) HOTEL
[/td](B) Cost
[/td]Hotel 1
[/td]£800
[/td]Hotel 2
[/td]£800
[/td]Hotel 3
[/td]£800
[/td]Hotel 2
[/td]£700
[/td]Hotel 3
[/td]£900
[/td]Hotel 2
[/td]£900
[/td]Hotel 3
[/td]£600
[/td]Hotel 2
[/td]£1,000
[/td]Hotel 1
[/td]£1,000
[/td]Hotel 1
[/td]£1,100
[/td]Hotel 1
[/td]£1,200
[/td]
[/TABLE]
i've got a list of values that need summing, but only if i certain value is in another cell:
a b
1 paid
1 paid
1 not
1 paid
1 not
i need a formula that will count all the values in column a only if column b is set to "paid"
I need a formula that If One Cell Equals Certain Text, Then Another Cell Equals The Value Of Another Cell...
at the mo i've got
=IF(A2="PAID OUT", "OK", "Not OK")
but instead of the cell reading OK or Not OK, i need it to equal another cell. ie, if A2="PAID OUT", then =B2
how do i do that?