Excel Question - Am I Even Close?

mully

Hiya.

So, here's what I have for my formula:

=IF(NTotal<10,2,IF(10<NTotal<300,3,IF(301<NTotal<3000,4,IF(NTotal>3000,5,IF(H53="Industrial Manufacturing",6,0)))))

To explain what I am trying to do, I have a number that is returned in "NTotal" which is a formula from a whole bunch of other wacky concoctions. That number determines the VLOOKUP for another column, whose column names look like this:

N < 10 | 10 < N < 300 | 301 < N < 3000 | N > 3000 | Industrial Manufacturing

So, N will return a number and it will obviously fit into one of those numerical categories. If it doesn't, then it's column 6 - Industrial Manufacturing - which is a "primary use" that is checked off in another part of the form, so it needs to check "H53" to see if it's populated with Industrial Manufacturing.

Any help you can offer, and a swift slap upside the head if I'm doing something really stupid, would be appreciated.

I tend to look at easy problems really difficultly, so ... yeah.

THANKS.

-AKIRA-

mully wrote: »

=IF(NTotal<10,2,IF(10<NTotal<300,3,IF(301<NTotal<3000,4,IF(NTotal>3000,5,IF(H53="Industrial Manufacturing",6,0)))))

THANKS.

The First thing I would do to simplifiy what you've got it take out the range in your if terms. For instance, in 10<NTotal<300 you don't need the 10< part, as you've already checked that in the first if statement. So you'd end up with something like this:

=IF(NTotal<10,2,IF(NTotal<300,3,IF(NTotal<3000,4,IF(NTotal>3000,5,IF(H53="Industrial Manufacturing",6,0)))))

Now, you've got the problem that your last statement will never return false. If NTotal is a number, it will always fall into one of these categories as the first one is an upper bound, and the last one is a lower bound. You're going to want to check the "Industrial Manufacturing" part first I'd imagine, so you'd get something like this:

=IF(H53="Industrial Manufacturing",6,IF(NTotal<10,2,IF(NTotal<300,3,IF(NTotal<3000,4,IF(NTotal>3000,5,0)))))

I think that should do it.

Pheezer

Akira has the right answer for what you've described

mully

Thank you Akira! Sorry I couldn't get back to you faster. This worked great and I am forever grateful - and thank you for explaining it to me.

Is there a way to make a formula to select certain numbers? Like, for example, if it's from 11-20, put 4. if it's 7, 8 or 10, put 5. Any way to set ranges with numbers outside of that range? One I'd have to do is "1, 5 or 9".

Pheezer

Is this in conjunction with the rest of the formula above or something separate?

-AKIRA-

mully wrote: »

Thank you Akira! Sorry I couldn't get back to you faster. This worked great and I am forever grateful - and thank you for explaining it to me.

Is there a way to make a formula to select certain numbers? Like, for example, if it's from 11-20, put 4. if it's 7, 8 or 10, put 5. Any way to set ranges with numbers outside of that range? One I'd have to do is "1, 5 or 9".

I don't exactly know how excel handles formulas, but it's certainly possible.

If all of your numbers are going to be integers (you're not expecting 2.381 for example), you can do a simple if comparison along the lines of

If ('A7' == 1 OR 'A7' == 5 OR 'A7' == 9):
     what you want for true
Else:
     what you want for false

If you're concerned about a particular range, you can of course do something along the lines you had earlier

If ('A7' >= 11 AND 'A7' <= 20):
     what you want for true
Else:
     what you want for false

And of course you could put them together, to check for multiple ranges like this

If (('A7' >= 11 AND 'A7' <= 20) OR ('A7' >= 50 AND 'A7' <= 100)):
     what you want for true
Else:
     what you want for false

You can also put your second range (in the above example 50 --> 100, inclusive) in the false evaluation as a true to another If and just nest the If statements as much as you want, but I'm not too fond of that approach.

Watch out for situations like before where you have a desired behavior somewhere that doesn't ever get to evaluate.