If statement that includes adjusting a number (Excel XP)

Topic

New Reply

I have a spreadsheet of exam marks. It calculates the average total mark for 4 exams. Because of some issues with Exam 4, everyone who has an overall average between 50% and 59%, automatically passes. Therefore I need a formula that calculates the average (I have that part) and then if the overall is between 50% and 59%, increases the 4th exam mark to where the overall average = 60%.

Can someone help?

Thanks in advance.

Christa

Reply | Quote

Replies

If you change the mark for the 4th exam to a formula, you end up with a circular reference. You must either add two extra columns: adjusted mark for the 4th exam and adjusted average (both with formulas), or use VBA code to adjust the mark for the 4th exam.
Which would you prefer?

Reply | Quote

This spreadsheet will be going to users who don’ t know VBA at all (I know a little) so the Excel version would likely be best.

If you have the time, I would like to know the VBA version as well.

Thanks,

Christa

Reply | Quote

Here is a version with 2 extra columns. You can look at the formulas to see how the adjusted average is calculated, and the adjusted mark from that.

Note: I have assumed that there are no missing marks.

Reply | Quote

Thank you very much!!

It seems so simple now that I look at it…why couldn’t I think of that 🙂

I really appreciate your help!

Reply | Quote

Jezza pointed out the following to me: if a student has scored 22%, 77%, 22% and 80%, the average is just above 50%. So the 4th mark would have to be adjusted, but … in order to obtain an overall average of 60%, the 4th mark would have to be changed to 119%
It’s up to you to decide whether you want to accept this, or whether you want to limit the mark to 100% (and an overall average below 60%).

Reply | Quote

Good point! I’ll let the programs people know and see what they want to do…I don’t think that they have thought of that either.

Thanks!

Reply | Quote

If you want to keep the 4th value <= 100% then in Hans' Spreadsheet change F2 to:

=MIN(100%,IF(AND(E2>=50%,E2<=60%),60%,E2)*4-SUM(A2:C2))

Change G2 to:

=SUM(A2:C2,F2)/4

Copy F2:G2 down the column

Steve

Reply | Quote

Thanks, Steve!

Reply | Quote

And here is a version using VBA. There is a button on the worksheet that activates the macro.

Reply | Quote

Thanks again, Hans…(for this VBA version). You are truly an Excel guru 🙂

Reply | Quote

Whilst offline solving this another way you guys seem to have sorted it out. I tried a slightly different method using:

=IF(AND(F2<49,F2<61),F2,240-SUM(B2:D2))

My thinking was that (x+y+z)/3=180

Therefore bringing in another possible score of w we would get w= 240-(x+y+z)

Using the and function we could test if the values were between 50 and 60% and make adjustments in the if statement.

I believe this works. I have attached a wb for your delectation.

Reply | Quote

Hey, thanks anyway, Jerry. The more variations I get, the more I learn for next time…nothing’s wasted…so Thank you!

Reply | Quote

Just a caveat about my solution Christa. I read your original message wrong and assumed that there was a muck up with the scores for exam 4 and my one just makes an overall adjustment to alter exam 4’s grade, regardless of the pupils mark.

I thought I would add this so as not to confuse other Loungers if they read this.

Reply | Quote