Changing a value without changing previous results

**Sharpshooter** · 11-26-2010, 09:19 PM

I'm the scorer for a pistol club and I inherited some spreadsheets which are basically tables that could have been created with Word. I've upgraded them to automatically perform many updates that were previously done manually, and I'm trying to decide the best way to attack this particular upgrade.

There are sheets with each shooter's grade, personal best and handicap. I'm creating another sheet to input weekly results. Points are awarded based on best overall score and best score on handicap each week. (A shooter's handicap is relative to their personal best.) I'm stuck between two objectives: if the spreadsheet detects a new PB for a shooter, I want it to update automatically, which isn't a problem, but this would in turn update their handicap which would then cause recalculation of previous weeks' handicap results.

The only way I can think to make this work is to have invisible cells with each shooter's scores on the grades sheet and then have some kind of calculation on the weekly sheet to determine which PB and handicap to use, but to be honest I'm not even sure I could make that work, and it does seem overly complicated. I was wondering if any of the gurus here have any ideas or could point me toward a function or method that would make it simpler. Hopefully I've explained well enough and hopefully the title is sufficient. An example wouldn't be much good but I'll answer any questions to clarify.

Extra info: Handicaps are directly related to PBs. The handicap applied to a weekly score is related to the best score before that day (i.e. if you shoot a new PB, your handicap for that shoot is still the old handicap.)

**Lifesigns** · 11-26-2010, 11:42 PM

Hey there,

This one sounds pretty good, you perhaps have some sample data we can view?

**Sharpshooter** · 11-27-2010, 08:52 AM

Originally Posted by Lifesigns

Hey there,

This one sounds pretty good, you perhaps have some sample data we can view?

Ok, here's some sample data I knocked up, although I don't know how much use it will be other than to illustrate what I've tried to describe. I haven't attempted the handicap part of the weekly score sheet yet since I'm still deciding the best way to approach it.

Basically the intent is for the spreadsheet to insert the shooter's handicap from the relevant sheet and add it to their total score, but the problem is that I also want the handicap to update when the PB is updated. For example, if my PB is 500 and my handicap is 90, and I shoot 525 in today's match, my handicap score is (525 + 90) 615, but my new PB is 525 and my new handicap is 68. If the spreadsheet updates the PB to 525, it also updates the handicap to 68, which then changes the handicap score for today's shoot to (525 + 68) 593. I want to avoid this and preserve the original handicap score of 615 but update everything else. I don't doubt that I would fail in attempting this so I thought I'd get some advice before I try. If there's no way around it then I'll just have a crack and see what happens; I just hoped there was a simpler way that I don't know about.

**Lifesigns** · 11-27-2010, 09:43 AM

Thanks for that it is much easier having something to work from.

Just wanted to check a few things.

What is Column B & C for, some have F and some *
I take it you want the scores on the Weekly sheet to show correctly i.e. you input the Off Gun scores and it provides all the Handicap totals. If it is a P/B then the "All" sheet is also updated and this where the issue arises
From that I deduce the scores on the "All" sheet are updated manually
Is the Handicap scoring the same as the Off Gun

I'll check this over a later.

**Sharpshooter** · 11-27-2010, 10:02 AM

Originally Posted by Lifesigns

Thanks for that it is much easier having something to work from.

Just wanted to check a few things.

What is Column B & C for, some have F and some *
I take it you want the scores on the Weekly sheet to show correctly i.e. you input the Off Gun scores and it provides all the Handicap totals. If it is a P/B then the "All" sheet is also updated and this where the issue arises
From that I deduce the scores on the "All" sheet are updated manually
Is the Handicap scoring the same as the Off Gun

I'll check this over a later.

The Fs are for females and the asterisks are for juniors. The grades vary for females and juniors and the grade formula checks those columns to determine which grading to use.
Correct, that's the aim.
At this stage, yes, but I'm trying to fix that.
Basically yes. The "Off Gun" score is used, the handicap added, and points awarded based on total.

Thanks mate, I appreciate you looking at it.

**Lifesigns** · 11-28-2010, 08:46 AM

I've had a play around and think I have it working but it's not perfect.

If you check the All sheet you'll notice I only changed AP & STD, you could consider using Lookup instead of nesting IF's it becomes hard to read and change. Below is an example:

Instead of:

=IF(E3="","",IF(C3="*",IF(B3="F",IF(E3<300,"D",IF(E3<340,"C",IF(E3<360,"B","A"))),IF(E3<450,"D",IF(E3<510,"C",IF(E3<540,"B","A")))),IF(B3="f",IF(E3<320,"D",IF(E3<350,"C",IF(E3<365,"B","A"))),IF(E3<500,"D",IF(E3<540,"C",IF(E3<560,"B","A"))))))

You could try this:

IF(E3="","",IF(C3="*",IF(B3="F",LOOKUP(E3,{0,300,340,360},{"D","C","B","A"}),LOOKUP(E3,{0,450,510,540},{"D","C","B","A"})),IF(B3="f",LOOKUP(E3,{0,320,350,365},{"D","C","B","A"}),LOOKUP(E3,{0,500,540,560},{"D","C","B","A"}))))

I added a new sheet with all the P/B scores. The All sheet takes the highest one from there so if John Smith scored 525 today this is higher than his previous 500 so it appears on All which in turns updates his Hcp. The new sheet has a record of his old P/B and this is then used on the weekly sheet to recalculate his Hcp which is added to the new score.

It's not perfect because if there is no old P/B then the Hcp returns the incorrect value as you can see from John Smith3. Also you would need to copy all the new P/B values over to the prev P/B column on the new sheet so it can used in future.

I suggest a better solution would be to redesign the workbook a little by creating a sheet with all the scores for each person by date. It would be much easier to "flag"a P/B from there and pull it over to the all sheet. The Weekly sheet will also be able to find previous scores as they are all still kept in the database so Hcp's will be calculated correctly.

Hope that helps.