Conditional Formatting Headache

**aborg88** · 06-21-2012, 06:20 AM

Hi guys

Im having trouble using conditional formatting, was hoping its an easy fix (sure it is for you guys)

Im processing withdrawels, so all my figures are in negatives. Essentially i have:

-two columns, yesterdays payout, previous weeks pay out, so e.g. if yest was sunday, column B will have yesterday (sunday), columns C will have data for the previous sunday
- row go down per country ; looks like this

Column A

UK
Sweden
Germany

Column B

-£2,580
-£29,990
-£959

(notice that they are all in negatives)

Column C

-£400
-£31,000
-£2154

I would like two bits of conditioning. for each row; If column B is less then column C (due to the negatives), i want it to be coloured colour A. secondly, if column b is less then 35% increase of column C then colour it colour B.

I would also like this to be used across all rows; e.g. countries (my list is very long, so i dont want to do it manually)

is this possible?

**aborg88** · 06-21-2012, 06:21 AM

i can attatch an excel sheet if this makes it easier.

**aborg88** · 06-21-2012, 06:24 AM

conditional_format.xlsx

attatchment

**npamcpp** · 06-21-2012, 06:39 AM

I often work out conditional formatting formulas by putting the formulas on the worksheet. So, to figure out if column B is less than column C, the formula would be

=B2<C2

The result is either TRUE or FALSE. If you plug that into a conditional format, then the TRUE will trigger the formatting. I'm not quite sure what you mean with

If column B is less then column C (due to the negatives), i want it to be coloured colour A.

There really is no room for interpretation. -1000 is smaller than -500. BECAUSE of the negatives.

The second formula would calculate if B is less than C + 35%, is that right? If so, something like this will work:

=B2<C2*1.35

Again, this will return TRUE or FALSE and can be used in a conditional format.

Now, in your data sample, the results of both formulas are the same for the first and the second condition. What formatting to you want to apply if BOTH conditions are true? Do you want to keep the formatting for the first condition and ignore the second condition? Or should the second condition override the first condition?

Please post a data sample with a few more numbers that show all the possible cases and manually color the cells as you would want to see them after the conditional format. Then we know what to aim for.

**aborg88** · 06-21-2012, 07:07 AM

Withdrawel_Report.xlsx

Hi and thanks for your swift reply.
Attatched is the whole excel sheet i am working with. Pls ignore my previous comment, as you 're right it doesnt really make sense.

Your solution, despite very logical, will affect the reability of my sheet, as this would have to be repeated over multiple columns,

What do you thinkl?

**aborg88** · 06-21-2012, 07:08 AM

also, i forgot to say: i will keep the condition of the second condition if both are true, as for the second one to be true, the first one would have to be as well.