Any Excel pro's wanna help me?

Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum

Help Support Homebrew Talk:

Strave19

Well-Known Member
Joined
May 12, 2010
Messages
140
Reaction score
1
Location
WA
I'm trying to convert a large dataset with numbers from 0-99 into classes. I.e. 1-5=1, 6-25=2, 26-50=3...etc... I cannot get an if/then statement to work...

what is wrong with this? =IF(G2<1, G2, IF(G2<=5, 1, IF(G2<=25, 2, IF(G2<=50,3, IF(G2<=75, 4, IF(G2<=95, 5, IF(G2<=100, 6, G2)))))))

all cells are returning zero... Checked to make sure data is entered as numbers, it is. Any tips?
 

stevo155

Well-Known Member
Joined
Jun 19, 2008
Messages
987
Reaction score
51
Location
Derry
Strange...I put it in just like you listed and got this with a this test data:

0 0
1 1
11 2
21 2
31 3
41 3
51 4
61 4
71 4
81 5
91 5
100 6
 

funkapottomous

Well-Known Member
Joined
Feb 1, 2010
Messages
628
Reaction score
11
Location
Houston
did you set up a table to refer to the dataset? That would be the easiest way. Just set up a table with your values and then do a V Lookup. It should work.

basically your reference table would have your numbers on the left (0, 1, 6, 26, etc) and the values on the right (G2, 1, 2, 3, 4, 5,)

so basically you'd have if G2 < 1, G2 and then if G2<6, (Cell number of of your 1 on the right of the table)

I'm just typing this out right now without looking at excel but I think that should fix your problem, unless you're having to type it all out and not use the formula windows.
 

TopherM21

Active Member
Joined
Jun 1, 2011
Messages
43
Reaction score
0
Location
Broomfield
Could set up a table with the value and the corresponding group and use the vlookup function
 

graybeard

Active Member
Joined
Jun 4, 2010
Messages
43
Reaction score
0
Location
Canton, OH
VLOOKUP versus a reference table is probably the cleanest way to go, allowing you to edit where the breaks are if you ever want to. Just as funkapottomous says.

Before I learned the VLOOKup command and nuances, I would have solved your type of problem with two steps:

First, set up a row of single-condition if statements way off to the right, out of your line of sight (for neat appearance).
Cell AA2 --> =IF(0.9<G2<5.1,1,0)
Cell AB2 --> =IF(5.9<G2<25.1,2,0)
...and so forth until you describe all your cases, say, through cell AE2.

Second step, in cell H2--> =SUM(AA2:AE2)

should return your correct value.

Peace.
 
OP
S

Strave19

Well-Known Member
Joined
May 12, 2010
Messages
140
Reaction score
1
Location
WA
Thanks guys! Problem solved... Its embarassing, but I was referring to the wrong cell the whole time. I overlooked it because I figured I must have screwed up the if/then (Which I'm new at). Thanks again guys.
 

DrDarwin

Well-Known Member
Joined
Dec 16, 2008
Messages
105
Reaction score
0
Location
Fort Worth
You're formula works for me, and should work for you. Are you sure that your worksheet is calculating? What happens when you try to use the data in other formulas?

Edit: late...
 

Homercidal

Licensed Sensual Massage Therapist.
Lifetime Supporter
Joined
Feb 10, 2008
Messages
33,310
Reaction score
5,716
Location
Reed City, MI
Thanks guys! Problem solved... Its embarassing, but I was referring to the wrong cell the whole time. I overlooked it because I figured I must have screwed up the if/then (Which I'm new at). Thanks again guys.
Ha ha! Classic!

I did the same thing with a VBScript I was working on recently. Couldn't get the value to change on my IF statement and after disassembling the code, I found I was referencing the wrong variable! The nice thing was after taking out the REM statements, everything worked.
 
Top