2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Advanced cell referencing issue

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

submitted by /u/Robyle4
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#AI formula generation techniques
#Excel alternatives for data analysis
#formula generator
#generative AI for data analysis
#natural language processing for spreadsheets
#Excel compatibility
#financial modeling with spreadsheets
#Excel alternatives
#rows.com
#big data management in spreadsheets
#conversational data analysis
#row zero
#real-time data collaboration
#intelligent data visualization
#data visualization tools
#enterprise data management
#big data performance
#data analysis tools
#data cleaning solutions
#Excel