Formula to calculate points - Win, Tie and Loss situations

Seems to be an error.

When editing and previewing does not keep our message in the box ... so forces you to re type?

So I lost the original message. Not sure if anyone else is having the same problem.

Can't you just use this formula copied down from F3?

=IF(C3>E3,2,IF(E3=C3,1,0))

I don't know hockey, but it would appear that the draw occurs whenever there is an equal number of "Promoters" and "Detractors", whether that is 0 and 0 or 3 and 3, etc.

The only exception which is not clarified and I'm not sure if it can happen in Hockey; can there be no promoters, detractors and passives in a game? I assume however that this would still result in a draw? So logic wise, from what I can tell, passives have no baring on the outcome whatsoever!

Simpler than that:

Formula:

Please Login or Register  to view this content.

Formula in F3 is

=VLOOKUP(C3-E3,{-99,0;0,1;1,2},2,TRUE)

See attached file for complete example/solution

@pytheus, @Cerbera, @gak67, @nimrod - Thank you all formula versions appear to work however, can I get a small tweak.

It's highly unlikely but there could be a week where there are no Promoters, Passives, Detractors.

Currently it is returning a 1 if the week is blanked out. Can I get one of your formula's to return a 0 value if the week has no results.

Try this:

=IF(SUM(C3:E3)=0,0,IF(C3>E3,2,IF(E3=C3,1,0)))

=if(or(isnumber(e3),isnumber(d3),isnumber(c3)),vlookup(c3-e3,{-99,0;0,1;1,2},2,true),0)