Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

    Thanks in advance.

    Christa

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 :-)

    I really appreciate your help!

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Jerry

  9. #9
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Jerry

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #14
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •