MINIFS with OFFSET

[ollyhughes1982]

Hi all,

I'm having trouble combining MINIFS with an OFFSET. What I want to do is find the minimum value (if it isn't 0 or a blank) in column 'I' ('Distance from Home parkrun (K)') of the 'Miscellaneous Data - AP' worksheet - I can do this fine with the following formula:

=MINIFS('Miscellaneous Data - AP'!I3:I10002,'Miscellaneous Data - AP'!I3:I10002,"<>",'Miscellaneous Data - AP'!I3:I10002,">0")

However, I then want to return the value of the corresponding cell in column G ('Latitude, Longitude (Decimal Degrees'). Please see images of the formula I came up with, which doesn't work.

Thanks!

 

[CA_Punit]

Can you share the Excel File

 

[ollyhughes1982]

parkrun - My parkrun Record - WORKING.xlsx

Microsoft Excel Workbook

1drv.ms

I basically want to display the coordinates and the distance in K next to it

Thanks.

 

[CA_Punit]

Cannot Open the File

 

[ollyhughes1982]

Apologies I will cut it down a bit in size

 

[ollyhughes1982]

New link: parkrun - My parkrun Record - WORKING.xlsx

 

[CA_Punit]

I Suppose You require the Corresponding value of Column G for the MIN Value. If it is so

Then you can try the formula as stated in formula BAR

 

[ollyhughes1982]

Thanks so much that worked perfectly and I have updated the OneDrive file. Just one more thing, which I forgot: the event also has to be one that I haven't completed yet. i.e. the corresponding cell in column 'K' of the 'Miscellaneous Data - AP' worksheet should be blank, not a 1. Please could you show how this could be built into the function? You can save over the OneDrive version, as I have master saved locally. Thanks again!

 

[ollyhughes1982]

Also, similarly to above, column 'V' of the 'Miscellaneous Data - AP' worksheet should be blank, not a 1. i.e. Only 5K events should be included in the search, not junior events.

 

[CA_Punit]

Sorry for not being able to reply.. I will go through it and reply at the earliest