Is there a more efficient way of entering the function I’ve used for assigning scores in a voting survey?
Cells showing votes, with slight view of table above
Collated scores/votes where function is used
Is there a more efficient method of entering the function shown here to assign scores for each persons choices? I have a working function but it’s labour intensive the way it’s written.
=switch(B3:B62,B65,"20",B66,"19",B67,"18",B68,"17",B69,"16",B70,"15",B71,"14",B72,"13",B73,"12",B74,"11",B75,"10",B76,"9",B77,"8",B78,"7",B79,"6",B80,"5",B81,"4",B82,"3",B83,"2",B84,"1","0")
Each person votes for their top 20 with scores assigned 20-1 in descending order. The function takes these votes/scores for each person lower down and totals in the table at top of the page.
My knowledge of sheets/functions is limited to what I’ve learnt creating this, but I figure there’s a better way than what I have done, so apologies if this is a bit of a basic request.
delete everything in E3:O62 and use this in E3:
=ARRAYFORMULA(IFNA(VLOOKUP(C3:C62&E2:O2, {C64:C&
VLOOKUP(ROW(A64:A), IF(A64:A="Pos.", {ROW(A64:A), C64:C}), 2, 1),
IFERROR(21-A64:A)}, 2, 0), 0))
- Remove first element from a $_POST array
- Trouble reading numbers from file into array
- Dynamically populate a subarray inside of an array declaration
- Only print array row data if id is found in a whitelist array
- Increment variable name in a loop
- Transpose an associative 2d array
- how to split each word in a string in a separate list
- Array is always empty even when it is expected to have elements
- Build a multi-line address string with conditional components
- Access only the first row of a 2d array
- Doubled elements in result array while scraping HTML content
- Loop over an array of unknown depth
- How to access keys and values of a multidimensional array
- How to union ranges in google spreadsheets
- Validate an array as "empty" if it has no elements or if all elements have no length
- Generate an array of href strings from all <a> tags in an HTML document using Simple HTML DOM Parser
- Extracting JSON response and putting them inside a customize JSON
- Best way to filter an objects's properties for data
- How to loop over json-encoded data?
- Fill array with numbers adding up to a nominated total without exceeding item maximum
- Generate an associative 2d array with incremented column values using a flat array as first level keys
- Put Values in Multidimensional Array keys
- What is the proper way to initialize a fixed length array?
- Need help understanding pointer to a type of n elements in C (int (*p)[10])?
- Create Pivot table and add additional columns from another dataframe
- Algorithm to reverse an array/string only in terms of rotate operations
- Group data from lines of a log file by one column and create subarrays from another column
- When did iterating on the span of an array become faster than iterating on the array itself
- Use values of a flat array as the keys in every row of a 2d array
- Function to clear any value by assigning to it a default constructed value of same type