Do you have a way to automatically calculate averages for each probability bucket? I’m trying to start a system like this, but I don’t see a way to score the percent right I get in each probability category other than manually selecting which predictions go into which bucket (right now all my predictions are each a single row in an excel sheet).
Sort of, so firstly I have a field next to each prediction that automatically computes its “bucket number” (which is just FLOOR(<prediction> * 10)). To then get the average probability of a certain bucket, I run the following: =AVERAGE(INDEX(FILTER(C$19:K, K$19:K=A14), , 1)) - note that this is google sheets though and I’m not sure to which degree this transfers to Excel. For context, column C contains my predicted probabilities, column K contains the computed bucket numbers, and A14 here is the bucket for which I’m computing this. Similarly I count the number of predictions of a given bucket with =ROWS(FILTER(K$19:K, K$19:K<>"", K$19:K=A14)) and the ratio of predictions in that bucket that ended up true with =COUNTIF(FILTER(D$19:K, K$19:K=A14), "=1") / D14 (D19 onwards contains 1 and 0 values depending on if the prediction happened or not; D14 is the aforementioned number of predictions in that bucket).
If this doesn’t help, let me know and I can clear up one such spreadsheet, see if I can export it as xlsx file and send it to you.
Do you have a way to automatically calculate averages for each probability bucket? I’m trying to start a system like this, but I don’t see a way to score the percent right I get in each probability category other than manually selecting which predictions go into which bucket (right now all my predictions are each a single row in an excel sheet).
Sort of, so firstly I have a field next to each prediction that automatically computes its “bucket number” (which is just
FLOOR(<prediction> * 10)
). To then get the average probability of a certain bucket, I run the following:=AVERAGE(INDEX(FILTER(C$19:K, K$19:K=A14), , 1))
- note that this is google sheets though and I’m not sure to which degree this transfers to Excel. For context, column C contains my predicted probabilities, column K contains the computed bucket numbers, and A14 here is the bucket for which I’m computing this. Similarly I count the number of predictions of a given bucket with=ROWS(FILTER(K$19:K, K$19:K<>"", K$19:K=A14))
and the ratio of predictions in that bucket that ended up true with=COUNTIF(FILTER(D$19:K, K$19:K=A14), "=1") / D14
(D19 onwards contains 1 and 0 values depending on if the prediction happened or not; D14 is the aforementioned number of predictions in that bucket).If this doesn’t help, let me know and I can clear up one such spreadsheet, see if I can export it as xlsx file and send it to you.