Turn вЂњabsoluteвЂќ numbers into вЂњpercentagesвЂќ.Copy and вЂњpaste as valuesвЂќ so the data is no longer linked to the pivot table.Highlight the data from the pivot table.Make a bar chart of the final age categories Also drag вЂњAge CategoriesвЂќ into the Pivot Table вЂњValuesвЂќ so that it shows вЂњCount of Age CategoriesвЂќД§.Drag вЂњAge CategoriesвЂќ into the Pivot Table вЂњRowsвЂќ.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.

Excel if statement