Helper spreadsheet for making up points

[Mick D.]

Hi Folks,

I created a simple spreadsheet that I'm using to help me figure out where the 'best bang for buck' items are for my next flight judging. It calculates the new score you would pickup based on fixing particular items.

NCRTS-Helper2.jpg

You can copy the spreadsheet here (if you have a Google Account), or I've also included an Excel version attached to the thread which you can edit.

All you have to do to is fill out the parts in dark green, and the scoring setup for your car.

0. Fill out the "Scoring Setup" section with the maximum available points for the car being judged.
1. Fill out Section 3 "Scoring as Judged" and "Miles Driven" from your scoring sheet
2. Fill out the miles you plan to drive to the next event in Section 4 "Scoring after improvements"
3. In Section 5 "Clawbacks", Fill out the line items you need to fix, and points deducted.
4. Enter the points for items you are going to fix in the "Fixed" column for each judging section
5. The spreadsheet will do the rest and calculate how many points you will pick up.

I hope someone else finds this sheet useful, and let me know if you have feedback or corrections. Maybe there is already a tool that does this kind of thing, but I was not aware of one.

Thanks

Mick

EDIT : Updated to V1.1 4/18/23 thanks to feedback from Mark!
EDIT : Updated to V1.2 4/18/23 added Mark's code to autodetect Flight Award.

[David H.]

Re: Helper spreadsheet for making up points

Mick

Be careful that by "fixing" a CONDITION issue, you don't generate an ORIGINALITY issue.

e.g. "Repaired scratches". But by doing so you changed the original FINISH. i.e. gain Condition points and lose Originality points - resulting in no point deduction change or worse.

Dave

[Mick D.]

Re: Helper spreadsheet for making up points

Good call out David. I will be careful.

Mick

Be careful that by "fixing" a CONDITION issue, you don't generate an ORIGINALITY issue.

e.g. "Repaired scratches". But by doing so you changed the original FINISH. i.e. gain Condition points and lose Originality points - resulting in no point deduction change or worse.

Dave

[John W.]

Re: Helper spreadsheet for making up points

Nice spreadsheet. Looks like you put a lot of work into it. Hope others find it useful.

[Mark F.]

Re: Helper spreadsheet for making up points

Hi Folks, All you have to do to is fill out the parts in dark green. I hope someone else finds this sheet useful, and let me know if you have feedback or corrections. Maybe there is already a tool that does this kind of thing, but I was not aware of one. Thanks Mick

Hi Mick,

Cool ss and what I like about it is it can work for every generation Corvette (assuming the Available points are the same for each scored Section).

I, too developed many spreadsheet versions (C2 only, though) to help me campaign my car thru 2 Regionals and 1 National (1998 to 2003). Mine also incorporated anticipated costs per point re-gained. It was a real eye-opener how expensive some points were to attempt to re-gain (example: paint) and also how cheap some points were to recover (example: OPS).

For your ss, if I am looking at it correctly, I have two suggestions respectfully submitted for your consideration:

1. Cells B26 thru B30 have the values (711 thru 551) from cells B14 thru B18 hard-coded rather than simply incorporating an automatic cell reference; thus eliminating the need to enter the numbers twice.
2. Also, your Q16:Q1003 SUM reference should be changed to Q4 to be consistent w/ the others:

B26 Operations..=SUM(711,H4:H1003) ..........change to: =SUM(B14,H4:H1003)
B27 Interior.......=SUM(702,K4:K1003) ..........change to: =SUM(B15,K4:K1003)
B28 Exterior.......=SUM(1047, N4:N1003) ......change to: =SUM(B16, N4:N1003)
B29 Mechanical..=SUM(1321, Q16:Q1003)... change to: =SUM(B17, Q416:Q1003)
B30 Chassis.......=SUM(551, T4:T1003) .........change to: =SUM(B18, T4:T1003)

Hope this helps...

[Mick D.]

Re: Helper spreadsheet for making up points

Great catch Mark, thanks for reviewing the spreadsheet!

I have changed the live version to incorporate your fixes and updated the version to 1.1, with a new XLSX file also below.

I've also added a 'Scoring' sheet where you can set up the points for C2/C3/C4 if they are different (I was not sure), and they updated everywhere.

Hi Mick,

Cool ss and what I like about it is it can work for every generation Corvette (assuming the Available points are the same for each scored Section).

I, too developed many spreadsheet versions (C2 only, though) to help me campaign my car thru 2 Regionals and 1 National (1998 to 2003). Mine also incorporated anticipated costs per point re-gained. It was a real eye-opener how expensive some points were to attempt to re-gain (example: paint) and also how cheap some points were to recover (example: OPS).

For your ss, if I am looking at it correctly, I have two suggestions respectfully submitted for your consideration:

1. Cells B26 thru B30 have the values (711 thru 551) from cells B14 thru B18 hard-coded rather than simply incorporating an automatic cell reference; thus eliminating the need to enter the numbers twice.
2. Also, your Q16:Q1003 SUM reference should be changed to Q4 to be consistent w/ the others:

B26 Operations..=SUM(711,H4:H1003) ..........change to: =SUM(B14,H4:H1003)
B27 Interior.......=SUM(702,K4:K1003) ..........change to: =SUM(B15,K4:K1003)
B28 Exterior.......=SUM(1047, N4:N1003) ......change to: =SUM(B16, N4:N1003)
B29 Mechanical..=SUM(1321, Q16:Q1003)... change to: =SUM(B17, Q416:Q1003)
B30 Chassis.......=SUM(551, T4:T1003) .........change to: =SUM(B18, T4:T1003)

Hope this helps...

[Mark F.]

Re: Helper spreadsheet for making up points

Hey Mick,

Cool beans again !

Another little ditty you can do is type (or copy) the nested "IF" equation below into cell E4 and then also copy it down to cell E5 and your cell D4 judged score and cell D5 improved score values will be translated into the corresponding flight award levels, along with whether they were Chapter, Regional or National.
Note that the order the score is tested is required to work correctly and there are no spaces in the equations.

Nested "IF" Equation for cell E4:
=IF(D4>=94,$D$3&" Top Flight",IF(D4>=85,$D$3&" Second Flight",IF(D4>=75,$D$3&" Third Flight",IF(D4<75,$D$3&" No Flight Award"))))

I changed your cell D3 to “Regional” and if all goes well when you punch this in you should see something like this…PS I was checking that the score cut offs work for each award level below

Score	Flight Award
94	Regional Top Flight
93.9	Regional Second Flight
85	Regional Second Flight
84.9	Regional Third Flight
75	Regional Third Flight
74.9	Regional No Flight Award
60	Regional No Flight Award

[Mick D.]

Re: Helper spreadsheet for making up points

Another great suggestion Mark! Smart thinking. I have implemented both into v1.2 via the original links!

Kudos to your testing of the code

Hey Mick,

Cool beans again !

Another little ditty you can do is type (or copy) the nested "IF" equation below into cell E4 and then also copy it down to cell E5 and your cell D4 judged score and cell D5 improved score values will be translated into the corresponding flight award levels, along with whether they were Chapter, Regional or National.
Note that the order the score is tested is required to work correctly and there are no spaces in the equations.

Nested "IF" Equation for cell E4:
=IF(D4>=94,$D$3&" Top Flight",IF(D4>=85,$D$3&" Second Flight",IF(D4>=75,$D$3&" Third Flight",IF(D4<75,$D$3&" No Flight Award"))))

I changed your cell D3 to “Regional” and if all goes well when you punch this in you should see something like this…PS I was checking that the score cut offs work for each award level below

Score	Flight Award
94	Regional Top Flight
93.9	Regional Second Flight
85	Regional Second Flight
84.9	Regional Third Flight
75	Regional Third Flight
74.9	Regional No Flight Award
60	Regional No Flight Award

[Murray S.]

Re: Helper spreadsheet for making up points

Hi Mark: I looked at the preview of 1.2 and I like the suggested changes.
Can you make a Zip file available with those changes, when I previewed it I was unable to save it.
Thanks
Murray

[Mark F.]

Re: Helper spreadsheet for making up points

Hi Mark: I looked at the preview of 1.2 and I like the suggested changes. Can you make a Zip file available with those changes, when I previewed it I was unable to save it. Thanks Murray

Hi Murray,

Sorry, but Mick Dreeling has the original file and I didn't see that he added a zip link to V1.2 in his last post.
I looked at it on Google, but couldn't save it either...and it looks like he made a few other changes, too.

Hopefully Mick will see this and add a V1.2 zip link in his post from his end

[Mick D.]

Re: Helper spreadsheet for making up points

Hey Folks

You should be able to download it as Microsoft XLSX via the file menu in the Google Sheets version (shown below)

download.jpg

If not - I have included the Microsoft XLSX v1.2 below, It is in a zip file, because you cannot directly attach XLSX files to posts. The XLXS is inside.

Hi Murray,

Sorry, but Mick Dreeling has the original file and I didn't see that he added a zip link to V1.2 in his last post.
I looked at it on Google, but couldn't save it either...and it looks like he made a few other changes, too.

Hopefully Mick will see this and add a V1.2 zip link in his post from his end

[Mark F.]

Re: Helper spreadsheet for making up points

Hey Folks...You should be able to download it as Microsoft XLSX via the file menu in the Google Sheets version (shown below)...If not - I have included the Microsoft XLSX v1.2 below, It is in a zip file, because you cannot directly attach XLSX files to posts. The XLXS is inside.

Hey Mick,

Thanks for teaching me how to download files from Google...I was amazed at how many formats it would allow me to download your file !

and there was one there I had never seen before *.tsv (tab separated values).
so the old dog learned a new trick today