Excel, getting values from the same row but different column

Question

I'm trying to write a GPA calculator in Excel (LibreOffice really, but it works the same) and what I need help with is calculating quality points. I've only ever coded in C and similar languages, so spreadsheets and their notation are incredibly foreign to me.

In case you don't know, GPA is calculated by dividing your total quality points by your total credit hours, quality points being your grade set to a 4-scale in a particular class multiplied by the class's credit value. So, for example, if I got an B in a 4-hour class, I would get 3*4 = 12 quality points for that class. If I took a 17-hour semester and earned 63 quality points, my GPA for that semester is 63/17 = 3.706.

Getting to the point, my spread sheet is set up something like this

         A          B           C
       GRADE     CREDITS     QUALITY
1        B          3           9
2        A          4          16
3        B          1           3
...

so my formula would look something like this

IF(A1="A",4*B1,
IF(A1="B",3*B1,
IF(A1="C",2*B1,
IF(A1="D",  B1,0))))

The problem is, this code will only work for row one. For any other row, I'd have to replace all the 1s with the row number being calculated. There must be a better way to write this formula. How would I go about generalizing this so I can just copy and paste the formula without editing it?

Answer 1

In Excel you can copy and paste a formula, or fill-down a formula and it will automatically update the references for you. Have you tried copying the formula down? Otherwise you can do indirect formulas that use commands like INDIRECT and ROW, but I cannot guarantee those will convert to LibreOffice.

If you need to use INDIRECT formulas, it'd look something like this:

=IF(INDIRECT("A" & ROW())="A",4*INDIRECT("B" & ROW()),
IF(INDIRECT("A" & ROW())="B",3*INDIRECT("B" & ROW()),
IF(INDIRECT("A" & ROW())="C",2*INDIRECT("B" & ROW()),
IF(INDIRECT("A" & ROW())="D",  INDIRECT("B" & ROW()),0))))