The AND function takes each of the conditions inside the brackets and evaluates for their truth, and then multiplies the results together. TRUE = 1 and FALSE = 0. So if any condition is FALSE then the overall statement returns FALSE.

The formula for AND needs to be placed in brackets with each of the conditions separated by a comma. For example =AND(H1<1,G1>1)

In this instance the function will evaluate H1 to see if the value is less than 1. if it is then it will return TRUE or 1 and if not FALSE or 0. Then it will do the same for G1.

The two values are multiplied together and the answer is then either 1 or 0, TRUE or FALSE.

The Excel Help says:

One common use for the

ANDfunction is to expand the usefulness of other functions that perform logical tests. For example, theIFfunction performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using theANDfunction as theargument of thelogical_testIFfunction, you can test many different conditions instead of just one.

I had a reader request some help with Conditional Formatting in Excel 2007.

Hi

Baffled in trying to get to grips with conditional formating in 2007

Scenario :

Column A = Actual Result

Column B = Target

Column C = Needs to show

1) If Actual result is less than 90% of target turn font colour RED

2) If Actual result is >90% of target and is <100% of target turn font colour AMBER

3) If Actual result is >= 100% of target turn font colour GREENIf column C meets criteria 1 use character "R", criteria 2 = "A" and criteria 3 = "G"

I can acheive 1 and 3 with =IF(a1b1,"G")) and then conditional format so that if contents of cell = R or G then colour font accordingly </B1,"R",IF(A1>

I`m stumped with the % factor and also think there might be a better way of doing it

Any help would be appreciated :)

Here is a solution to this problem.

We formatted two columns to give two examples as to how it could be done.

The actual column determines the percentage as part of the conditional format and formats in that way.

The result column formats on the “R”, “A”, and “G”.

The formula in Column C1 is =IF(A2/B2<1,IF(A2/B2<0.9,"R","A"),"G") and this can be copied down.

Here are the rules for Column A

There has been so much Excel and Office related info come over my desk the last couple of weeks I don't have time to do it all justice, but I will try and share it with you in an organised way as possible.

Create conditional drop-down lists. In Excel 2007 (and earlier), it’s possible to create a drop-down list. By using the INDIRECT function, you can then create additional drop-down lists that are conditional to the first drop-down list.

From J-Walk, run some code that runs 1 million random numbers and see how fast your computer is compared with others.

I have had a bunch of pretty cool Excel things to post up – and finally got around to clearing my flagged items and browser windows.

I have written a couple of articles on Conditional formatting in Excel 2007 with lots of readers comments and requests for help. They are the two most read articles on this site.

I was very interested then to come across this article on Joseph’s site by Amit Velingkar where he shows you how to change the automatic colour ranges that are used in Excel 2007 for conditional formatting. He even includes some VBA code for this.

## Recent comments

6 years 5 weeks ago

6 years 5 weeks ago

6 years 6 weeks ago

6 years 6 weeks ago

6 years 6 weeks ago

6 years 6 weeks ago

6 years 6 weeks ago

6 years 6 weeks ago

6 years 6 weeks ago

6 years 6 weeks ago