Averaging using the IF function and Array formula

To create an average of a particular group of cells you can use the Average function in conjuction with the IF function. This can be very handy for working out the averages of a group of options within a larger group.

Following the example below we want to see what the average lifespan of a set of tires are compared to the laps that they complete.

We have our basic information as shown above, however to find out and keep the information of how long on average the tires last we need to use a nested function using the Average and the IF function

Next we have to insert the functions to get what we need

Under the Slick tire type in the following formula

=AVERAGE(IF(A2:A14="Slick",B2:B14,""))

This formula will average all the slick tires. To complete this formula we need to make it an Array formula, to do this once you have typed in the last bracket " ) " press the Ctrl key down and the Shift key and press enter, this will make the formula an array and add extra brackets }.

It should look like this.

That's it, complete the rest of the formula's by changing the Slick to Medium and then Long and don't forget to make then Array formulas and you now have the average life of your tires.