timiop2011
New Member
- Joined
- May 18, 2011
- Messages
- 1
Hi
I am trying to create a scoring spreadsheet for a childrens sport/fitness event but am having trouble with one of the formulas
The results part is fine:
It looks like this:
(Note though that I have simplified it from over 400 competitors! and made up fake teams, names etc)
The bit below works fine, you put the score in and it calculates the total points for you automatically
However, this is the Bit I am struggling with:
It is a bit where it has all the competition records from the previous years:
I cannot change the format either
. It has to stay like this
I need to create a formula which will change the competition record if one gets broken in that year.
If a record get equalled, the formula should add the competitors first initial to the competition record co-holder. For example, If Kate Smith had thrown 47.00 metres in the bean bag toss in 2010 rather than 17.32 metres, i would need the competition record cell to say “H. Jones/D. Yates/K. Smith” and the teams to say “Blue/Silver/Orange” and the Years to say “1998/2006/2010”.
However, If Kate Smith had thrown 47.01 metres in the bean bag toss in 2010 rather than 17.32 metres, I would need the competition score to say “47.01”, the name to say “K.Smith”, the team to say “Orange” and the Year to say “2010”. I need to account for “tied competition records” for up to five people.
I am in something of a tizz over creating this formula. I think it is probably the hardest formula I have ever tried to create. I have looked at using several fuctions in the formula including lookup, min, concatenate, If, match, or etc... but can’t seem to get my head round what to do. I use Excel 2007.
Any help would be gratefully accepted!
I am trying to create a scoring spreadsheet for a childrens sport/fitness event but am having trouble with one of the formulas
The results part is fine:
It looks like this:
(Note though that I have simplified it from over 400 competitors! and made up fake teams, names etc)
The bit below works fine, you put the score in and it calculates the total points for you automatically
However, this is the Bit I am struggling with:
It is a bit where it has all the competition records from the previous years:
I cannot change the format either
. It has to stay like this
I need to create a formula which will change the competition record if one gets broken in that year.
If a record get equalled, the formula should add the competitors first initial to the competition record co-holder. For example, If Kate Smith had thrown 47.00 metres in the bean bag toss in 2010 rather than 17.32 metres, i would need the competition record cell to say “H. Jones/D. Yates/K. Smith” and the teams to say “Blue/Silver/Orange” and the Years to say “1998/2006/2010”.
However, If Kate Smith had thrown 47.01 metres in the bean bag toss in 2010 rather than 17.32 metres, I would need the competition score to say “47.01”, the name to say “K.Smith”, the team to say “Orange” and the Year to say “2010”. I need to account for “tied competition records” for up to five people.
I am in something of a tizz over creating this formula. I think it is probably the hardest formula I have ever tried to create. I have looked at using several fuctions in the formula including lookup, min, concatenate, If, match, or etc... but can’t seem to get my head round what to do. I use Excel 2007.
Any help would be gratefully accepted!
