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?
mully
Registered User regular
Registered User regular
Hiya.
So, here's what I have for my formula:
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.
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
#canadian #sorry
Posts
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:
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:
I think that should do it.
CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
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".
CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
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 falseIf 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 falseAnd 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 falseYou 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.