IF, AND, OR combination + how to calculate two deviations in same formula

[JohnChoint]

Hi,
I've been searching the internet for hours and still did not find a solution to my problem.
Basically, I am trying to create a formula that shows me whether there are any mistakes in a row.
For example:

Sheep	Dog	0	Cat	500
Sheep	Dog	0	Cat	700
Horse	Eal	5	Tortoise	100

If the first column is a Sheep, the second one should be a dog and so on... and if all 5 columns are correct and want excel to say "correct".
so far so good:

Please Login or Register  to view this content.

That works absolutely fine so far. But Column 5 can also be 700 as seen in row 2, and i can't figure out how to create a formula that accepts either 500 or 700 in column E. That is my main problem.
Now there is a completely different constellation that can also be correct (Row 3), but only in this order (Horse, Eal, 5, Tortoise, 100). How can i combine these two

Please Login or Register  to view this content.

The next step would be to find out in which column the mistake is. For example if C1=8 I want it to say "incorrectC" instead of just incorrect. Any way excel can tell me that?

And my last question (In a new formula only considering A1 and E1)... For A1 i basically get E1 in$. How do I find out how big the deviation between my Value and the actual price is. For example.: A sheep usually gets me 500$ but I only got 300 this time so i made -200$.

Please Login or Register  to view this content.

+

Please Login or Register  to view this content.

. How do I combine these two formulas? I tried to do an OR formula but always got errors.

I hope my questions are understandable.
I very much appreciate your help.
Cheers
John

[Glenn Kennedy]

Here's the answer to your "main problem"... now for the rest of them!!

Please Login or Register  to view this content.

[Glenn Kennedy]

Next bit...

Please Login or Register  to view this content.

[Glenn Kennedy]

Last bit...

Please Login or Register  to view this content.

[JohnChoint]

Awesome!
You were a huge help thank you very much.
My last question was whether there is a way that excel can tell me in which Column the "incorrect" part was. Any Idea if you can inculde that into the formula below? (For example if C1=8 (wrong) I want it to say "incorrectC" instead of just incorrect.)

Next bit...

Please Login or Register  to view this content.

My Attempt looks like that:

Please Login or Register  to view this content.

(that one works absolutely fine)
But I can not figure out how to include this one in the other one espescially if it is possible that there is more Incorrect than just 1.

Please Login or Register  to view this content.

But the answer i get if A1= Sheep, B1=Dog and C1 = 0 is always Look up even though it should be "correct" ?! where is the mistake?!

Thank you very much for your help

[Glenn Kennedy]

Yes. I can do that... but you need to think a bit about column E. the way you have set up this query, column E is the price that you GOT for the sale of the animal. My answer in Post #4 compares the price that you GOT with the EXPECTED PRICE (the expected price is hard-coded. If that is correct, isn't column E totally variable and should NOT be limited to 100 or 7500, or whatever. Am I missing the point??

[Glenn Kennedy]

Here is what it looks like for the first part of the question. Once we sort out EXACTLY what column E is I can modify for horses...

Please Login or Register  to view this content.

[Glenn Kennedy]

Here is where I am puzzled...

And my last question (In a new formula only considering A1 and E1)... For A1 i basically get E1 in$. How do I find out how big the deviation between my Value and the actual price is. For example.: A sheep usually gets me 500$ but I only got 300 this time so i made -200$.

Is the value in E1 FIXED, in which case including it in your error trapping is OK, or is it variable, in which case it can't be included in the error trapping and the equation supplied at post #4 is answering the wrong question...

[JohnChoint]

Hi,
thanks for your quick reply again, you are a genius.
I am supposed to get 500 for each sheep and 100 for each horse, not more, note less and thus everything else must be considered as wrong. Which means that the E1 Value is fixed. But in the end I still need to know whether i got payed more or less than i wanted.
Your formula works great so far, but how do I know if there is more than one mistake in a row,e.g. IncorrectC and IncorrectD.

Cheers
John

[Glenn Kennedy]

...but how do I know if there is more than one mistake in a row,e.g. IncorrectC and IncorrectD.

It's sounding more like you want drop-down boxes to pick responses from, rather than increasingly complicated logical equations. I'll finish off the formula to take the horses into account. Check back in about 30 mins. Something else more important than Excel to do first...

[Glenn Kennedy]

Google data dependent drop down boxes. Tell me if this is what you really need....

[JohnChoint]

Hi,
sorry the animal example was a really poor choice
It is rather some sort of telephone game. The first one whispers a word, the second one translates that word in a different language the third one the same word in a third language and the last one translates the word into an amount of money. The money should always correlate with the first word (Apple = 50$). If the apple suddenly turns into "peach" in collumn 3 the amount of money will must likely be wrong as well. But sometimes eventhough one collumn is wrong the amount of money is still right.
Thus I need to find out if there are mistakes in each row and where exactly the mistakes are and if they are just in one collumn or more than one (subsequent error). As I dont only have the word Apple but also banana I want to create a formula that tells me whether it is translated correctly. And If not in which collumn most of the errors are.
I hope this example explains my problem a little better.

Apple	Apfel	Abbel	50$
Banana	Banane	Bananas	100$

Please Login or Register  to view this content.

... and so on... if i do this for every possible constellation this formula gets enormously long and confusing as I have more than just 2 rows probably about 30 constellations that can be correct.

Thanks again
John