Solved If And statements xls 2010

August 9, 2011 at 15:05:24
Specs: Windows XP, 3.2 GHz / 3.5 GB
What I am looking to do is return an answer based on 1 of 3 values - under 80, over 200, or 80-199 if the 1st 2 statements are true. I'm sure I am missing somehtin obvious, but I am trying this on my own with only the help files avail, and keep hitting a brick wall. If you need more info, please let me know. Thanks in advance for the help.


=IF(Questions!H2,IF(AND(Questions!H4),IF(AND(Questions!B14>79),A23),IF(AND(Questions!B14>=200,A25),A24)))


See More: If And statements xls 2010

Report •


✔ Best Answer
August 10, 2011 at 13:14:16
re: "The Questions!xx was referring to another tab in the spreadsheet"

I know what a reference to another sheet looks like. That wasn't my point.

Everything I said in my earlier response still remains unresolved.

IF(H2 what?

You still haven't asked Excel to compare H2 to anything.

Please spend a few minutes reviewing the Excel Help file for the IF function so you can at least understand how it is supposed to work.

AND(H4) means nothing to Excel.

Please spend a few minutes reviewing the Excel Help file for the AND function so you can at least understand how it is supposed to work.

If all you are doing is checking B14 for a value then the solution is a basic Nested IF:

=IF(B14<79, "SM", IF(B14<200, "MD", "LG"))

If H2 & H4 are somehow involved in this, you'll need to explain that to me, in words, not by trying to construct another formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 9, 2011 at 19:45:50
It's really hard to tell what you are trying to do since you didn't tell us what your answers should be based on which conditions.

Give us some examples of your data and the expected outcome and we'll see if we can help.

If you are going to post any data in a table format, please click on the following line and read the instructions found via that link. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 10, 2011 at 07:52:26
Sorry - what i am trying to do is a pricing spreadsheet where there are several options based on plant type and volume, then if the # orders reaches a specific #, it bumps the price to the next level. So if it is plant type A with low volume (under 80 orders), then it is a small plant, then next level of orders is 80-under 200 and this bumps it to a med plant, over 200 goes to a large plant. So A23=SM, A24=MD, A25=LG.

The plant type and initial volume are selected on the Questions page (H2 and 4 respectively). The number of orders is entered on the quetsions page (B14).

If I am still not explaining correctly, I apologize. Hopefully this info will help. Thanks again for the help.


Report •

#3
August 10, 2011 at 09:22:30
I'm still confused. Please post some actual data and the expected results from that data.

Since your IF AND formula is totally wrong, I can't even tell what you are trying to do with it.

=IF(Questions!H2...

IF Questions!H2 what? An IF function is supposed to evaluate a logical_test that it can evaluate to TRUE or FALSE.


e.g. =IF(Questions!H2 = 79, etc.

You haven't given your IF function anything to evaluate.

AND is used to evaluate two or more logical conditions and return TRUE when all logical conditions are TRUE, e.g.

=AND(A1=4, B3=C3)

Only when A1 = 4 and B3 = C3 will that function return TRUE.

Your AND functions aren't evaluating anything, so I don't know what you are trying to do with them.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 10, 2011 at 10:13:22
The Questions!xx was referring to another tab in the spreadsheet.

Try this and see if it is better:

=IF(H2,IF(AND(H4),IF(AND(B14<79),"SM"),IF(AND(B14>=200,"LG"),"MD")))

H2 and H4 are T/F Cells and B14 is a number. If B14 is less than 79 then the value returned should be "SM", if 200 or greater then it should be "LG" and anythign in between should be "MD"

Sorry for the confusion, in my head I know what I want it to do, but expressing it and doing are are apparently 2 different things.

Thanks for your patience.


Report •

#5
August 10, 2011 at 13:14:16
✔ Best Answer
re: "The Questions!xx was referring to another tab in the spreadsheet"

I know what a reference to another sheet looks like. That wasn't my point.

Everything I said in my earlier response still remains unresolved.

IF(H2 what?

You still haven't asked Excel to compare H2 to anything.

Please spend a few minutes reviewing the Excel Help file for the IF function so you can at least understand how it is supposed to work.

AND(H4) means nothing to Excel.

Please spend a few minutes reviewing the Excel Help file for the AND function so you can at least understand how it is supposed to work.

If all you are doing is checking B14 for a value then the solution is a basic Nested IF:

=IF(B14<79, "SM", IF(B14<200, "MD", "LG"))

If H2 & H4 are somehow involved in this, you'll need to explain that to me, in words, not by trying to construct another formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 10, 2011 at 13:39:11
H2 and H4 are form control boxes that need to be checked (True) in order to calculate what the pricing will be. If either one is not checked (False) then no pricing calculations will be made. If both are true then the numbe in B14 will determine the plant size (SM,MD,LG) for the pricing to be based on.

Report •

#7
August 10, 2011 at 15:34:35
=IF(AND(H2,H4), IF(B14<79, "SM", IF(B14<200, "MD", "LG")),"No Calc")

Both H2 & H4 must contain TRUE for the initial IF to be TRUE, otherwise the Nested IF will be ignored and "No Calc" will be returned.

If the initial IF is TRUE, then the Nested IF will be evaluated.

If B14 < 79, the first IF of the Nested IF will be TRUE and SM will be returned.
If B14 is not less than 79, then the second IF of the Nested If will be evaluated.
If B14 <200, MD will be returned, if not, LG will be returned.

The question now is: Do you know why the formula works as described? Can you explain it in terms of the IF function arguments value_if_true and value_if_false?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
August 10, 2011 at 15:52:14
Based on your last reply, I modified what i had (i think i had too many AND's) and now i seem to be getting the answers i want.

Thanks again for your help and patience.

Janet


Report •


Ask Question