IF type formula likely needed

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
322
Office Version
  1. 365
Platform
  1. MacOS
Hi all,

Would anyone please be able to help with three calculations in my ‘All Completed Runs - Summary’ worksheet. In cells B117:B119 I am trying to find a way to list my ‘Hoffman’, ‘Bailey’ and ‘Full Bailey’ progress. The definition for this club is:

"XinX" is running at a number of different 5k parkrun venues before running at a venue for a second time - so the parkrunner has run "X" different Events in their first "X" parkruns.

For me, I repeated Newport in my fifth run, so my Hoffman, Bailey and Full Bailey result is 4. i.e. I ran 4 events before ever repeating one. The relevant cells should display as follows:
  • B117: '4' / C117 '4 out of 50'
  • B118: '4' / C118 '4 out of 100'
  • B119: '4' / C119 '4 out of 250'
This can now never change as it is the first time you ever repeat an event, and then that’s it.

I’m guessing this is likely to be a IF formula type solution that’s needed.

Link to file here: Hoffman, Baily, Full Bailey.xlsx

Thanks in advance!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,992
Office Version
  1. 365
Platform
  1. Windows
Enter this formula into CG3 of 'All completed runs' and fill down to the end of your data.

=IFERROR(MATCH(C3,C4:C$2002,0),"")

Then this one into B117 of the summary, array confirmed with Ctrl Shift Enter (may be a different key combination on Mac)

=MIN(IFERROR(INDEX('All Completed Runs'!$CG$3:$CG$2002,MATCH(TRUE,ISNUMBER('All Completed Runs'!$CG$3:$CG$2002),0)),0),50)

Change 50 to 100 and 250 for the other cells.
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
322
Office Version
  1. 365
Platform
  1. MacOS
Thanks. I’ve tried to avoid array formulas so far, is there a way to do this with the new unique filter options in the latest Excel version? I have this done similarly in a number of tables in my workbook, but couldn’t work out how to make it fit this. Thanks for your help.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,992
Office Version
  1. 365
Platform
  1. Windows
Where would I find the existing unique filter formulas in your workbook?
I'm not using office 365 so don't have the new functions to try anything with, but if I might be able to work something up from an existing formula if I can see how it has been written.
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
322
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Where would I find the existing unique filter formulas in your workbook?
I'm not using office 365 so don't have the new functions to try anything with, but if I might be able to work something up from an existing formula if I can see how it has been written.
Thanks. If you look at the 'All Completed Runs - E(V)' worksheet for example. This one filters to unique events completed so far and how many time they have been completed in total. What I am after here is pretty similar to this, but rather than filter to unique events, it should filter to unique events that have been completed at least twice and give a count of them in column B.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,992
Office Version
  1. 365
Platform
  1. Windows
As far as I can see, those functions can only identify the unique records, not their positions within the list.

There might be someone out there who can prove me wrong, but I don't see any way to get what you need without arrays of some kind. Even something on the lines of SMALL(UNIQUE(FILTER(...))) to get the first and second instances of a run would only be able to compare one event without an array, from previous experience such things don't play well when you try to compare multiple items.

There will most likely be other alternative formulas that will work, but ultimately I think that they will all require a helper column in the source sheet and an array formula to summarise.
Beyond that, you would need to look at alternative methods such as vba or power query.
 

Forum statistics

Threads
1,144,625
Messages
5,725,365
Members
422,621
Latest member
dfrare

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top