Complex Functions in Excel

Recall that our primary aim is to find the types of people who are most likely to respond positively to the campaign. You can analyse various attributes like age group, marital status etc. one at a time or could automate the task to do the same thing in a fraction of seconds. In this segment, you will learn faster ways to extract the important pieces of information from the data.

In the previous video, we observed the use of:

• 0 & 1 instead of No & Yes. The use of binary values (especially 0 & 1)  makes the computation of average easier. 
• Remove Duplicates in the Data Tab. Remove Duplicates can be used to find only the unique values in a column.

Is there a formula that can automate the aggregation by age group and eliminate the use of filters? Let's explore

To summarize the most important things you've learnt:

1. You calculated the average of the response column based on the marital  column using the AVERAGEIF function; it computes the average of the response column if the marital status is married / single / divorced
2. You learnt how to freeze certain cells in a formula using a dollar sign; in the AVERAGEIF formula, you wanted the marital status to change but not the range of response values

Freezing cells in formulae is one of the most important skills you'll need to do in everyday analysis, so make sure to practise a bit more of that in the upcoming segments.