Create a formula to analyze data in excel.

Asked By Daan Gates 10 points N/A Posted on - 08/04/2012

Hi, If you are an expert in excel please help me on this.

I have an excel sheet including student detail in an institute. Those students are in different ages. I want to categorize student based on the age. If a student is under 15, it should be appeared in a specific column as “under 15”. If over 15 should be appeared as “over15”. Can you create a formula?

• Status: Open
• Question Views: 1261
• Answer Count: 3
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Geisha Nitta

Go To Solution

Best Answer

Best Answer

Answered By Geisha Nitta 0 points N/A #135908

Create a formula to analyze data in excel.

I think your requirement can be solved using a function in Microsoft excel. I will take “IF” function in this case. I will take an example in order to explain that.

Please see the image below.

Now I will assume that you want to display under / over 15 in the column category.

Lets see how to apply “IF” function.

First we will apply it to cell D3.

Go to the cell D3 and type the following formula.

=IF(C3>15,"over 15","Under 15")

Copy the same formula to below cells. Now you can see the result you want as shown in the below image.

About Geisha Nitta

Questions
1

Answers
55

Best Answers
27

• Vote Up 0 Vote Down

• Posted on - 08/04/2012
• Question Category: MS Excel

Answered By Loida Arcel 0 points N/A #135909

Create a formula to analyze data in excel.

I have a different method for this one. It is not based on Formula but based on sorting and filter. Saying if the age of the students is in a different column. Just highlight that column and on the ribbon click Sort and Filter and click the Filter option. Now , you have set the age column to be filtered. You will notice there will be an arrow in the very top column cell of that category. 

 

You just need to click on that Arrow button and you will see an option that says number filter. Then you can click greater than and fill in 15 for those over 15. There is a radio button which says AND & OR. Selecting AND would mean you include the filter with students with the age of 15. If You use OR. Then only students above 15 will be shown. You can do likewise for students under 15 be doing the same exact process and selecting Less than.

About Loida Arcel

Questions
4

Answers
41

Best Answers
8

• Vote Up 0 Vote Down

• Posted on - 08/04/2012
• Question Category: MS Excel

Answered By Anthew Staw 0 points N/A #135910

Create a formula to analyze data in excel.

I understand that you want two columns, 1st column for “under 15” and 2nd column is “15 up”, right? Here’s what you’ll do:

 

      Under 15             15 up

=IF(age<15, 1,"") =IF(age>=15,1,"")

 

The “age” on the formulas should be the cell which contains the age of the students. After placing the formulas drag the cell down so that it will be applied to the other cells. If the age of the student is below 15, a number will appear on the category “Under 15” otherwise it will appear as blank.

 

The “15 up” on the other hand pretty much does the same thing it’s just that ages 15 above are the only ones it processes otherwise it will appear as blank. Hope this helps.

 

About Anthew Staw

Questions
3

Answers
34

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 08/05/2012
• Question Category: MS Excel