Pages

Monday 14 June 2021

Something different: Excel!


 I made this Google sheet for the Italian Facebook group Leggo Letteratura Contemporanea (translation: "I read contemporary literature"). It allows to transform a two option poll final score in a five sets tennis match score.


Knock yourself out! ^_^"


The above screen capture of Woody Allen's Match Point (2005) opening credits was taken from aphelis.net


Here are the formulas used in the sheet:
  • lines 3 and 4 mirror one another (only 3 will be shown)
  • values in F6:10 are constant parameters

C3:
=ROUND(100*B3/SUM(B3:B4),1)
C5:
=ABS($C$3-$C$4)
C7 (no. of sets):
=IF($C$5<$F$6,IF($C$5<$F$7,5,4),3)
C8 (loser's games):
=IF($C$7=3,ROUND(12*MIN($C$3,$C$4)/((100-$F$6)/2)),IF($C$7=5,ROUND(12+12*MIN($C$3,$C$4)/49.9),ROUND(6+12*MIN($C$3,$C$4)/((100-$F$7)/2))))
C9 (winner's games):
=IF($C$7=3,18,IF($C$7=5,IF($C$5<10,ROUND(18+8*MAX($C3,$C$4)/((100+$F$7)/2))+1,ROUND(18+8*MAX($C3,$C$4)/((100+$F$7)/2))),ROUND(18+4*MAX($C$3,$C$4)/((100+$F$6)/2))))

The five sets ([E, G, I] for a three sets match ; [E, F, G, I] for a four sets match) :

E3:
=IF($B3>$B4,6,IF(($C$8-6*($C$7-3))>4,4,ROUND(($C$8-6*($C$7-3)/3))))
F3:
=IF($C$7=3,"--",IF($E$3<5,6,IF($C$7=5,$C$9-18-$H$3,$C$9-18)))
G3:
=IF($B3>$B4,6,IF(($C$8-6*($C$7-3)-$E3)>4,4,ROUND(($C$8-6*($C$7-3)-$E3)/2)))
H3:
=IF($C$7=3,"--",IF($G3<5,IF($C$7=4,"--",IF($C$5<$F$8,7,6)),IF($C$7=5,IF($C$5<$F$8,5,IF(($C$9-18)>4,4,ROUND(($C$9-18)/2))),"--")))
I3:
=IF($B3>$B4,IF($C$5<$F$9,7,6),IF($C$5<$F$9,5,$C$8-6*($C$7-3)-$E3-$G3))+IF($C$5<=$F$10,10*(ROUND((2.2-$C$5)/2,1)))

No comments:

Post a Comment