I don't normally consider using Excel or Numbers to be programming but I know other people do and I feel like this might be beyond the people who frequent the other sections of the forums so here goes.
I want to be able to write a simple spreadsheet for calculating my grade in a class that has the following 3 input columns:
A: Names for all my assignments
B: The grades I got on each assignment
C: A list of weights
Now here's where it gets a bit confusing: The weight and grade in a given row aren't necessarily related to each other. Instead, the highest grade in column B gets the highest weight in column C, the second highest grade in column C gets the second highest weight in column C, and so on. I don't want the table sorted automatically by any column (I'll manually drag stuff around as necessary.)
I want an output column D which contains B, from the same row, multiplied by the correct value from C, as described in the preceding paragraph.
Does anyone know how I can do this in Numbers? I've looked up and down the available functions nothing is jumping out at me.
Edit: Naturally, it's right when I post the question after weeks of searching that an idea comes to me...
I see there's a RANK function and an INDEX function... I feel like between these two things I almost have an answer...
Actually, never mind, I just found an even more obvious function, LARGE...
D = B * LARGE(C:, RANK(B, B:, TRUE))
Now I just need to remember how exactly formatting equations works and this will be totally solved...
Edit 2X:
All done! Here's what the properly formatted formula looks like:
= (Scored Set 1) * LARGE((Available Weights), RANK((Scored Set 1), (Scored)))
It seems that getting the value from a specific cell in the same row of a different column is just (<Column Name> <Row Name>) and getting all the values from a specific column is just (<Column Name>).
Edit 3X:
Actually... it's not quite done yet... given multiple scores of equal value, it'll consider them to have the same rank, and so a single weight could be used multiple times. I may have to hack together a solution...
I want to be able to write a simple spreadsheet for calculating my grade in a class that has the following 3 input columns:
A: Names for all my assignments
B: The grades I got on each assignment
C: A list of weights
Now here's where it gets a bit confusing: The weight and grade in a given row aren't necessarily related to each other. Instead, the highest grade in column B gets the highest weight in column C, the second highest grade in column C gets the second highest weight in column C, and so on. I don't want the table sorted automatically by any column (I'll manually drag stuff around as necessary.)
I want an output column D which contains B, from the same row, multiplied by the correct value from C, as described in the preceding paragraph.
Does anyone know how I can do this in Numbers? I've looked up and down the available functions nothing is jumping out at me.
Edit: Naturally, it's right when I post the question after weeks of searching that an idea comes to me...
I see there's a RANK function and an INDEX function... I feel like between these two things I almost have an answer...
Actually, never mind, I just found an even more obvious function, LARGE...
D = B * LARGE(C:, RANK(B, B:, TRUE))
Now I just need to remember how exactly formatting equations works and this will be totally solved...
Edit 2X:
All done! Here's what the properly formatted formula looks like:
= (Scored Set 1) * LARGE((Available Weights), RANK((Scored Set 1), (Scored)))
It seems that getting the value from a specific cell in the same row of a different column is just (<Column Name> <Row Name>) and getting all the values from a specific column is just (<Column Name>).
Edit 3X:
Actually... it's not quite done yet... given multiple scores of equal value, it'll consider them to have the same rank, and so a single weight could be used multiple times. I may have to hack together a solution...
Last edited: