How to use the AND Function in Excel

Here's the scenario. The government have decided to take a closer look at school averages. If 4 or more students in a subject get a score of 70 or above, then the school gets a cash bonus for that subject. There are 8 subjects, so 8 cash bonuses are up for grabs.

First, we'll use the AND function to work out if 4 or more students have scores of 70 or above.

The AND( ) function checks a list of arguments and determines whether they are true or false. If all the arguments are true then the function gives you an answer of TRUE. If one or more of the arguments are false, then the function gives you an answer of FALSE.

To give you an easy example. You can check whether two plus two does indeed equal 4. Click inside an empty cell of your spreadsheet, then click inside the formula bar. Enter this:

=AND(2 + 2 = 4)

When you press the Return key, Excel gives you the answer TRUE. Now change it to 2 + 3 = 4 and see what happens.

So Excel checked the argument to see whether it was true or false. That's all it will check for, an either or answer. You can have up to 30 arguments between the AND brackets. You could have this, for example:

= AND(2 + 2 = 4, 1 + 2 = 4)

There's two arguments to check there. Excel will check the first one and return an answer of TRUE. When it checks the second one it will return an answer of False. The answer to the whole function will then be FALSE. (It's false because all conditions have to be true before the overall answer is TRUE.)

Our AND function is going to be quite simple. We're going to check the B column, the Number of students who have Below Average scores.

• 
  
  So click inside C15


• 
  
  Click inside the formula bar


• 
  
  Enter the following formula :

= AND(B15 >= 4)

• 
  
  Press the return key on your keyboard


• 
  
  Excel will enter True in this cell (B15 is the cell where we had a score of 4)


• 
  
  Use Auto Fill to calculate the rest of the AND functions


• 
  
  Your spreadsheet should look something like the one below:

All we're saying in our function is "IF the cell B15 is greater than or equal to 4 THEN put True in cell C15, else put False". Remember: the government pays out if 4 or more students get above a score of 70 for a lesson

Now that we have some True/False values for our C column, we can check all these True/False values. We want to add up all the cash values associated with our TRUE cells. However, we haven't got any cash values yet, so let's do that now. Enter the same cash values as in the image below:

The values are just potential values. A school only gets them if 4 or more students gain a score of 70 or above in that subject. So there's a potential ten thousand for Maths, five thousand for English, 8 thousand for Science, etc.

OK, we have 2 cells with TRUE in them. There is a different cash value associated with each subject. We have a TRUE for English, so the school will receive 5 thousand pounds for this. The school can expect 10, 000 for Maths. But we need a way to add all the cash values associated with the TRUE values. We can use SUMIF for this.