# 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.

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

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)))))),"")

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),"")

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

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

dogberry - Hi I will do this shortly thanks

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

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

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

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!

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

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.

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

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

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.

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)

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.

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

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

Thank you teethless mama

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

You're Welcome!

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

Thanks for the update dredwolf!

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.

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1