Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Excel Help Forum » Microsoft Office Application Help - Excel Help forum » Excel General » "small" function

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 07-02-2009, 06:02 PM
ikautzman ikautzman is offline
Registered User
  
Join Date: 25 Jun 2009
Location: Spokane, WA
MS Office Version:Excel 2003
Posts: 7
ikautzman is becoming part of the community
"small" function

Please Register to Remove these Ads

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
Reply With Quote
  #2  
Old 07-02-2009, 06:16 PM
contaminated contaminated is offline
Valued Forum Contributor
  
Join Date: 07 May 2009
Location: Baku, Azerbaijan
MS Office Version:Excel 2003/2007
Posts: 702
contaminated is very confident of their ability contaminated is very confident of their ability contaminated is very confident of their ability
Send a message via MSN to contaminated Send a message via Yahoo to contaminated
Re: "small" function
Try
SMALL(...,COLUMN(INDIRECT("1:20")))
__________________
Regards
Vusal M. Dadashev
Baku, Azerbaijan




Не имея собственного мнения, советы других никогда не будут достаточны
Öz fikirin olmasa, digərlərin məsləhəti sənə heçvaxt kömək ola bilməz.

The only way to read a book of excel formulas without being bored is to open it at random and, having found something that interests you, close the book and meditate.
Reply With Quote
  #3  
Old 07-02-2009, 06:51 PM
daddylonglegs's Avatar
daddylonglegs daddylonglegs is offline
Forum Moderator
  
Join Date: 14 Jan 2006
Location: England
MS Office Version:2002 (work), 2003 & 2007 (home)
Posts: 6,565
daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding
Re: "small" function
I don't think you'll get the correct result with COLUMN, try ROW..............
Reply With Quote
  #4  
Old 07-02-2009, 06:57 PM
contaminated contaminated is offline
Valued Forum Contributor
  
Join Date: 07 May 2009
Location: Baku, Azerbaijan
MS Office Version:Excel 2003/2007
Posts: 702
contaminated is very confident of their ability contaminated is very confident of their ability contaminated is very confident of their ability
Send a message via MSN to contaminated Send a message via Yahoo to contaminated
Re: "small" function
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....
__________________
Regards
Vusal M. Dadashev
Baku, Azerbaijan




Не имея собственного мнения, советы других никогда не будут достаточны
Öz fikirin olmasa, digərlərin məsləhəti sənə heçvaxt kömək ola bilməz.

The only way to read a book of excel formulas without being bored is to open it at random and, having found something that interests you, close the book and meditate.
Reply With Quote
  #5  
Old 07-02-2009, 07:09 PM
ikautzman ikautzman is offline
Registered User
  
Join Date: 25 Jun 2009
Location: Spokane, WA
MS Office Version:Excel 2003
Posts: 7
ikautzman is becoming part of the community
Re: "small" function
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.
Attached Files
File Type: xls Iannew.xls (578.5 KB, 3 views)
Reply With Quote
  #6  
Old 07-02-2009, 07:20 PM
daddylonglegs's Avatar
daddylonglegs daddylonglegs is offline
Forum Moderator
  
Join Date: 14 Jan 2006
Location: England
MS Office Version:2002 (work), 2003 & 2007 (home)
Posts: 6,565
daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding
Re: "small" function
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
Reply With Quote
  #7  
Old 07-02-2009, 07:37 PM
contaminated contaminated is offline
Valued Forum Contributor
  
Join Date: 07 May 2009
Location: Baku, Azerbaijan
MS Office Version:Excel 2003/2007
Posts: 702
contaminated is very confident of their ability contaminated is very confident of their ability contaminated is very confident of their ability
Send a message via MSN to contaminated Send a message via Yahoo to contaminated
Re: "small" function
Maybe this one in B1 of DATA sheet

=(AVERAGE(IF(chEntries<>"",SMALL(chEntries,COLUMN(chEntries)-COLUMN(A8)),""))*(0.96))
__________________
Regards
Vusal M. Dadashev
Baku, Azerbaijan




Не имея собственного мнения, советы других никогда не будут достаточны
Öz fikirin olmasa, digərlərin məsləhəti sənə heçvaxt kömək ola bilməz.

The only way to read a book of excel formulas without being bored is to open it at random and, having found something that interests you, close the book and meditate.
Reply With Quote
  #8  
Old 07-02-2009, 08:38 PM
contaminated contaminated is offline
Valued Forum Contributor
  
Join Date: 07 May 2009
Location: Baku, Azerbaijan
MS Office Version:Excel 2003/2007
Posts: 702
contaminated is very confident of their ability contaminated is very confident of their ability contaminated is very confident of their ability
Send a message via MSN to contaminated Send a message via Yahoo to contaminated
Re: "small" function
I thinl I misunderstood...
Sorry
__________________
Regards
Vusal M. Dadashev
Baku, Azerbaijan




Не имея собственного мнения, советы других никогда не будут достаточны
Öz fikirin olmasa, digərlərin məsləhəti sənə heçvaxt kömək ola bilməz.

The only way to read a book of excel formulas without being bored is to open it at random and, having found something that interests you, close the book and meditate.
Reply With Quote
  #9  
Old 07-04-2009, 02:56 AM
ikautzman ikautzman is offline
Registered User
  
Join Date: 25 Jun 2009
Location: Spokane, WA
MS Office Version:Excel 2003
Posts: 7
ikautzman is becoming part of the community
Re: "small" function
Quote:
Originally Posted by daddylonglegs View Post
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.
Attached Files
File Type: xlsx Iannew.xlsx (133.9 KB, 3 views)
Reply With Quote
  #10  
Old 07-04-2009, 05:51 AM
martindwilson's Avatar
martindwilson martindwilson is offline
Forum Guru
  
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 6,088
martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding martindwilson Has a higher level of understanding
Re: "small" function
{=AVERAGE(SMALL(chEntries,ROW(INDIRECT("1:"&B488))))*0.96}
it should look like that
see my sig for how to enter arrays
__________________
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code

how to enter array formula

recommended reading
wiki Mojito

how to say no convincingly

most important think you need
Reply With Quote
  #11  
Old 07-04-2009, 07:46 AM
daddylonglegs's Avatar
daddylonglegs daddylonglegs is offline
Forum Moderator
  
Join Date: 14 Jan 2006
Location: England
MS Office Version:2002 (work), 2003 & 2007 (home)
Posts: 6,565
daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding daddylonglegs Has a higher level of understanding
Re: "small" function
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.......
Reply With Quote


Reply

Bookmarks

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump

Contact Us - ExcelTip.com - Archive - Top