Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

# Excel Question - Am I Even Close?

Registered User regular
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.

mully on

## Posts

• Registered User
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.

• Registered User, ClubPA
Akira has the right answer for what you've described

IT'S GOT ME REACHING IN MY POCKET IT'S GOT ME FORKING OVER CASH
CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
• Registered User regular
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".

• Registered User, ClubPA
Is this in conjunction with the rest of the formula above or something separate?

IT'S GOT ME REACHING IN MY POCKET IT'S GOT ME FORKING OVER CASH
CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
• Registered User
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.