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?

- Thread starter Strave19
- Start date

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?

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.

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

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.

- Thread Starter
- #6

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.

Edit: late...

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.