Solved

Excel data segment

Posted on 2013-01-11
8
444 Views
Last Modified: 2013-01-11
I've got a lot of data in Excel and would like to divide them into segments according to 2 columns' value:

Asset                              No of product        Segment
> 1000000                >10                        Premium 1
> 1000000              >=10 & <100      Premium 2
> 1000000               >100                        Premium 3
>50000 & <=1000000      >10      Smart 1
>50000 & <=1000000       >=10 & <100      Smart 2
>50000 & <=1000000      >100      Smart 3
<=50000                                    >10       Normal 1
<=50000                               >=10 & <100      Normal 2
<=50000                                  >100      Normal 3


Are there any smart ways to lookup the segments?
0
Comment
Question by:hkgal
  • 5
  • 3
8 Comments
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
Add a nested if statement with and AND operator and an OR operator that classifies them in a third column -
Copy your conditions on a separate location
You could get something like in C2
=if(AND(A2=$E$2;B2=$F$2);"Premium1";if(AND(A2=$E$3;B3=$F$2);"Premium2";....

(substitute ; to , depending on your regional settings

assuming that column A holds the Asset values and B the No of Product and columns E and F hold the conditions for the classification

Instead of getting a very complicated nested if, maybe you can group together conditions
i.e. I see that from your data Premium 1, Normal 1 and Smart 1 change due to the first condition (Asset).  Can I have an excel with sample data?
0
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
An easy way would be to color code these based on conditional formatting.
0
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
What classification is obtained (segment) when No of product is equal to 100????
0
 

Author Comment

by:hkgal
Comment Utility
I am using Excel 2003, but when I used nested If function, it reach condition limit after 7 If function set..
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 17

Assisted Solution

by:xtermie
xtermie earned 500 total points
Comment Utility
I broke down your conditions into two conditions
One that based on the Asset produces the Part A of the segment (premium, smart, normal) and one based on the No of product produces the Part B of the segment (1, 2, 3) and then concatenate the result in a final segment classification column.  Please see attached file with example.
Example2.xlsx
0
 

Author Comment

by:hkgal
Comment Utility
will formula like sumproduct, match helps?
0
 
LVL 17

Accepted Solution

by:
xtermie earned 500 total points
Comment Utility
sumproduct is a calculating function that multiplies corresponding components in the given arrays, and returns the sum of those products - dont think that it can be applied in your case.

Match is a lookup function that searches for a value in an array and returns the relative position of that item - dont think that it can be easily or very efficiently applied in your case.

Doesn't the provided formulas in the example file work for you?
You can hide the two columns if they are in the way :)
Sometimes it is better to break up the formula into two instead of creating very complex formulas and try to see the logic behind what you are trying to do.
Let me know if you have any other ideas so that I can try to help you out.
0
 

Author Closing Comment

by:hkgal
Comment Utility
Your comment helps!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now