# Excel "if" Conditional Formula Help



## hifromdp (Sep 25, 2007)

i need to create a CONDITIONAL formula

Cell A1 is 1 (ONLY CELL THAT DATA WILL BE PUT INTO)
Cell B1 is 3.22
Cell C1 is =IF(OR(A1=0, A1<3), B1, "Not OK")

in CELL A1, if a 1 or 2 is entered, you will get what is in cell B1.
if you enter any other number, you will get "Not Ok"

NOW the problem is...
IF THE NUMBER "0" IS ENTERED, I SHOULD GET "NOT OK"
however, when "0" is entered, i am not getting "not okay"

what is wrong with the formula where?
i need only the numbers 1 or 2, to get cell B1. if "0" is entered or any number above 2 is entered, i should get "not ok" in cell C1.
Thanks


----------



## Rollin_Again (Sep 4, 2003)

Will only whole numbers (1 or 2) be entered in cell A1?

If so, you can use the following

*=IF(OR(A1=1,A1=2),B1,"NOT OK")*

If you want all values between 1 and 2 (inclusive) you can try the following
*=IF(AND(A1>=1,A1<=2),B1,"NOT OK")*

Regards,
Rollin


----------



## hifromdp (Sep 25, 2007)

that was it, perfect, thank you.


----------



## Rollin_Again (Sep 4, 2003)

Glad to help. Feel free to mark this post as solved using the thread tools at the top.

Regards,
Rollin


----------



## hifromdp (Sep 25, 2007)

Rollin..
One more question.....

i need to create multiple arguments and i am getting errors
for example, what i need is

if in cell C9=1,c9=2, i get 2.33 in cell C10
if in cell c9 is >2 and <11, i get 4.33 in cell C10
if in cell c9 is >10 and <36, i get 6.16 in cell C10
if in cell c9 is >35 and <71, i get 7.39 in cell C10
and so on....

each one of these is an argument, correct?
i need to take this to 12 arguments. How do i do this?
this is what i have now. i can get the first two arguments and if true, shows correctly. however, when the 3rd argument is true (C9 is between 11 and 35), i am getting the 2nd argument's true answer (4.33). same thing for the 4th argument. if i enter 50 i am getting 4.33 BUT really should be getting 7.39.

=IF(OR(C8=1,C8=2),"3.20",IF(OR(C8>2,C8<11),"4.33",IF(OR(C8>10,C8<36),"6.16",IF(OR(C8>35,C8<71),"7.39"))))

Thanks


----------



## The Villan (Feb 20, 2006)

Thats becuase you are using OR.
You should be using AND

=IF(OR(C8=1,C8=2),3.2,IF(AND(C8>2,C8<11),4.33,IF(AND(C8>10,C8<36),6.16,IF(AND(C8>35,C8<71),7.39))))


----------



## hifromdp (Sep 25, 2007)

Thank you, this worked great.
However, now when i try to total the sum of the rates in this column, it comes up 0.
from what i am reading, it says that you can not sum logical values.
is there a formula where it will pick up the whatever value is put there?


----------



## Zack Barresse (Jul 25, 2004)

You cannot sum logical values, assuming they are actually boolean (True/False) values and you are using a traditional SUM() function. You can, however, either use an array entered (w/ Ctrl + Shift + Enter, called a CSE function) or with other functions. Example...

=SUM(--A1:A5)

.. enter as CSE formula (Excel will put the { } curly brackets around the function to denote an array-entered formula.

This will use a double urnary minus to coerce a boolean value in the range to a 1 (=TRUE) or 0 (=FALSE) value. You can keep numerics in the range as well. The problem with using an array function like this is you can no longer use text in the range else an error will approach. You would need to add more to the formula. The other thing you could do is use a non-array entered function..

=SUMPRODUCT(--A1:A5)

This is the same thing as the first formula, just non-array entered.

HTH


----------



## The Villan (Feb 20, 2006)

Are you entering the valus as text by keeping the " " around the value

i.e. "3.2" =IF(OR(C8=1,C8=2),"3.20",IF(and(C8>2,C8<11),"4.33",IF(and(C8>10,C8<36),"6.16" ,IF(and(C8>35,C8<71),"7.39"))))

or have you changed your formula to 3.2

i.e =IF(OR(C8=1,C8=2),3.2,IF(AND(C8>2,C8<11),4.33,IF(AND(C8>10,C8<36),6.16,IF(A ND(C8>35,C8<71),7.39))))

I have just done a test with the last one and it sums fine using =sum


----------



## hifromdp (Sep 25, 2007)

yes, i removed the quotes and got it to work. With that, i was able to total that column as well as format the cell to put $$ there.

Now a new problem.
=IF(OR(C12=1,C12=2),3.2,IF(AND(C12>2,C12<11),4.33,IF(AND(C12>10,C12<36),6.16,IF(AND(C12>36,C12<71),7.39,IF(AND(C12>70,C12<151),8.13,IF(AND(C12>150,C12<201),8.77,IF(AND(C12>200,C12<1000),0.0438*C12,IF(and(C12>999,C12<2000),0.0419*C12))))))))
i need to add another logical value and instead of putting down a number, i need it to multiply and put answer in. IF(AND(C12>200,C12<1000),0.0438*C12.
This worked the first time. all was good.
then, when i went to add the 2nd one, IF(and(C12>999,C12<2000),0.0419*C12
i am getting an error where it says "and"

i am wondering if since i now have included the multipying in there twice, i am to use "and" to separate them ,or shoudl it be something else?


----------



## Zack Barresse (Jul 25, 2004)

Instead of nesting IF functions like that, which is very unproductive, setup a table of data and use LOOKUP. Only use an IF in case you want to specify a lower/upper end value in your table. Check out the help files. Post back if you need more help.


----------



## The Villan (Feb 20, 2006)

Your fomula should look more like this

=IF(OR(C12=1,C12=2),3.2,IF(AND(C12>2,C12<=10),4.33,IF(AND(C12>10,C12<=36),6.16,IF(AND(C12>36,C12<=70),7.39,IF(AND(C12>70,C12<=150),8.13,IF(AND(C12>150,C12<=200),8.77,IF(AND(C12>200,C12<=1000),0.0438*C12,0.0419*C12)))))))

The last IF needs to be 0.0438*C12,0.0419*C12 It doesn't need and IF statement as it should be the last possible result if all other conditions have not been met.

When you are trying to do your checks, you need to be careful how you do them.

You can't do >2 and <10 if you mean to capture all values up to and including 10

Likewise you have to be careful what values you use

IF(AND(C12>36,C12<71),7.39,IF(AND(C12>70,C12<151),8.13,

The above expression wont work

It should be something like this
IF(AND(C12>36,C12<=70),7.39,IF(AND(C12>70,C12<=150),8.13,

I hope you understand what I mean


----------



## aggs03 (Feb 19, 2008)

I am having issues with this formula. I am trying to have a specific percentage for which ever the cell H15 falls into. Any help would be aprrciated.

=IF(H15>=10000000,0.7%,IF(OR(H15>=900000,H15<=9999999),0.575%,IF(OR(H15>=700000,H15<=899999),0.46%,IF(OR(H15>=500000,H15<=699999),0.345%,IF(OR(H15>=400000,H15<=499999),0.23%,IF(OR(H15>=300000,H15<=399999),0.115%))))))


----------



## The Villan (Feb 20, 2006)

A quick look at your above example , I think would change to this.

=if(h15>=10000000,0.7%,if(and(h15>=900000,h15<10000000),0.575%,if(and(h15>=700000,h15<900000),0.46%,if(and(h15>=500000,h15<700000),0.345%,if(and(h15>=400000,h15<500000),0.23%,if(and(h15>=300000,h15<400000),0.115%))))))


----------



## Zack Barresse (Jul 25, 2004)

You're still better off using a lookup-type formula. Use the lower-ends of your values for the lookup side. You can then enclose your LOOKUP function in an IF statement for the upper bound value only. One IF statement - much easier to manage.

I.e.
300000,0.115%
400000,0.23%
500000,0.345%
700000,0.46%
900000,0.575%
10000000,0.7%

Assuming this was on another sheet (we'll say Sheet2) in range A2:B7 (row 1 for headers) you could use this...
=IF(H15<300000,"VALUE TOO LOW",LOOKUP(H15,Sheet2!A2:A7,Sheet2!B2:B7))

If you wanted to put an upper bound cap on it, you could adjust it as follows...

=IF(H15<300000,"VALUE TOO LOW",IF(H15>=20000000,"VALUE TOO HIGH",LOOKUP(H15,Sheet2!A2:A7,Sheet2!B2:B7)))

Note the cap I just guessed as 20,000,000 and I put it as greater than or equal to that amount. This is good because you can expand this as far as you want and you are not limited to the 7 (up to version 2003) nested IF functions. Plus just reading that formula gives me a headache, and it will probably be difficult to manage/maintain/troubleshoot. A table gives you a graphical representation of your data.

HTH


----------



## The Villan (Feb 20, 2006)

I agree with you entirely Firefytr, however the issue originally is that Aggs03, needs to understand the difference between OR and AND and >= > <= and < as well.

Lookup tables are always the first way to go when nested IF statements are used. I also don't like to see values in formulas, as these can change over time and its not good to have to start changing formulas because the variables have changed. Thats a bonus with the Lookup table as the variables are separated from the formulas. I Still think your variables Firefytr in your Lookup formula should be referring to cells with those values in. Then it becomes an easy job to change them without having to change the formulas.

But apart from that, smack on Firefytr :up:


----------



## Zack Barresse (Jul 25, 2004)

Thanks!


----------



## slurpee55 (Oct 20, 2004)

firefytr, for a lookup, wouldn't you need all the possible values between 300000 and 10000000 in a column? I don't see how that is possible...or can you set it to look for any number in a range?


----------



## The Villan (Feb 20, 2006)

slurpee - no you wouldn't, but as Firefytr introduced the Lookup, I won't steal his glory. I will leave him to explain


----------



## slurpee55 (Oct 20, 2004)

Would that entail using MATCH as well as LOOKUP?


----------



## Zack Barresse (Jul 25, 2004)

LOL! Thanks Villan. The LOOKUP function works a lot like the MATCH function and the VLOOKUP function, but is (IMHO) much more versatile. The important thing to note (as seen in the Help files) is if the Lookup can not find an exact match, it chooses the _largest _value in the array that is *less than or equal to* the value. This is why you would use the lower-bound value for your data in column A (as in the data I posted). So setting the search array (column A data, aka _lookup_vector_), it will look at the _lookup_value_ and find the value that is *less than or equal to it*. Given the return array (column B data, aka _result_vector_) it will bring back the matching vector. This means the two vectors MUST be the same size as each [lookup] unit matches a [lookup] result.

You could get the same results with a VLOOKUP (which would be my next route) or with an INDEX/MATCH-type combination. I like LOOKUP because, especially in this situation, you're going to be using its inherent qualities (i.e. *the less than or equal to* condition) and only must specify the lower/upper bound entries.

Hmm, does that explain it? We could get very, very technical if we wanted to, but I think we just start losing people at that point. We can use LOOKUP for vertical lookups, horizontal lookups, array matches, textual matches (reads alphabetical entries sorted), etc. This is why it makes it the most versatile of the LOOKUP functions.

HTH


----------



## The Villan (Feb 20, 2006)

he he - thats why I left it to you Firefytr.


----------



## slurpee55 (Oct 20, 2004)

I was thinking you might use it with MATCH because MATCH can be set to look for the the largest value that is less than or equal to value being searched for (value 1), the exact value (value 0) or the smallest value that is greater than or equal to value....
Oh, and Villan, it's fun to get Zack all worked up and rambling on....


----------



## Zack Barresse (Jul 25, 2004)

Yeah, you could most definitely use MATCH in the way specified. I would change it to something like this using that formula...

```
=IF(H15>=20000000,"VALUE TOO HIGH",IF(ISNUMBER(MATCH(H15,Sheet2!A2:A7,1)),INDEX(Sheet2!A2:B7,MATCH(H15,Sheet2!A2:A7,1),2),"VALUE TOO LOW"))
```
A little bit much, but it works. This also takes into account a high/low end range as well. That would be (most likely) fairly good on recalc time too, even though it is 4:1 (number of formulas). I'd be interested to see the recalc times on someone with FastExcel though. I do not have it myself, so I'm stuck with crude tests. My thoughts are, with the MATCH function, being so fast and set up on a match type of 1, it will probably end up being slightly faster. The second function will get bogged down with the ISNUMBER (a speculation on my part).

So for speed, I would probably use the INDEX/MATCH combo, but for simplicity, which almost always rules out in my opinion, I would use the LOOKUP. A matter of taste I guess.


----------



## slurpee55 (Oct 20, 2004)

urg...The Villan, you want to explain Zack, the walking VBA command?


----------



## Zack Barresse (Jul 25, 2004)

Rambling on, walking VBA command.... you guys is losin me...

I'm tired.


----------



## slurpee55 (Oct 20, 2004)

You, admit that???? 
And you've forgotten 
Zack![Option1].SetFocus and the like??? LOL


----------



## Zack Barresse (Jul 25, 2004)

Oh no, I'm still on petition for that.


```
Zack.SetFocus
```


```
Zack.Font.Bold = True
```


```
Zack.Repaint
```


```
Zack.Select
```


```
Zack.ShouldHaveHisOwnVBAObject = True
```


----------



## The Villan (Feb 20, 2006)

Slurpee m8, youve lost me. I have a memory like a seive and besides that I am posting at the moment, having just got rid of my visitors and about a dozen cans of beer gone and half that in wine bottles.

Hic Hic where is that damn toilet. Too late LOL Catch you guys in the morning and hopefully my head won't be too sore.

TTFN
Les


----------



## slurpee55 (Oct 20, 2004)

Hmmm, Zack.Repaint - sounds like something the little woman probably has said....
<-- Zack.ShouldHaveHisOwnVBAObject = True --> that's marvelous!

Villan...well, good morning!!!!! Feeling all cheery today?


----------



## The Villan (Feb 20, 2006)

ooooooh good morning. Where did last night go to 
Alls well that ends well.


----------



## slurpee55 (Oct 20, 2004)

LOL
just have to debug your memories....


----------

