Issue with LPF League Points Column

[ollyhughes1982]

[FONT=&quot]Hi.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]I have a spreadsheet that I have setup to record all of my running club’s club championship events. However, I have an issue with the ‘LPF League Points Column’ in my spreadsheet (‘Results Data’ worksheet). e.g. In this example, cells L114 and L139.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]This column (L) works perfectly for all the cells in it, but only when the first cell (K5) has a result within it. The issue is, as per my example, if the runner in the top row doesn’t compete, then the calculations in L114 and L139 show an error. I need to fix this, as obviously not every person will compete in every event. Is there a way that I can get this to work, perhaps so the formula looks down column K to find the first cell with a result in and allow the calculations in column L to then work?[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Here’s the link to the document:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]https://1drv.ms/x/s!AtIvkRgReIvwgQZMIPklw3YJxtY_[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Really stuck, please help![/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Thanks.[/FONT]

 

[stumac]

Your formula in L5:

=IF(I5="",0,(MINUTE('Membership List'!AH4)*60+SECOND('Membership List'!AH4))*((E5/5)^1.06)/((HOUR(F5)*3600)+(MINUTE(F5)*60)+SECOND(F5))*(1/AVERAGE($K$5:[COLOR=#0000ff]$K$484[/COLOR]))*100)

changes from row 6 onwards

=IF(I137="",0,(MINUTE('Membership List'!AH136)*60+SECOND('Membership List'!AH136))*((E137/5)^1.06)/((HOUR(F137)*3600)+(MINUTE(F137)*60)+SECOND(F137))*(1/AVERAGE($K$5:[COLOR=#b22222]$K$5[/COLOR]))*100)

therefore if there is nothing in 5 you are dividing by 0 and get that error.

Also the setup you have means that if you reorder lists, add in new runners etc you are extremely likely to run into problems with calculations - I would consider using Lookups (Vlookup, Hlookup or index/match) to pull the relevant members records from the members list.

 

[mole999]

you are referencing the row above AH114 is looking at AH113 which is zero hence divide by issue

 

[stumac]

Seems to be referencing AH114 in the membership List worksheet where the runners are in the same order but the list starts on row 4 instead of 5. There is no division by that cell though.

 

[ollyhughes1982]

Ah, thank you so much. It was just a simple error in that I changed the formula to the correct one in the top row, but forgot to fill it down! I did this as I rearranged the order when I had to add some new competitors in. This is as you say, a little problematic as I need to update the formula each time I do this. Thanks

 

[ollyhughes1982]

Ah, thank you so much. It was just a simple error in that I changed the formula to the correct one in the top row, but forgot to fill it down! I did this as I rearranged the order when I had to add some new competitors in. This is as you say, a little problematic as I need to update the formula each time I do this. I have now corrected.

Could you possibly amend me spreadsheet to show me how I could possibly do the index / match / lookup solution so it would automatically cater for any new rows added in / resorted?

Thanks