Comments on: Excel Question
http://ask.metafilter.com/295095/Excel-Question/
Comments on Ask MetaFilter post Excel QuestionThu, 28 Apr 2016 06:34:34 -0800Thu, 28 Apr 2016 06:39:53 -0800en-ushttp://blogs.law.harvard.edu/tech/rss60Question: Excel Question
http://ask.metafilter.com/295095/Excel-Question
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." <br /><br /> That's basically it. I don't want to do this by hand because there are a lot of data. <br>
<br>
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.<br>
<br>
Is that possible? <br>
<br>
Thank you beautiful nerds!post:ask.metafilter.com,2016:site.295095Thu, 28 Apr 2016 06:34:34 -0800LutoslawskiexceldataBy: cogitron
http://ask.metafilter.com/295095/Excel-Question#4273758
Set cell H1 (or whichever row you want to start at) to <br>
<br>
=IF(AND(G1>30, G1 * 50), "moderate", "")<br>
<br>
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<br>
<br>
=IF(AND(G1>30, G1 * 50), "moderate", IF(G1>=50, "high", ""))<br>
<br>
and so on.<br>
<br>
Replace * with a less-than symbol. (Sorry for lazy editing to deal with HTML formatting!)comment:ask.metafilter.com,2016:site.295095-4273758Thu, 28 Apr 2016 06:39:53 -0800cogitronBy: EndsOfInvention
http://ask.metafilter.com/295095/Excel-Question#4273800
Here's the copy&paste version of cogitron's formula:<br>
<br>
=IF(AND(G1>30, G1<50), "moderate", "")<br>
<br>
If you want to label things that are below 30, between 30-50, and above 50, you could use:<br>
<br>
=IF(G1>50,"high",IF(G1<30,"low","moderate"))<br>
<br>
That formula includes the values 30 and 50 in the "moderate" range. <br>
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:<br>
<br>
=IF(G1>=50,"high",IF(G1<=30,"low","moderate"))comment:ask.metafilter.com,2016:site.295095-4273800Thu, 28 Apr 2016 07:23:09 -0800EndsOfInventionBy: aimedwander
http://ask.metafilter.com/295095/Excel-Question#4274015
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.comment:ask.metafilter.com,2016:site.295095-4274015Thu, 28 Apr 2016 10:53:41 -0800aimedwanderBy: pompomtom
http://ask.metafilter.com/295095/Excel-Question#4274914
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.<br>
<br>
So if your table is:<br>
<code> A | B<br>
4 | small<br>
8 | medium<br>
12 | big<br>
</code><br>
<br>
then a lookup (for G1) of <code><br>
=vlookup(G1,A:B,2,TRUE)<br>
</code><br>
would give "small" for <4, medium for 4<G1<=8 etc<br>
<br>
(nb the table needs to be sorted)comment:ask.metafilter.com,2016:site.295095-4274914Fri, 29 Apr 2016 16:35:48 -0800pompomtom