Varying calculation formula

Hi all,

Firstly, thankyou to all of you who make the daily effort to help news like me with formulas. It's so handy having a resource like this, well done to all involved.

I am sure this is an easy one, but I can't wrap my mind around it. Am sure it's mostly if/elseif but need to put it to the experts.

I need a point calculation formula that either awards 1 point for a matching score, or 0.5 awarded when the scoring is only off by 1 variance. I am sure I haven't explained that well enough so I've included a super basic document.

A bit too oversimplified!!

Try this, after some rearrangement of data:

=IF(A2=B2,1,IF(ABS(VLOOKUP(A2,F:G,2,FALSE)-VLOOKUP(B2,F:G,2,FALSE))=1,0.5,0))

Another way.

Formula:

Please Login or Register  to view this content.

Another one
C2
=(3-MATCH(ABS(MMULT(CODE(A2:B2),{1;-1})),{0,1,2}))/2

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.