Current Events > Need some excel formula help please.

(+) Yeah! (+)

So, my school has gone and changed how the grades are being weighted this term. The midterm was worth 30 points originally. Test was out of 40 and then I had to convert it to 30 marks. Now they want it changed to 20 marks instead. Is there a formula I can use that will change this automatically? It'll be a pain in the ass to have to go over 300 cells worth of scores and change them manually. Also need it for scores that are worth 10 that need to be changed to 15.



They want it as when I change it.
T1 T2 Midterm T3 T4 Final
15 15 20 10 10 30
---

who got 9.8 what a fucking dummy
---

recreate the table next to it and multiply the scores out of 30 by 4/3

then you can just paste values on top of the old ones

wait sorry multiply it by 2/3 if you're going to marks out of 20

1) Add a column to the right of the score (if MTE is E, let's say F)

2) Write in F1: = E1*20/30. Press enter. (or whichever line has the first value)

3) Click on F1 and drag all the way down. The formula will spread to the entire column.

4) Go to the total column (say it's J). Click on J1.

5) Replace in the formula bar E1 for F1. Click enter then drag down in order to spread the formula.

Alternatively, select all the midterm values and somewhere in the right click menu there's an option to apply a math operation to all values. Dunno where.
---

If I'm understanding it correctly, you want "old score"/30 = x/20, solve for x.

Put that into a column and just hide the original.
---

https://i.gyazo.com/9f5c951122a25d48b154de524b3c4342.png

Turtlebread posted...

who got 9.8 what a fucking dummy

ESL and a couple of my students are only in it because their parents are rich. I've had students score 0/20 on spelling tests and under 10% on tests. Luckily they are in the minority. I have one student who has held onto a 98% average for 2 years while also being top of the class in all her Thai and Chinese studies, one of the smartest second graders I've ever met and is definitely the perfect student to have to teach.

and thanks for the help guys. Will try that now. I'm not really experienced with excel (I actually don't even use formulas for the most part, only for the Total Score column).
---

you can also write =2/3 in a random cell. then copy that cell.

Then highlight all the scores and paste special -> multiply

I'd go back to the original scores and then convert if I were you, don't wanna get angry phone calls due to rounding error.
---

really you should rethink your whole spreadsheet though

record each score out of what it was on the original test

then have a table with a multiplier that you can change at will

COVxy posted...

I'd go back to the original scores and then convert if I were you, don't wanna get angry phone calls due to rounding error.

They are all buried in school storage. Plus the school is stupid and actually wants me to round the weighted scores so that there are no decimal points at all. I have a meeting with them Thursday and plan to tell them that that idea is completely stupid and would drastically alter marks. A girl who scores 95% on all her tests will have her scores rounded up to 100% basically which isn't fair at all.
---

lesidesi posted...

really you should rethink your whole spreadsheet though

record each score out of what it was on the original test

then have a table with a multiplier that you can change at will

Next year I plan to. I didn't even make this one, it's what the school sent me to use. They want it streamlined and easy to read, having multiple extra scores on there will likely confuse the fuck out of them.
---

So if the test is out of 40, and you need to convert it to out of 20...
Maybe you could use division? I think you'll find the answer quite easily by messing around with Google and Excel for a bit.
---

MuayThai85 posted...

COVxy posted...

I'd go back to the original scores and then convert if I were you, don't wanna get angry phone calls due to rounding error.

They are all buried in school storage. Plus the school is stupid and actually wants me to round the weighted scores so that there are no decimal points at all. I have a meeting with them Thursday and plan to tell them that that idea is completely stupid and would drastically alter marks. A girl who scores 95% on all her tests will have her scores rounded up to 100% basically which isn't fair at all.

Wow, amazing
---

You're too good to work at this school
---

scar the 1 posted...

So if the test is out of 40, and you need to convert it to out of 20...
Maybe you could use division? I think you'll find the answer quite easily by messing around with Google and Excel for a bit.

I don't have the original scores out of 40 anymore.I would have to convert it from the already converted weighted score then convert again 20 if I did it that way. Plus they are in storage and it isn't worth the hassle of trying to find them.
---

EpicMickeyDrew posted...

You're too good to work at this school

It's a Top 10 school in the country (no bullshit either, school is mostly rich kids because of it). They are just chopping the foreign teachers off at the knees. Always last to find out anything, shit gets changed almost weekly and we don't get the materials we need to teach the class properly which is pretty fucking bad considering I teach their most expensive program.

It's an entire Thailand thing. They just don't give a fuck.
---

MuayThai85 posted...

scar the 1 posted...

So if the test is out of 40, and you need to convert it to out of 20...
Maybe you could use division? I think you'll find the answer quite easily by messing around with Google and Excel for a bit.

I don't have the original scores out of 40 anymore.I would have to convert it from the already converted weighted score then convert again 20 if I did it that way. Plus they are in storage and it isn't worth the hassle of trying to find them.

I think that some combination of division and multiplication will definitely do the trick!
---

scar the 1 posted...

I think that some combination of division and multiplication will definitely do the trick!

Lmao, you can be such a prick =P
---

COVxy posted...

scar the 1 posted...

I think that some combination of division and multiplication will definitely do the trick!

Lmao, you can be such a prick =P

Well in this case TC already got plenty of help so I can be a prick and he will still solve his problem just fine.
---