"small" function

**ikautzman** · 07-02-2009, 07:02 PM

I am creating a golf handicap formula, the number of scores used is based on the number of rounds played. I have set up a table with the values to use a vlookup to see how many scores it should use. To complete the handicap formula i need to average the scores and then multiply by .96. The formula i am using is =(AVERAGE(SMALL(chEntries,{})))*(0.96) The problem is the "k" area of the "small" function does not seem to allow a vlookup function to provide the {1,2,3,4} or however many scores i am trying to enter. Does anyone know a way around this or maybe a different function that would work better?

Thank you for your help,

Ian

**contaminated** · 07-02-2009, 07:16 PM

Try
SMALL(...,COLUMN(INDIRECT("1:20")))

**daddylonglegs** · 07-02-2009, 07:51 PM

I don't think you'll get the correct result with COLUMN, try ROW..............

**contaminated** · 07-02-2009, 07:57 PM

I think it depends on the table
I have used ROW() when data were stored by columns. I didn't get the result I needed. When I replaced it with COLUMN() I got what I want....

**ikautzman** · 07-02-2009, 08:09 PM

That did not seem to work correctly... Attached is the file I am working on. The formula should be in the data sheet B2. You will find the Vlookup formula in B488, that number determines how many of the Course Handicaps (scores) to average.

**daddylonglegs** · 07-02-2009, 08:20 PM

You can use this formula

=AVERAGE(SMALL(chEntries,ROW(INDIRECT("1:"&B488))))*0.96

This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

**contaminated** · 07-02-2009, 08:37 PM

Maybe this one in B1 of DATA sheet

=(AVERAGE(IF(chEntries<>"",SMALL(chEntries,COLUMN(chEntries)-COLUMN(A8)),""))*(0.96))

**contaminated** · 07-02-2009, 09:38 PM

I thinl I misunderstood...

Sorry

**ikautzman** · 07-04-2009, 03:56 AM

Originally Posted by daddylonglegs

You can use this formula

=AVERAGE(SMALL(chEntries,ROW(INDIRECT("1:"&B488))))*0.96

This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

This did not seem to work correctly, when i fill in the "K" section of the "small" function with {1,2,3,4} I get a different result then your formula. Attached is the most updated spreadsheet.

**martindwilson** · 07-04-2009, 06:51 AM

{=AVERAGE(SMALL(chEntries,ROW(INDIRECT("1:"&B488))))*0.96}
it should look like that
see my sig for how to enter arrays

**daddylonglegs** · 07-04-2009, 08:46 AM

As Martin says, my suggested formula needs to be "array entered".

There's a difference when you use {1,2,3,4}, that doesn't need "array entering" so you get the correct result without it. When you replace the "array constant" {1,2,3,4} with a "calculated array" like ROW(INDIRECT("1:"&B488)) then you need CSE.......