# IF, AND, OR formula confusing

1. ## IF, AND, OR formula confusing

Hi,

Is it possible to have a formula with all this in?

If A1=P AND B1=X then C1+2
If A1=P AND B1=Y then C1+3
If A1=P AND B1=Z then C1+5

If A1=Q AND B1=X then C1+1
If A1=Q AND B1=Y then C1+ 2
If A1=Q AND B1=Z then C1+3

If A1="" then "" (this is meant to be if A1 is empty then my cell should be empty too)

I have tried all sorts of combinations but it just gets too long and confusing and I come up with an error.  Register To Reply

2. ## Re: IF, AND, OR formula confusing

Hi

You will get a much better reponse if you post a worksheet with what you have and what you would like to achieve.

To Attach a File:

2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Chris  Register To Reply

3. ## Re: IF, AND, OR formula confusing

try this I tested it and worked!!

=IF(C1<>"",IF(AND(A1="P",B1="X"),2,IF(AND(A1="P",B1="Y"),3,IF(AND(A1="P",B1="Z"),5,IF(AND(A1="Q",B1="X"),1,IF(AND(A1="Q",B1="Y"),2,IF(AND(A1="Q",B1="Z"),3,0)))))),"")  Register To Reply

4. ## Re: IF, AND, OR formula confusing

A different method

=IF(AND(OR(A1={"P","Q"}),OR(B1={"X","Y","Z"})),C1+HLOOKUP(B1,{"X","Y","Z";2,3,5;1,2,3},2+(A1="Q"),0),"")  Register To Reply

5. ## Re: IF, AND, OR formula confusing

How come some of you always want a file! Originally Posted by dogberry Hi

You will get a much better reponse if you post a worksheet with what you have and what you would like to achieve.

To Attach a File:

2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Chris  Register To Reply

6. ## Re: IF, AND, OR formula confusing

dogberry - Hi I will do this shortly thanks  Register To Reply

7. ## Re: IF, AND, OR formula confusing

rcm - Thank you
This worked when I put everything in and instead of the numbers I put in C1 plus number
Then I changed the C1 at the start to the correct square and it stopped working altogether
I shall have another go  Register To Reply

8. ## Re: IF, AND, OR formula confusing

Jason.b75 - thank you but this came up with #value when I put it in   Register To Reply

9. ## file attached

formula working.xls

Hopefully this attachement will show what I am trying to achieve.
All the formulas I have made have failed so I haven't included them as it shows how dumb I am!  Register To Reply

10. ## Re: IF, AND, OR formula confusing

Try :
Formula:  `Please Login or Register  to view this content.`

Hope this helps Edit-
Typed the formula before i saw the upload Edit 2
BTW, this formula stolen from SUMPRODUCT AND/OR usages...lol   Register To Reply

11. ## Re: file attached

Its better to mention your criteria's in separate location instead of evaluating it inside the formula. Because stepping into the formula in case of any addition or modification of conditions when the conditions are evaluated inside the formula will require too much of work.

So go through the attachment file for details.  Register To Reply

12. ## Re: IF, AND, OR formula confusing

Hi Dredwolf,
Thank you but this isn't working, this may be because X, Y and Z are not numerical values  Register To Reply

13. ## Re: file attached

Sixthsense, thank you for your solution, I think using a look uptable will work.

You use this formula
=IF(\$C5="","",VLOOKUP(\$A5&LEFT(\$B5),\$H\$2:\$I\$7,2,FALSE)+\$C5)

Please can you explain why this part is required "&LEFT" also why does (\$B5) have to be inside brackets?
Thank you  Register To Reply

14. ## Re: IF, AND, OR formula confusing

In lookup table i just mentioned the column a values and x, y.z.
But in your column b data you are differentiating each set by x/xx for each set of data ( as new beginning of sets). But after the beginning of new set the data is marked wit just x instead of x/xx. so using left function to get 1 character from each cell of column b to make the data unique. The left function should be used as left(b5,1) but using the left(b5) will also get the same result so just ignored the second argument.
sent from mobile device so accept the apologies in case of any typo errors.  Register To Reply

15. ## Re: IF, AND, OR formula confusing

This formula doesn't required a lookup table

=IF(C2="","",LOOKUP(LEFT(B2),{"X","Y","Z"},IF(LEFT(A2)="Q",{1,2,3},{2,3,5}))+C2)  Register To Reply

16. ## Re: IF, AND, OR formula confusing

Hi vox_vix

The VLOOKUP up is the way to go, easy to maintain, as it is not hard coded in.  Register To Reply

17. ## Re: IF, AND, OR formula confusing

This update of my original formula works with the sample you uploade, but it is definitely getting cumbersome...
In e2, copy down :
Formula:  `Please Login or Register  to view this content.`

the lookup solutions offered are probably much more efficient though, I just offered this to show it would work   Register To Reply

18. ## Re: IF, AND, OR formula confusing

Thank you teethless mama  Register To Reply

19. ## Re: IF, AND, OR formula confusing

You're Welcome!  Register To Reply

20. ## Re: IF, AND, OR formula confusing

Thanks for the update dredwolf!  Register To Reply

21. ## Re: IF, AND, OR formula confusing

Hi Kevin UK,
I have gone with the Vlookup because it allows me to change the numbers if I need to without getting back into the formula.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 