Another Formula with Multiple Conditions

[gidget424]

I need help with a formula that has several conditions. I can't seem to get it to work. The conditions are:

IF(UPPER(B41)="x"
IF(S8:S13="x",0,"")
IF(C1<=3,C1*C41,"")
IF(C1>=4,45,"")

I keep getting a message that I have too many arguments.

HELP!!!

 

[colbymack]

Can you indicate which ones you want to happen when.

Your code:
IF(UPPER(B41)="x"
IF(S8:S13="x",0,"")
IF(C1<=3,C1*C41,"")
IF(C1>=4,45,"")

I assume you want it such that if UpperB41="x", then execute the next line. However, your next line indicates that it should end with either a 0 or a blank. This is where you need to indicate that you want the next piece of code - is it supposed to evaluate C1 if S8:S13 <>"x"? If so, then the next line should be put in place of the " ".

Something like this:
IF(UPPER(B41)="x",IF(S8:S13="x",0, IF(C1<=3,C1*C41,"")), (this should be the other half of your If when upper B41<> "x" ))

It is not possible to tell you how the code is to be written unless you list the conditions for each.

HTH,
Colbymack

 

[gidget424]

The IF(UPPER condition is a mandatory condition for all of the conditions below it. That is the one constant that applies to all.

 

[Norie]

gidget424

What you have posted is rather confusing.

Are you trying to use these conditions in 1 formula?

Why are they on different lines?

By the way the first condition UPPER(B4)="x" will always evaluate to false.

 

[gidget424]

ok.....I'l try to say what I need a little clearer. The conditions for one cell are:

IF(UPPER(B41)="x" (Applies to all conditions below)
And
IF(S8:S13="x",0,"")
Or
IF(C1<=3,C1*C41,"")
Or
IF(C1>=4,45,"")

When I have the formula in my worksheet, I have it all on one line.

 

[colbymack]

Are you indicating that you want all conditions evaluated in one cell, or are these 3 different equations, all beginning with the IF(UPPER(B41)="x" ?

Norie
Unless I am doing something wrong, when I evaluate If(upper(b41)="x", it evaluates to true whether I have a small X or a capital X in B41. I thought the same thing you stated (that it would always evaluate to false), but then I tried it. I am not sure what gidget is trying to accomplish with this portion . . .Gidget, can you explain that part as well?

 

[Norie]

Norie
Unless I am doing something wrong, when I evaluate If(upper(b41)="x", it evaluates to true whether I have a small X or a capital X in B41. I thought the same thing you stated (that it would always evaluate to false), but then I tried it. I am not sure what gidget is trying to accomplish with this portion . . .Gidget, can you explain that part as well?

That's right, must of had my VBA hat on.

If that's the case there's no need for the UPPER then.

 

[gidget424]

If B41 is marked with an "x", then any of the other conditions may apply. If B41 is not mared with an "x", then none of the conditions apply.

Yesterday, I posted a question regarding multiple conditions. I had a situation similar to this one but it only had 2 conditions, and one was that a certain cell had to be marked with an "x". I used the corrected formula that I was give and it worked fine which contained the IF(UPPER command. I tried to apply the same method to a formula today and it isn't working. Maybe I shouldn't be using the IF(UPPER command for this formula? I used it because I would have had too many IF's in the formula.

 

[colbymack]

Gidget, you wrote the equation as:
IF(UPPER(B41)="x" (Applies to all conditions below)
And
IF(S8:S13="x",0,"")
Or
IF(C1<=3,C1*C41,"")
Or
IF(C1>=4,45,"")


However, the inherent problem with that is that you have " " at the end of each of the OR items. For example, you evaluate S8:S13 for "x". IF it is true, then you return a 0. If not, then you return " ".

What do you want it to do if it finds an X in S8:S13, and it also fins that C1 <=3? If this is not possible, and you are actually intending to just chain all of these together, then you should have something like this:

IF(UPPER(B41)="x", IF(S8:S13="x",0,IF(C1<=3,C1*C41,IF(C1>=4,45,""))),"")

However, doing this results in an error in the returned value because the formula is unhappy with the evaluation of S8:S13="x". What are you trying to evaluate with this?

 

[colbymack]

Norie,
Why is it that when I evaluate S8:S13 for "x" in an IF statement by itself it returns the false value, but if I put it into gidgets formula, it chokes and give a #Value response?

I think I know why it returns the false when it is on its own - it doesn't know how to interpret the range of cells equal to "x" - but I can't figure out why it won't evaluate the range within the bigger forumual and still return false.

Either way, I am guessing this is not what gidget wants.