Background
(About) 		|
|		 Visitor
Information 		|
|		 Contest
Information 		|
|		 Nat'l, SD, & Other States'
Contest Results 		|
|		 Miscellaneous Links
and Information 		|
|		 Other Events &
Misc. Info 		|
|		 Support
The Contest


EXCEL Deduction Formulas for Scoring Corn Husking that Allow User to Pick Reference Cells

      The cell references (and named cells) below highlighted in bold can be changed so that they can be used as you wish in a spreadsheet.
      To demostrate this press the buttons to the right of the red C below. You will notice the change in the formulas above as well as in the input box to the right of the red B. So the formulas may be changed by typing cell references in the input box to the right of the red A, red B, and red D.
      Just above the red B you will see a formula that takes both quarter ounce (A2) and minutes picking (MinPick). Before you, the user, possibly modified the minutes picking varible it pointed to a named reference which I attempted to explain at the end of this page.
      Note the bold print to the right of the input box by the red E. Copy the formulas from this page to that input box and then re-copy from the inpute box. Otherwise the bold formatting in the copied text will affect the EXCEL cell.

Adjust Husk Weight to Quarter Ounce Increments
  • For corn husking use this formuula to round down to the last full 1/4 ounce
  • Last ¼ Ounce/floor/round down
    • =INT(A5*4)/4
       
  • These formulas were developed to check which rounding method was being used
    • these were not excpet by mistake
  • Nearest ¼ ounce
    • =INT(( A5+0.125)*4)/4
  • Next ¼ Ounce/ceiling/round up
    • =INT((A5+0.2499)*4)/4
Ainput cell reference for the ounces of husk



Calculate Husk Deduction Percentages from Quarter Ounce Increments
Note the combined calculation that will also take a variable for the 10, 20, or 30 minute pick
  • Thirty Minute Pick Husk Deduct
    • =IF(A2 <1.5,0,IF( A2<=2.5,( A2-1.5)*4,IF( A2>10.5,100,( A2-2.5)*12+4)))
    • reorganizes to
    • =IF( A2<1.5,0,IF( A2<=2.5,4* A2-6,IF( A2>10.5,100, A2*12-26)))
  • Twenty (and Ten) Minute Pick Husk Deduct
    • =IF( A2<1,0,IF( A2<=2,( A2-1)*4,IF( A2>10,100,( A2-2)*12+4)))
    • reorganizes to
    • =IF( A2<1,0,IF( A2<=2,4* A2-4,IF( A2>10,100, A2*12-20)))
  • Combined calculation using husk by quarter ounce (A2) and minutes picking (MinPick)
    • =IF(MinPick= 30, IF( A2<1.5,0,IF( A2<=2.5,4* A2-6,IF( A2>10.5,100, A2*12-26))) ,IF(MinPick<= 20, IF( A2<1,0,IF( A2<=2,4* A2-4,IF( A2>10,100, A2*12-20))) ,"?"))
    • This formula was developed before I knew that the 10 and 20 minute pick used the same table. So note the IF(MinPick<= 20,... is a bit of a cop out; anything less than or equal to 20 will cause the formula to produce the 10&20 minute husk deduction percentages.
Binput cell referance for the ounces of husk by quarter ounce increments
CClick these buttons for some sample values for above text box
Dinput cell reference or named reference for the minutes of picking See my attempt at the end of this to describe a named reference



Copy the above formaulas here, and then re-copy from this text box. If you don't, the bold HTML coding in the text of the formula will format the cells in EXCEL to bold.
E

Note the gleaning decution is 3 times the weight of the gleanings
In case you need it, multiply grams by 0.03527396 to get ounces


About cell references and naming cells: At the left you can see A1 in the box that show where the cursor is in the spreadsheet. At the right you can see that the A1 cell has been named MinPick. And that was done simply by typing MinPick in and pressing Enter. Cells named this way don't change which cell is reference even when a formula is copied to other cells. When