Any Excel pro's wanna help me?

[Strave19]

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]

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]

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]

Could set up a table with the value and the corresponding group and use the vlookup function

 

[graybeard]

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.

 

[Strave19]

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]

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]

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.