Nov 18 2021 11:28 PM
Hi,
CONTEXT
I am building a database for my website oneformula.org about a format for driver performance ranking IN Formula 1. I have completed a sheet "DATA" (see attachment) with : driver name/year/grand prix/race result. (columns B:H).
PROBLEM
I want to see the level of competition of each season in Formula 1 (1950-2020). I use the points spread between drivers in a single season. I converted the 6 different points systems -used from 1950-2020- into one systems - apples to apples. I understand that the function AVEDEV can give me the points spread between drivers in a single season and hence the level of competition
QUESTION
Is AVEDEV the right function for this? How can I use the data on the "DATA" sheet to get this points spread per season?
Thank you for your time.
I have added a sheet "SEASONS" as an example of the points earned in single seasons.
Nov 18 2021 11:59 PM - edited Nov 19 2021 12:00 AM
@HansExcel Perhaps a simple Pivot Table does what you need. I added a column ( col R ) to the DATA sheet for Total Points. Not really sure how to interpret AVEDEV. Am not a statistics expert, sorry.
See attached.
By the way, I've entered another formula in column S. Then you don't need all the IF formulae for the first 10 placements.
Nov 19 2021 02:46 AM
Dank je @Riny_van_Eekelen
The S column is really helpful. Will need to talk to a statistics expert indeed on the competition issue
Warm regards
Nov 20 2021 02:18 AM
Nov 20 2021 02:26 AM
@HansExcel Ik werk uitsluitend met de engelse versie van Excel, dus voor mij ziet de formula er zo uit:
=IFERROR(CHOOSE(F2,12,10,8,7,6,5,4,3,2,1),0)
CHOOSE kijkt naar de waarde in F2. Als dat 1 is, dan kiest de formula de eerste waarde uit de reeks die volgt (dus 1=12, 2=10 etc. neer tot 10=1). Als de waarde groter is dan 10 of een tekst zoals "ab" of "dsq" dan geeft de formula een foutcode. Met IFERROR( <...formule...> , 0) vang je die op en wordt een 0 weergegeven.
Nov 20 2021 02:36 AM