User Profile
Vicphuket
Copper Contributor
Joined Nov 23, 2018
User Widgets
Recent Discussions
Re: Excel spreadsheet managing
This is the scenario 12 guys going on a golf trip playing 4 rounds of golf at 4 different courses They will play in groups of 4 (4 balls) each day. The task is to sort the 4 balls in such a way as nobody plays with the same person more than twice over the 4 rounds. Each player has his official U.S.G.A. Index for his home course which will not change during the trip, Column C. Column A shows the heading for the groups (4 balls) and the day. Column B is the Letter allocated to the player. The order of the letters change for each course and the data relating to the player must relocate to the new position each day. Note that the cells in the handicap column contain Absolute references ie Slope, Rating & Par. Column D is the players name under the course to be played. Column E is the players handicap based on the Slope, Course Rating & Par (different for each course) The cells in this column contain a formula that calculates the players handicap. They will play a different course each day They will use the USGA world index system to determine there handicap at each course. i hope the grids attached are self explanatory, but I have tediously carried out the sorting manually vic755Views0likes0CommentsExcel spreadsheet managing
I have a small table 5 columns x 12 rows. For convenience use the spreadsheet cell identifiers, A1 to E12. E5 to E12 contain a formulae that relates to the information in columns A1,B1 & D1. I want to sort the data in column A so that the data in columns B, D & E move to the new positions. Column C is irrelevant. In other words data in columns B, D & E are dependent on the Data in column A. Any change in row position of data A column must be followed by B,D & E any advice out there please vic846Views0likes2CommentsConditional Formatting
I am using this formula to Conditionally format a worksheet. The sheet consists of a list of names vertically, top row horizontally every Tuesday for 6 months. The corresponding cells against each name are filled in weekly with a number if they have participated . The formula takes the 9 largest numbers in each row and highlights them. However for the formula to work I need to fill the sheet with Zero's and until there are 9 whole numbers in a row it also highlights the the Zero's. Is it possible to highlight the Zero's that are included in the formula without affecting the original conditional formatting =E3+COLUMN(E3)%%%>=AGGREGATE(14,6,$E3:$AD3+COLUMN($E3:$AD3)%%%,9)860Views0likes3CommentsRe: EXcel SUMPRODUCT
Hi Hatham, I had responses from yourself, erol sinan zorlu, Detief Lewin and Sergei Baklan regarding selecting the 9 largest numbers in a row. The following three formulas worked fine in selecting only the 9 largest; =AVERAGE(AGGREGATE(14,6,$E2:$AC2,{1,2,3,4,5,6,7,8,9})) =ROUND(SUMPRODUCT(LARGE(A2:AC2,ROW($A$1:$A$9)))/9,2) =SUMPRODUCT((AGGREGATE(14,4,E2:AC2,ROW($1:$9)))/9) However the problem came with the conditional formatting where I only wanted 9 numbers to be highlighted. When the row contained a number of duplicates some formulas included all of them if they were part of the 9 largest. =E2>=AGGREGATE(14,6,$E2:$Z2,9) this one did not exclude the duplicates =E2+(1/COLUMN(E2))%%%>=AGGREGATE(14,6,$E2:$Z2+(1/COLUMN($E2:$Z2))%%%,9) This one works fine from Sergei So I have settled on =AVERAGE(..........for the 9 largest And =E2+(1/COLUMN(E2))%%%>= ......... for the conditional formatting. Thank you and your fellow colleagues in the Tech Community for your help, the problem is now solved Regards Vic4.8KViews0likes8CommentsRe: EXcel SUMPRODUCT
In addition I only want 9 of the highest highlighted. If the numbers are duplicated and therefore more than 9 are highlighted, it must disregard any duplicate over and above the 9, EG: if the string of numbers were all the same, say 12 numbers all 39 then only 9 of them must be highlighted. Thks5KViews0likes1CommentRe: EXcel SUMPRODUCT
Hi, thank you for your help, replacing the 5 with 9 works fine. But with the conditional formatting it highlights all of the duplicate numbers, so it appears as if more numbers have been selected. It needs an additional condition that only allows highlighting of 9 cells Regards Vic5KViews0likes5CommentsEXcel SUMPRODUCT
=SUMPRODUCT((AGGREGATE(14,4,E2:Z2,ROW($1:$5)))/5) I have been using the above formula successfully to select the 5 largest numbers in a row of 35 numbers There are some 60 rows I now want to amend the formula to select the 9 largest numbers in a row I then use conditional formatting to highlight the selected cells Can anyone tell me what the formula for 9 selections should look like? Vic7.2KViews0likes24Comments
Recent Blog Articles
No content to show