Advanced cell referencing issue
Our take
For class today, we are looking through data sets and figuring out ways to answer questions we have about them. My professor sent us a table with a bunch of random test and activity scores, and we set up two basic functions to tally the scores and give final percentages. The names of the "students" are in column A, and final percentages are in K. I highlighted the set and sorted for final scores highest to lowest.
The question is: who has the highest score?
I quickly found the highest score with =max(k5:k42) and put that in a new cell close to the set in a little box/table (a few cells that have all borders drawn). That is all well and good, the highest score is 94%. But I want to know WHO has the highest score. So my professor walked me through the logic formula:
=IF(k5=MAX($K$5:$K$42), a5, "")
He put this in column L, pulled it down, and yes, this did indeed turn out the name of the highest scorer next to their mark (his table was unsorted, so his answer was in like L20). When we tried putting this formula into a random cell in my little box off to the side, it ported over the entire column's worth of logic answers.
My question, is how do i write out a logic formula in my little box of cells to do the same thing (put the value of a(x) into the cell if the corresponding k(x) is indeed the max value of k5:k42)? But NOT have excel use an entire column to do it?
I am totally aware that the simpler method would be to use a simple cell reference and then hide the column, but both my professor and I are now hell-bent on figuring out this formula.
So I want to:
-Find the max value of k5:k42
-Use the NAME of the scorer from column A in the same row as the max value
-write the formula so that if any final scores change to reflect a new highest score, the name in my chosen cell will change too
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Lookup in table, return a formulaIs there a way to lookup a name/value of a cell in a table and the resulting answer is a formula? I am wanting to build a calculation, which could use 1 or more variables, depending on the value of a dropdown menu (Im assuming a drop down, since that would force the exact name/format of the formula callout). https://preview.redd.it/bhib489h9fpg1.png?width=1115&format=png&auto=webp&s=c79605dbb3388ec2e3471e60aecb4dd1f304bcfd Above is a quick and dirty example. If in Cell8, there is a drop down that listed the values in column J (1 Face Area, Length Face Area, etc.). In Cells D-F8, a user can input the thickness, length & width. Cell H8 would lookup the value from C8, reference the table from J5:K11, and after finding the formula, it will do the appropriate calculation. What I listed as a formula is just text for the example. I am open to adjusting as needed. This is just an example. I know I could do a bunch of imbedded IF statements in this example, but assume the table from J5:K11, could be many many lines. The table could be as many columns and rows as needed. Further, the example is just a single line (row 8), the intent would be to have many (10's, 100's or even 1,000's) rows of data, so calculating the answers and looking those up the table wouldn't work, like a single line entry. Is this even possible? I would prefer to accomplish this without VBA, if possible. EDIT: Here is another version of the example. As you can see I am planning on having multiple lines of entries. Some will have the same dropdown formula requested (i.e. Length Face Area), while others may never be requested. Hopefully that clarifies a bit. Also, while I would prefer to do without VBA, if that is the "easiest" solution, I am not opposed to entertaining. https://preview.redd.it/kqil9xiljfpg1.png?width=759&format=png&auto=webp&s=71f6d68f69bd0f6a5550d256b344032d7bc9ccbb submitted by /u/ernstm63 [link] [comments]
- Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.Hello Reddit pros, I am trying to create/find a formula that will do the following: Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes? I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this: =IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"") This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight. I am aware the formula on the screenshot is different than my post body; I deleted the top row but didn't fix the formula u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed! submitted by /u/ClandestineGhost [link] [comments]
- Calculate equal-weight test averages with different totals and missing scores in ExcelHello, I’m a teacher trying to build a gradebook in Excel to track student test scores, but I’m struggling to calculate the overall grade correctly. I will have around 12 tests throughout the year, and each test has a different total number of marks (e.g., some out of 10, some out of 20, etc.). What I need: Each test should contribute equally to the student’s final grade, regardless of its total marks (e.g., 8/10 and 16/20 should be treated as equal performance) Missing tests (blank cells) should be ignored and not counted as 0 The final result should be a percentage that updates automatically Current setup: Columns F onward = individual tests (Lesson 1, Lesson 2, etc.) Row 3 = total marks for each test Student scores are entered in each row Column E is intended to show each student’s overall percentage Issues encountered: I've tried using =SUM(scores)/SUM(totals) but it creates a weighted average based on total points, which I do not want Whe I used formulas like AVERAGE(F6/F$3, ...) or AVERAGEIF it causes errors: #DIV/0! when totals or scores are blank #VALUE! when combining IF or array formulas Excel (Korean version) does not seem to support range division like F6:O6 / F$3:O$3 Or if it does, I can't wrap my head around how to make them work. Goal: A formula that converts each score to a percentage, averages them equally, ignores blanks, and can be filled down for all students I would prefer not to add extra rows per student, but a helper column is fine Optional: A way to calculate the class average (row 5) from these results Screenshots of my current setup are included for reference. My excel is stuck in Korean though and I can't change it due to my administration permissions. I don't speak Korean either! Thank you in advance for any help. https://preview.redd.it/aj4buc6q85qg1.png?width=1080&format=png&auto=webp&s=ddcf91502c137ce5b1fb54b415277365698908d9 https://preview.redd.it/o9jy7m2r85qg1.png?width=892&format=png&auto=webp&s=32ccfbba0596fd88e3fa0763a221da35f338781c https://preview.redd.it/q6klh5mr85qg1.png?width=697&format=png&auto=webp&s=2cf6a8dfa5988fbf81b217c92203452cf73a1ce9 https://preview.redd.it/n3rx5j5s85qg1.png?width=947&format=png&auto=webp&s=9e8404240303cd7acbb891c85b649d76eb898f82 https://preview.redd.it/tvt8aiqs85qg1.png?width=1080&format=png&auto=webp&s=6ca90e08017781e8200ffb3aa340abab26c5d735 submitted by /u/Puzzleheaded-Bag8904 [link] [comments]