ollyhughes1982
Well-known Member
- Joined
- Nov 27, 2018
- Messages
- 677
- Office Version
- 365
- Platform
- MacOS
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:
Thanks in advance!
I have attached a link to the file via Google Drive:
parkrun - My parkrun Record (Oliver Hughes - A132324) WORKING.xlsx
Also posted on Excel Forum: Possible COUNTIF solution
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"))
Thanks in advance!
I have attached a link to the file via Google Drive:
parkrun - My parkrun Record (Oliver Hughes - A132324) WORKING.xlsx
Also posted on Excel Forum: Possible COUNTIF solution