To find the lowest value in fill colour.......

[deepakbathla]

Respected Sir,

Warm Greetings of the day.......

I want to know in problem 1, how can I calculate lowest value1,2,3,&4 between fourth members.

With the help of which formula , how can i difference-2 colour according to their lowest value1,2,3 &4.

suppose lowest value of john 10 in 1st & 2nd row and in 3rd row lowest value of hari so i want to 10 would be yellow colour and harry value 19 red colour.


Problem:1 John Fernando's Lucy Harry

10 15 20 21

16 17 18 19

20 25 35 19

35 40 45 34


Regards/ Thanks

Deepak Bathla

09891272628

 

[SirJB7]

Hi, deepakbathla!


I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).

Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.

Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, well, come back here, tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question, as far as I know there is no formula to handle directy cell colors, yet font or yet fill/backcolor.

You have two workarounds: the first, using conditional formatting (from the Start tab, Styles group, Conditional formatting icon, New Rule, first entry (apply format to all cells accordingly to their values) and play a little around there; the second, is entering macro world and get involved with a little of VBA code -Visual Basic for Applications-. It's not so difficult but it's not so easy as to start from.


Hope you have a starting point now, just try and let us know if you find any trouble.


Regards!

 

[srinidhi]

I guess your heading & u r problem is opposite of each other.

As per my understanding you want to find out the min value from 4 columns, say u r data starts from A2 to D2 , in E =SMALL(A2:D2,1), for finding the min value & in F =SMALL(A2:D2,2), for finding the 2nd min value, in the last empty cell in E write the formula,=SMALL(E2:E5,1)& same for F column as well, now apply conditional formatting for A2:D5 & select the cell where the min value is there in E column & apply the color u want.


I hope this what is u r looking for, if not, pls post the worksheet here.

 

[Luke M]

In addition to what srinidhi said, you could use a formula in a Conditional Format formula to color the cell with smallest value. CF formula, with A2 active, would be something like:

=A2=SMALL($A2:$D2,1)

Format yellow


Note that you can change the last argument of the SMALL function to get the Nth smallest value of the range specified in 1st argument.

 

[Faseeh]

Hi deepakbathla,


You can actually conditional format row and define which color to be the first, second third and fourth, i will try to give you a formula for that.


Luke M is absolutely correct, I was also working this formula. so you can just go with that formula.


Regards,

Faseeh

 

[deepakbathla]

Thanks a lot.......Sir/Ma'am....SirJB7,srinidhi,Luke M & Faseeh...

According to your suggestion my half problem has solved out & now half pending yet (about filing the colour) so i want to know, how can i fill the colour lowest value.

I want to ask 1 think more if i want to send excel sheet, what should i attach it?

As per Suggestion LUKE M...i found the lowest value as i want.

Finaly i want to fill the colour in these lowest value.


John Fernando's Lucy Harry Lowest

10 15 20 21 10

16 17 18 19 16

20 25 35 19 19

35 40 45 34 34

In this table i have captured the lowest value by the formula. If you copy this sheet and paste excel. you will see the formula and now i want to fill the colour in John (10), Fernando's (16), Lucy (19) & Harry (34) by formula.


Regards/ Thanks

Deepak Bathla

09891272628


_______________________________________________________________________________________

 

[Luke M]

Deepak,


As I stated above, you need to use conditional formatting. Right now, you just have a formula in a CELL, that probably looks like:

=SMALL(A2:D2,1)


What you need to do is select the entire table, and with A2 being the active cell (cell you are currently looking at), the Conditional Format formula is:

=A2=SMALL($A2:$D2,1)


If you want to upload a workbook, please see the 3rd sticky in this forum

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[srinidhi]

Deepak,


In home tad > conditional formatting > New Rules > Use the formula to determine which cell to format, in the formula bar =A2=SMALL($A2:$D2,1)> Format > Fill Tab & select the colour you want to apply for the first least value, I hope now even the other 1/2 of u r problem is solved.