Press “OK” to insert the pivot table into a new spreadsheet.Then, write “if” again, and another open bracket, and check if the age is less than 40 years by writing E2 Pivot Table.If you hit “Alt+Enter” on your keyboard – this puts the next part of the formula on the next line and makes it easier to keep track of all the if statements in your formula.After the second set of double quotation marks, add a comma. Take the formula you’ve already written.So, you’re going to add a second IF function to the formula to check if it’s in the next age category. Right now, we’re only checking one of our age categories (we’re checking to see if the data in cell E2 is less than 18). And hit enter = if your age data is greater than or equal to 18, the cell will now saw “FALSE”.Last, put another bracket to end the function:.

Then, put double quotations and the text that you want to apply and double quotations again.Then, put in a “less than” sign, put in the age 18 and a comma to mark the end of the logical test.

Next, click on the cell where your age data is held (in my example, my age data is in cell E2).

First, write an “equals” sign, then write “if” and an open bracket.

Make this function check: “if the age is less than 18 years old, then apply age category ‘<18 years'”. Start out by putting a single “If” function into the top cell in this column. Write your first If Functionįirst, add a new column to the spreadsheet and call it “Age Categories”. Step-by-Step: Excel Nested If Function 1. If you apply the Excel nested if function to the age data using these categories, you’ll end up with this chart instead (much nicer to put into a report): For example, let’s use these age categories: Usually, you want to group the heads of households in certain age categories based on your organisation’s indicators, or based on your donor indicators. If we were to show this data in a bar chart, here’s what it would look like (not super useful to anyone) – just a lot of bars: In this example survey data, we have collected age data (for example, we’ve asked “How old is the head of household?”) Here’s a small snapshot of the age data: Syntax is Boring – Let’s Look at a Real Example – Age Categories

In this case we will put a new IF function in the place of the parameter. To create an Excel Nested IF function, if the logical_test is FALSE, we want to apply a second logical_test. These two values can be in any format: cell, text, number, date, time, or even another function, etc. Value_if_false is the value that will be returned if the logical_test is FALSE. Value_if_true is the value that will be returned as a result of the formula if the logical_test is TRUE. The logical_test is most often some comparison between two values.

Apply income ranges to individual household results when you’ve only collected income data.

Use a formula (like a vulnerability calculation) only for certain survey respondents.

Create age categories for individuals when you’ve only collected age data.

Here are three examples of when you might want to use an Excel Nested If function: When would you use an Excel Nested If Function? And you can use the Excel Nested If function to apply categories to population data. The IF function in Excel is a logical expression which consists of a “logical test” and a true/false result. And most likely, you use Excel (sometimes or all the time) to analyze all that data. You’ve probably got a lot of population data that you’ve collected through assessments and surveys.

How to do an if then formula in excel