1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel sum and average functions

Discussion in 'Business Applications' started by caraewilton, Feb 27, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Hi guys

    I am trying to set up a markbook type thing using excel. File attached.
    There are 10 sets of mark. Each set is made up of a academic mark (%), which will be typed in by the teacher. The second column is a symbol which will add or subtract bonus marks. A=25
    B=15
    C=7.5
    D=0
    E=-15

    The teacher will just type in the symbol.

    The third column is a hidden column. This column uses an if function which gives the correct bonus mark as described above.

    The fourth column must add the marks from the first column and the bonus marks in the third column.

    At the end, there must be an average column, which must keep a running average of the marks.

    My problem is this, if I use the sum function it returns a 0 value, if no values are entered. This then messes up the average value at the end. How can I enter the sum value so that if no figures are entered the total cell remains blank so that the correct average value is reflected.

    Many thanks
    Cara
     

    Attached Files:

  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Is this what you want?
     

    Attached Files:

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,667
    I think it should be more like this (only three values entered though).

    Won't you need a Lookup for the values in the Hidden Columns?
     

    Attached Files:

  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Well, now, my UK friend, in your formula
    =IF(E5="A",25,IF(E5="B",15,IF(E5="C",7.5,IF(E5="D",0,IF(E5="E",-10,"")))))
    E should be equal to -15, not -10 (see post 1) - you don't grade as hard as Cara, obviously!
    Otherwise we get the same thing, though.
    Oh, and neither of us changed the spelling of "Totoal" - is that maybe some odd SA spelling? LOL
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,667
    Sorry, I don't think that your average is quite right.
    Also that formula is not mine :D
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Uh, duh, quite right on both counts (shamefaced) I didn't even look at my average - "Sorry, I don't think that your average is quite right." indeed!!! Boy, are you being nice today!
    =(G5+K5+O5+S5+W5+AA5+AE5+AI5+AM5+AQ5)/10
    would work better for the average....
    As for the formula, well, you know, I guess Cara is just a typical woman - can't make up her mind. (I said that to annoy you Cara, you do know that, don't you?)
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,667
    The trouble is dividing by 10 only works for 10 values and cara specifically wanted to allow for Blanks.
    I like you Vlookup though, which is what I was suggesting cara needed.
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Oh, I hadn't read her post well enough - how about a column using ISBLANK to list the data that is blank as True or False, then using a COUNTIF in a manner that you caount the False responses and divide by that?
    For instance, with data in column A, I have a this in D
    =ISBLANK(A1)
    and this at the bottom of D
    =COUNTIF(D1:D17,"False")
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,667
    I think that the formula that I used in the Totals Columns Takes care of it, just add your Vlookup to the hidden columns and it should work OK.
     
  10. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Thanks guys.
    Sorry about the spelling error;)
    As for the -10, -15 thing, the powers that be are still deciding soooo, hopefully, they will eventually decide and let me know:)
    I thought of using a lookup table rather than the IF formula. The up side to that would be that should the powers that be change their mind regarding the grading, it will be much easier to adjust... I think.
    Anyway thanks a million.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Set your SUM formulas to return a numeric, if zero, have it return the NA() function. Then, on your AVERAGE formula, use an array-based formula. Something like this ...

    Code:
    =AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))
    This assumes your values are in A1:A5 (no, I have not looked at your file) and can be numerics or the NA() function, or even alpha characters, but it will only pick up valid excel-based numbers. You see where I'm going though (I hope).. :)

    HTH

    Edit: The array-based formula is confirmed with Ctrl + Shift + Enter, btw.
     
  12. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Hi, just another question, obp, in your formula you use <>
    What exactly does this mean? Does it just tell excel to look for values for greater than or less than 0 only...
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    <> means "not equal to", whereas = means "equal to".
     
  14. caraewilton

    caraewilton Thread Starter

    Joined:
    Nov 7, 2007
    Messages:
    1,352
    Thanks firefytr. I originally tried something like that but could not get it to work properly because the figures I want to average are not in consecutive cells, ie A1, B1,C1 etc but rather A1, E1, G1.
    Personally I think my problem lies in trying to work with arrays:( I can't even get them to work on paper, as my understanding is messed up. I even have to get another teacher to teach that section of the syllabus:(
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,667
    And it is checking that there is something in the cell, and if it is nothing then it puts nothing in the total.
    It seemed to work. :cool:
    Unfortunately Excel doesn't have the same "Is Null" and "Null" functions that Access has, but then I would use Access anyway because it suits inputting things like "Pupils" and "Subjects" etc. :D
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/687788

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice