Excel Question
April 28, 2016 6:34 AM   Subscribe

Is it possible in excel to create a formula that says "if a value in this column is in this range of numbers, put label x in a different column."

That's basically it. I don't want to do this by hand because there are a lot of data.

I want it to do a thing where it's does something like: if G6 is greater than 30 but less than 50, put the word "moderate" in H6.

Is that possible?

Thank you beautiful nerds!
posted by Lutoslawski to Technology (4 answers total) 11 users marked this as a favorite
 
Best answer: Set cell H1 (or whichever row you want to start at) to

=IF(AND(G1>30, G1 * 50), "moderate", "")

and fill down column H. The second argument to IF, "moderate", is what should go in that cell if G1 is between 30 and 50. The third argument is what should go there otherwise. In this case, I'm guessing it should just be empty (""), but if you wanted to also mark high values, for instance, you could instead do

=IF(AND(G1>30, G1 * 50), "moderate", IF(G1>=50, "high", ""))

and so on.

Replace * with a less-than symbol. (Sorry for lazy editing to deal with HTML formatting!)
posted by cogitron at 6:39 AM on April 28, 2016 [7 favorites]


Best answer: Here's the copy&paste version of cogitron's formula:

=IF(AND(G1>30, G1<50), "moderate", "")

If you want to label things that are below 30, between 30-50, and above 50, you could use:

=IF(G1>50,"high",IF(G1<30,"low","moderate"))

That formula includes the values 30 and 50 in the "moderate" range.
If you want to include 30 in the "low" range and 50 in the "high" range (so e.g. 30=low, 30.01=moderate, 49.9=moderate, 50=high) you'll need to use less-than-or-equal-to and greater-than-or-equal-to instead of just less-than and greater-than:

=IF(G1>=50,"high",IF(G1<=30,"low","moderate"))
posted by EndsOfInvention at 7:23 AM on April 28, 2016 [2 favorites]


Best answer: This is not a direct answer to your question, but you may find it interesting to play with the "conditional formatting" button (on main "home" menu bar of Excel 2010). Its default is to color-code cells in a continuous range (eg. 30 is red, 50 is yellow and 80 is green, but also 40 is orange and 70 is lime) but the usage you describe is more like "icon sets" (in the pull-down menu). Use "create new rule" to set precise range limits.
posted by aimedwander at 10:53 AM on April 28, 2016 [4 favorites]


Also, if you have a large range of numbers, you can use VLOOKUP with the range-lookup parameter set to true. Point it to a reference table and have as many bands as you like.

So if your table is:
A | B
4 | small
8 | medium
12 | big


then a lookup (for G1) of
=vlookup(G1,A:B,2,TRUE)

would give "small" for <4, medium for 4<G1<=8 etc

(nb the table needs to be sorted)
posted by pompomtom at 4:35 PM on April 29, 2016


« Older Self-Improvement Self-Assessment   |   Psilocybin Mushrooms clinically? where do i find... Newer »
This thread is closed to new comments.