if and?

[EARTHWALKER]

Hi peeps. Im trying to give a cell a score, depending on a criteria.

Basically If cell D4=2 and cell E4=0 and C4=A then make the answer 5
If D4=2 and E4=0 and C4=H then the answer=4
If D4=2 and E4=1 and C4=A then the answer=3
If D4=2 and E4=1 and C4=H then the answer=2
If D4=1 or 0 then the answer=0

It's for a pool league and points awarded for wins at home or away an
whether the score was 2-0 or 2-1 win or they lost.

It's probably a very tall order because of specific criteria. But, i
anyone can sort this out for me. You brainy bunch can

 

[Ron Rosenfeld]

Hi peeps. Im trying to give a cell a score, depending on a criteria.

Basically If cell D4=2 and cell E4=0 and C4=A then make the answer 5
If D4=2 and E4=0 and C4=H then the answer=4
If D4=2 and E4=1 and C4=A then the answer=3
If D4=2 and E4=1 and C4=H then the answer=2
If D4=1 or 0 then the answer=0

It's for a pool league and points awarded for wins at home or away and
whether the score was 2-0 or 2-1 win or they lost.

It's probably a very tall order because of specific criteria. But, if
anyone can sort this out for me. You brainy bunch can.

Here's one way. The assumption is that the entries can only be what you
indicate above:

=IF(D4<>2,0,IF(AND(E4=0,C4="A"),5,IF(AND(E4=0,C4="H"),4,IF(C4="A",3,2))))


--ron

 

[EARTHWALKER]

That's absolutely fantastic Ron. Thank you.
Now instead of getting weekly score cards in for 60 teams and the
converting the points for individual players, I simply put the scor
it.

You have saved me hours and hours of work.

Very much appreciated. Probably more than you know.
If there is anything I can do for you, pop me an email.

(e-mail address removed)
http://www.tfcmaps.co.u

 

[AlfD]

Hi!

Try

=IF(OR(D4=0,D4=1),0,5-(D4<>2)-(C4<>"A")-(E4<>0)*2)

and copy down

Al

 

[EARTHWALKER]

Hi AlfD. I have rons in my spreadsheet now and it works just as it say
on the tin. I tried your also and that works too. many thanks.

Far too many clever people in the world.

What the forums for my next thread...no one will get an answer to that
I'm sure of it :

 

[AlfD]

Hi!

You're welcome. Look forward to the next.

Alf

PS Are you by any chance English. They tell me that no other peopl
have the concept of "being too clever by half"

 

[EARTHWALKER]

More English than most English people m8

Oh, my other thread is on the forum here. A box of jaffa cakes If yo
can sort it :

 

[Soo Cheon Jheong]

Hi,

=SUM((C4&D4&E4={"A20","H20","A21","H21"})*{5,4,3,2})


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--