1. ## If statement that includes adjusting a number (Excel XP)

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?

Christa

2. ## Re: If statement that includes adjusting a number (Excel XP)

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?

3. ## Re: If statement that includes adjusting a number (Excel XP)

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

4. ## Re: If statement that includes adjusting a number (Excel XP)

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.

5. ## Re: If statement that includes adjusting a number (Excel XP)

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

6. ## Re: If statement that includes adjusting a number (Excel XP)

Thank you very much!!

It seems so simple now that I look at it...why couldn't I think of that :-)

7. ## Re: If statement that includes adjusting a number (Excel XP)

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

8. ## Re: If statement that includes adjusting a number

<img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

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(B22))

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 <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> this works. I have attached a wb for your delectation.

9. ## Re: If statement that includes adjusting a number

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

10. ## Re: If statement that includes adjusting a number

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. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

11. ## Re: If statement that includes adjusting a number (Excel XP)

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% <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
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%).

12. ## Re: If statement that includes adjusting a number (Excel XP)

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!

13. ## Re: If statement that includes adjusting a number (Excel XP)

If you want to keep the 4th value <= 100% then in Hans' Spreadsheet change F2 to:
<pre>=MIN(100%,IF(AND(E2>=50%,E2<=60%),60%,E2)*4-SUM(A2:C2))</pre>

Change G2 to:
<pre>=SUM(A2:C2,F2)/4</pre>

Copy F2:G2 down the column

Steve

14. ## Re: If statement that includes adjusting a number (Excel XP)

Thanks, Steve!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•