Conditional colour fill

[Bebbspoke]

I've a large columnar data base with Row1 as header. I wish to create a new column "X" which obeys (e.g.): -

where (say) values An<Bn<Cn<Dn then Xn is a (specified) fill colour

Try as I may - I cannot get Excel 2007 to conditionally format to this seemingly simple requirement -

Please can someone advise...

[jeversf]

Can you post a sample of what you have tried so far?

[Bebbspoke]

Rationally the operation would be as follows: -

Click to highlght New column ("X"), open Conditional Formatting, select New Rule, scroll to and open "Use a formula to determine which cells to format"; open "Format values where this formula is true"; - select colA<colB<colC etc as desired... click "Format" select desired fill colour & click apply - fails.

In the C-F Rules Manager this creates (e.g.); -
Formula: =$A:$A<$B:$B<$C:$C (colour) which "Applies to" =$X:$X

If the same methodology is applied to individual cells i.e
Formula: =$A:$2<$B;$2<$C:$2

and the selection range is extended using the paint brush to give "Applies to" =$X2:$Xnn

the rule appears "locked" (in this case) to the values in Row 2

[Bebbspoke]

Trolling around hoping to find others with similar requirements implied that removing $ signs from the formula statement should achieve the desired functionality: -

So I tried Formula: =A2<B2<C2 which "Applies to" $X$2 - this also fails...

So I attempt to consider there may be some logic missing...

So I tried Formula: =A2<B2andB2<C2 - this does not work either

In trying to achieve my quest I have occassionally achieved colouration of my target cell - unfortunately this was not necessarily in agreement with the rule...
There is something very suspicious and deeply worrying about a system that is unable obey simple mathematic rules...

I'm only using numeric data (excluding headers) - I'm not trying to correlate the price of bananas against the USD/Japanese Yen via several historic spreadsheets written in different Excel versions discussing the relevance of the pre-raphaelite rebelion with reference to the existence of an "r" in the month in Swahili to calculate next weeks winning lottery numbers...

For pity's sake - I JUST WANT SOFTWARE THAT OBEYS SIMPLE MATHEMATICS PLEASE

[Bebbspoke]

Please can someone supply a link to the syntax rules for writing formulae involving the > and < functions?
I wish to stack several into a single formula statement... thank you

[Buster^]

I am not quite sure what you are after, but I think you coul do this (If you want people to help you faster, post a example workbook! ):

Please Login or Register  to view this content.

So if b1 is bigger than a1, c1 is bigger than b1 and d1 is bigger than c1, the result will be true, else false. If only one of them needs to bigger e.g. C1 is bigger than b1, but not the rest you can use the OR formula!

Hope this helps Buster^