thank you for noticing! the secret sauce is =IF((E14*1440)<181,0,INT(((E14*1440)-180)/30)) which calculates a 1 point penalty for every 30 seconds and subtracts it from =IF(F14+G14+H14+I14+J14<5,0,SUMPRODUCT(LARGE(F14:J14,ROW(INDIRECT("2:"&COUNT(F14:J14:F14:J14)-1))))) which adds the judges numbers and drops the highest and lowest scores. took me a while to figure out how to do that lol. eventually i want to make it so you can customize the amount of the penalty and the penalty interval and grace period so that other slams can use it and customize it without needing to be an excel guru. i also want to add meta data like average score and highest scoring poet etc...
Hot Excel spread-sheet!
ReplyDeletethank you for noticing! the secret sauce is =IF((E14*1440)<181,0,INT(((E14*1440)-180)/30)) which calculates a 1 point penalty for every 30 seconds and subtracts it from =IF(F14+G14+H14+I14+J14<5,0,SUMPRODUCT(LARGE(F14:J14,ROW(INDIRECT("2:"&COUNT(F14:J14:F14:J14)-1))))) which adds the judges numbers and drops the highest and lowest scores. took me a while to figure out how to do that lol. eventually i want to make it so you can customize the amount of the penalty and the penalty interval and grace period so that other slams can use it and customize it without needing to be an excel guru. i also want to add meta data like average score and highest scoring poet etc...
ReplyDelete