Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Spreadsheet for Handicap
I have a small spreadsheet downloadable from:
http://www.greenwichyachtclub.co.uk/racing/Book1.xls I am trying to find a way to calculate the value in column A This value has to be the average of the first 5 values in the same row excluding the highest value of 5 In my spreadsheet you will note that all rows with less than 5 numbers merely take the average of all the numbers in that row The ones that have a yellow cell under "Value" almost has a working formula but I am trying to figure out how to get Column U to automatically show the reference to the 5th value. Cells in blue are the cells that are averaged out. Cells in red are values excluded. Perhaps there is a much easier way of doing this. Thanks in advance for any assistance Regards Cathy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Spreadsheet for Handicap
This formula reproduced all your values:
=IF(COUNTA(B4:S4)=5,SUMPRODUCT(LARGE(OFFSET(A4,0, SMALL(IF(B4:S40,COLUMN(B4 :S4)-1),{1,2,3,4,5})),{2,3,4,5}))/4,SUM(B4:S4)/COUNT(B4:S4)) Except row 33 where you have t33 in U33 and it should be s33. Then the above formula matches the values produced by all rows. The formula must be entered with Ctrl+Shift+Enter. -- Regards, Tom Ogilvy "Cathy" wrote in message ... I have a small spreadsheet downloadable from: http://www.greenwichyachtclub.co.uk/racing/Book1.xls I am trying to find a way to calculate the value in column A This value has to be the average of the first 5 values in the same row excluding the highest value of 5 In my spreadsheet you will note that all rows with less than 5 numbers merely take the average of all the numbers in that row The ones that have a yellow cell under "Value" almost has a working formula but I am trying to figure out how to get Column U to automatically show the reference to the 5th value. Cells in blue are the cells that are averaged out. Cells in red are values excluded. Perhaps there is a much easier way of doing this. Thanks in advance for any assistance Regards Cathy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Spreadsheet for Handicap
Brilliant.
Thank you very much, I was getting my head in a twist over that. Will try to figure the formula out now. Never heard of using Ctrl+Shift+Enter in Excel before and would like to find out the purpose of this. WIll do a bit more reading up. PS. Yes I did make a mistake in row 33. Regards Cathy "Tom Ogilvy" wrote in message ... This formula reproduced all your values: =IF(COUNTA(B4:S4)=5,SUMPRODUCT(LARGE(OFFSET(A4,0, SMALL(IF(B4:S40,COLUM N(B4 :S4)-1),{1,2,3,4,5})),{2,3,4,5}))/4,SUM(B4:S4)/COUNT(B4:S4)) Except row 33 where you have t33 in U33 and it should be s33. Then the above formula matches the values produced by all rows. The formula must be entered with Ctrl+Shift+Enter. -- Regards, Tom Ogilvy "Cathy" wrote in message ... I have a small spreadsheet downloadable from: http://www.greenwichyachtclub.co.uk/racing/Book1.xls I am trying to find a way to calculate the value in column A This value has to be the average of the first 5 values in the same row excluding the highest value of 5 In my spreadsheet you will note that all rows with less than 5 numbers merely take the average of all the numbers in that row The ones that have a yellow cell under "Value" almost has a working formula but I am trying to figure out how to get Column U to automatically show the reference to the 5th value. Cells in blue are the cells that are averaged out. Cells in red are values excluded. Perhaps there is a much easier way of doing this. Thanks in advance for any assistance Regards Cathy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Spreadsheet for Handicap
It is called an array formula.
when properly entered with Ctrl+Shift+Enter, then if you look at it in the formula bar it will appear to be enclosed in curly brackets {=formula} to indicate that it is an array formula. (Excel displays the brackets as indicators - they are not really there) A simple example is a conditional sum - get the sum of payments by the Tom who Lives in London {=Sum((A1:A10="Tom")*(B1:B10="London")*C1:C10)} would sum the cells in C1:C10 where both the corresponding cells in columns A and B equaled to Tom and London respectively See Chip Pearson's page on this: http://www.cpearson.com/excel/array.htm -- Regards, Tom Ogilvy "Cathy" wrote in message ... Brilliant. Thank you very much, I was getting my head in a twist over that. Will try to figure the formula out now. Never heard of using Ctrl+Shift+Enter in Excel before and would like to find out the purpose of this. WIll do a bit more reading up. PS. Yes I did make a mistake in row 33. Regards Cathy "Tom Ogilvy" wrote in message ... This formula reproduced all your values: =IF(COUNTA(B4:S4)=5,SUMPRODUCT(LARGE(OFFSET(A4,0, SMALL(IF(B4:S40,COLUM N(B4 :S4)-1),{1,2,3,4,5})),{2,3,4,5}))/4,SUM(B4:S4)/COUNT(B4:S4)) Except row 33 where you have t33 in U33 and it should be s33. Then the above formula matches the values produced by all rows. The formula must be entered with Ctrl+Shift+Enter. -- Regards, Tom Ogilvy "Cathy" wrote in message ... I have a small spreadsheet downloadable from: http://www.greenwichyachtclub.co.uk/racing/Book1.xls I am trying to find a way to calculate the value in column A This value has to be the average of the first 5 values in the same row excluding the highest value of 5 In my spreadsheet you will note that all rows with less than 5 numbers merely take the average of all the numbers in that row The ones that have a yellow cell under "Value" almost has a working formula but I am trying to figure out how to get Column U to automatically show the reference to the 5th value. Cells in blue are the cells that are averaged out. Cells in red are values excluded. Perhaps there is a much easier way of doing this. Thanks in advance for any assistance Regards Cathy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Spreadsheet for Handicap
Think this is all just slightly out of my league.
Your formula works perfectly, however I am struggling to find a way to use conditional formatting to highlight the 4 numbers used in calculation in light blue and the number discarded in red. I have made the changes as per your formula and reloaded the sheet http://www.greenwichyachtclub.co.uk/racing/Book1.xls I guess I am looking for column t to show the column number of the 5th value counting from the left but if there is an easier way then I will be all ears. Thanks again in advance for any assistance. Kind regards Cathy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Spreadsheet for Handicap
Hi Cathy
To return the column number of the highest value in the row it is =INDEX($B$3:$S$4,1,MATCH(MAX(B4:S4),B4:S4))+1 For your conditional formatting, use the dropdown to select Formula Is rather than Cell Value Condition 1, Formula Is =B4=MAX($B4:$S4) Condition 2, Formula Is =B40 -- Regards Roger Govier "Cathy" wrote in message ... Think this is all just slightly out of my league. Your formula works perfectly, however I am struggling to find a way to use conditional formatting to highlight the 4 numbers used in calculation in light blue and the number discarded in red. I have made the changes as per your formula and reloaded the sheet http://www.greenwichyachtclub.co.uk/racing/Book1.xls I guess I am looking for column t to show the column number of the 5th value counting from the left but if there is an easier way then I will be all ears. Thanks again in advance for any assistance. Kind regards Cathy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Spreadsheet for Handicap
=IF(COUNT(B4:S4)=5,LEFT(ADDRESS(ROW(),SMALL(IF(B4 :S40,COLUMN(B4:S4)),5),4,
TRUE),1),"") Entered with Ctrl+Shift+Enter in T4, then drag fill downthe column. -- Regards, Tom Ogilvy "Cathy" wrote in message ... Think this is all just slightly out of my league. Your formula works perfectly, however I am struggling to find a way to use conditional formatting to highlight the 4 numbers used in calculation in light blue and the number discarded in red. I have made the changes as per your formula and reloaded the sheet http://www.greenwichyachtclub.co.uk/racing/Book1.xls I guess I am looking for column t to show the column number of the 5th value counting from the left but if there is an easier way then I will be all ears. Thanks again in advance for any assistance. Kind regards Cathy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Spreadsheet for Handicap
Just for information, she doesn't want the highest value in the row.
-- Regards, Tom Ogilvy "Roger Govier" wrote in message ... Hi Cathy To return the column number of the highest value in the row it is =INDEX($B$3:$S$4,1,MATCH(MAX(B4:S4),B4:S4))+1 For your conditional formatting, use the dropdown to select Formula Is rather than Cell Value Condition 1, Formula Is =B4=MAX($B4:$S4) Condition 2, Formula Is =B40 -- Regards Roger Govier "Cathy" wrote in message ... Think this is all just slightly out of my league. Your formula works perfectly, however I am struggling to find a way to use conditional formatting to highlight the 4 numbers used in calculation in light blue and the number discarded in red. I have made the changes as per your formula and reloaded the sheet http://www.greenwichyachtclub.co.uk/racing/Book1.xls I guess I am looking for column t to show the column number of the 5th value counting from the left but if there is an easier way then I will be all ears. Thanks again in advance for any assistance. Kind regards Cathy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Spreadsheet for Handicap
Hi Tom
My apologies. I just looked at Cathy's sheet on her website, and saw she had used the following as a test in her Conditional Formatting =MAX($B4:INDIRECT(CONCATENATE($T4,ROW()))) This led me to think she was looking for the highest value in the row. I have now read the rest of the thread, and I can see that she was asking for something different. -- Regards Roger Govier "Tom Ogilvy" wrote in message ... Just for information, she doesn't want the highest value in the row. -- Regards, Tom Ogilvy "Roger Govier" wrote in message ... Hi Cathy To return the column number of the highest value in the row it is =INDEX($B$3:$S$4,1,MATCH(MAX(B4:S4),B4:S4))+1 For your conditional formatting, use the dropdown to select Formula Is rather than Cell Value Condition 1, Formula Is =B4=MAX($B4:$S4) Condition 2, Formula Is =B40 -- Regards Roger Govier "Cathy" wrote in message ... Think this is all just slightly out of my league. Your formula works perfectly, however I am struggling to find a way to use conditional formatting to highlight the 4 numbers used in calculation in light blue and the number discarded in red. I have made the changes as per your formula and reloaded the sheet http://www.greenwichyachtclub.co.uk/racing/Book1.xls I guess I am looking for column t to show the column number of the 5th value counting from the left but if there is an easier way then I will be all ears. Thanks again in advance for any assistance. Kind regards Cathy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
golf handicap resolution | New Users to Excel | |||
golf handicap | Excel Discussion (Misc queries) | |||
golf handicap | Excel Discussion (Misc queries) | |||
Golf Handicap | Excel Worksheet Functions | |||
handicap predictions | Excel Programming |