Hi,
I wonder if anyone might be able to help with some formulas, please. I have an existing table (columns A & B) that work perfectly, counting how many runs I have completed at each different parkrun venue. However, I would now like to some extra columns (C-G), but am not sure of how I calculate them. At the moment I have inserted the formulas that calculate what I want, but for all events, whereas I want them to calculate for the relevant event in that row.
So, what I essentially want to do is to keep the same calculation for each of the C-G columns, but with something added into the formula so that it is calculated only for that particular row's event.
At present, I have the following in columns C-G, which calculate correctly, but for all events:
- In A3: =IFERROR(UNIQUE(FILTER('All Completed Runs'!C3:C2002,'All Completed Runs'!E3:E2002<>"")),"")
- In B3: =IF(A3="","",COUNTIF('All Completed Runs'!C:C,A3#))
- In C3: =IF(A3="","",OFFSET(INDEX('All Completed Runs'!E3:E2002,MATCH(MAX('All Completed Runs'!E3:E2002),'All Completed Runs'!E3:E2002,FALSE)),0,21))
- In D3: =IF(A3="","",IFERROR(TEXT(SMALL('All Completed Runs'!Z3:Z2002,COUNTIF('All Completed Runs'!Z3:Z2002,0)+1),"HH:MM:SS"),""))
- In E3: =IF(A3="","",AVERAGE('All Completed Runs'!Z3:Z2002))
- In F3: =IF(A3="","",MAX('All Completed Runs'!Z3:Z2002))
- In G3: =IF(A3="","",IFERROR(MODE('All Completed Runs'!Z3:Z2002),"N/A - None / Multiple"))
Is it something really simple like just adding in a COUNTIF somewhere in each formula? I have tried a a few things, but without success.
Thanks in advance!
I have attached a link to the file via Google Drive:
https://drive.google.com/file/d/1-ud...ew?usp=sharing
Screenshot 2020-08-07 at 10.47.46.jpg
Also posted on MR. EXCEL: https://www.mrexcel.com/board/thread...ution.1142320/
Bookmarks