Help with Excel: Colouring function results ....

[Oggin Aviator]

Hello

Just wondered if I could pick your collective brains for an answer on an Excel problem I am having.

basically I have put this formula

=IF(B5>((20*E2)-0.1),"Green",IF(B5>((10*E2)-0.1),"Yellow",IF(B5>((6*E2)-0.1),"Orange",IF(B5>(E2-0.1),"Red","Dead"))))

into cell D5 with figures entered in cells B5 and E2. It compares the two cell values and in cell D5 gives and displays a result, (dead, red, orange etc etc) depending on their relative value. I got this formula using the help section. It does work.

Anyway, it would be nice if once the result (red, orange etc etc) is calculated, it is displayed in that colour, or in black bold with background fill of the cell D5 that corresponding colour. My question, how can I do this? At the moment all it does is display the word as black on white, bold or not (or however cell is formatted).

Cheers

Oggin

[oldfella]

I've done this in the past and it's all to do with conditional ifs. If I find the answer or an example I'll mail you.

Found it.

In Excell select the cell you are interested in.
In the toolbar select Format, then select Conditional Formatting.
Select Format from the new small window that comes up.
From there you can select font colour or select Patterns to choose a cell highlight colour.

[Oggin Aviator]

oldfella - thanks for your time.

I tried the conditional formatting, using values of ="Green" or ="Yellow" etc, formatted the cell etc but it didnt work. Just wondered if you could see where I was going wrong? Do I need to assign a numerical value vice a word?

cheers

Oggin

Edit: I was using the wrong conditional formatting formula (doh!) - now fixed it. Thanks for the tip oldfella which put me in the right direction.

[ExGrunt]

Hello Oggin,

Is the -0.1 significant? As an observation it is generally a 'bad thing' to have constants in formulae.

If the object is just to ensure that, for example, values =20*E2 are shown as "Green" then this is a cleaner fomula:


=IF(B5>=(20*E2),"Green",IF(B5>=(10*E2),"Yellow",IF(B5>=(6*E2),"Orange",IF(B5>=E2,"Red","Dead"))))

Ideally you would also have the values 20,10 & 6 in a table so that users of the spreadsheet can check them without examining every individual formula.

A final word of caution about conditional formatting, It will stop evaluating the conditions at the first condition it matches - so you need to think carefully about the order in which you set up any conditional formatting.

HTH

EG

[Oggin Aviator]

EG

Thanks for your reply.

I want anything over and including to be labelled green. Therefore any value above 19.9 is good, and I'm not worried about 19.95 being green or yellow. Therefore I put the -0.1 in there. If I left it at =IF(B5>=(20*E2),"Green", then the value of 20*E2 exactly would appear as yellow, not green. Unless I'm doing something wrong of course !!!
A final word of caution about conditional formatting, It will stop evaluating the conditions at the first condition it matches - so you need to think carefully about the order in which you set up any conditional formatting. Yes I want it to, and this method works. I originally had it the other way round and it didnt work!
Thanks for your input.

Cheers

Oggin

edit to say: Just to clear things up, this is for a small spreadsheet to tell me my stack inflection point value compared to the pot whilst playing online poker (if anyone understands this) !! I tried it online yesterday and as long as I update it every hand which doesnt take long (just update my stack size and the starting pot size, then it quickly colour codes my inflection point value (called M for some bizarre reason). So by glancing at the spreadsheet, the colour quickly alerts me to how well I am doing and more importantly how long I have before someone eliminates me - lol
If anyone else out there would like to play with it, please PM me and I will email it across. It does work and is great for single table or multi table tournaments. It also calculates the other players' M values and colour codes them, to help you figure out how close they are to not being around anymore and hence the liklehood or not of them getting really desperate.