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.

Average's in excel

Discussion in 'Business Applications' started by gaunch, Oct 16, 2007.

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

    gaunch Thread Starter

    Joined:
    Oct 16, 2007
    Messages:
    3
    I am using 3 column's let's say A B and C using A and B by inputting daily numbers and the total in C by using formula =A1+B1 all the way down to 30 (monthly totals). What I want is the average on a running average from the C column. I have used the =AVERAGE(C1:C30) but all I get is the divx error.
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    Try, in C1, then copy down...

    =AVERAGE($C$1:C1)

    HTH
     
  3. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    60,087
    First Name:
    Chuck
    Did you mean D1? Otherwise you get a circular reference error.
     

    Attached Files:

  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yeah. :eek:
     
  5. cherry pie

    cherry pie

    Joined:
    Jun 4, 2007
    Messages:
    345
    What's the divx error? means Div/0? or is it something else?

    And why does this happen. I got the formula running column C has the addition formula and last cell in the bottom of the column has the average. Even if no data in the sheet no errors occur.

    Just curious to know what this is all about.
     

    Attached Files:

  6. gaunch

    gaunch Thread Starter

    Joined:
    Oct 16, 2007
    Messages:
    3
    You have the setup just like I have but the average isn't right when you just enter data from day to day, so if you just have the first 5 row's filled out the average is not by 5 but by the whole column.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Can you post a sample of your file?
     
  8. cherry pie

    cherry pie

    Joined:
    Jun 4, 2007
    Messages:
    345
    Yes you're correct. I didn't notice that :rolleyes:
    but in that case can use something like this
    =SUM(C3:C23)/COUNTIF(C3:C23,"<>0")
    Not really using the average function but simulating it... somehow...:)

    firefytr, Check this out...
     

    Attached Files:

  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Technically, no, that is a full average, not a running average, as the OP asked for. Using an extra column with a formula such as I posted will in fact give the running average. That is, an average from the start to each respective rows data, not a whole average as you've posted.

    As far as your formula, yes, that is how averages work, sum divided by count.
    I was referring to the OP when I said post a sample file. :)
     
  10. FLOVETT

    FLOVETT

    Joined:
    Sep 17, 2007
    Messages:
    79
    First Name:
    Felton
    Hello,

    I have worked out another solution for you - to get a running average you would want that 'running average' to be updated each time you added a grade I presume. To do so, you would have to count the number of grades COUNT - function, then divide this by the Sum of all the grades. You could run the sum column to the end of the data and the resulting 0's would have no effect on the average. As you can see, I simply totaled the number of grades in the 1st 2 columns. If you press Ctrl ~, you can see the formulas. Hope this helps.
     

    Attached Files:

  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Those will also give you skewed results as well and is not quite a true average. I think we need clarification from the OP.

    @ gaunch: Can you give examples and the results you would expect? Or better yet, can you post that sample file?
     
  12. cherry pie

    cherry pie

    Joined:
    Jun 4, 2007
    Messages:
    345
    got it... Didn't get what the word (running) means in the beginning, guess it's a new average generated everytime a new row is added keeping the old one. and full is only the final one... right?

    I see why you specified the name in the last post:p sorree...
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Cherry pie, definitely no need to apologize, I was only pointing out where I differentiated. :)

    Regarding the "running" average, yes, you've got it. Each row contains it's own average from its row to the beginning of information. Thus, the only 'final' average would be the last average formula in the data set. You can see this very well if you try the formula set I suggested, then press Ctrl + ~ to go to formula view. Then select some of the average cells individually to see the highlighted ranges. You'll see that I merely manipulated the absolute referencing to achieve the 'running' average. :)

    HTH
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Or, if you just want the average displayed in one cell, make A31 (or any other cell) have the formula =(SUM(A1:A30)/COUNT(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A28,A29,A30))
     
  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    No need to put them in individually Loche. You can still use COUNT(A1:A30), but you're still using two formulas to mimic one (Average()).

    The largest reason to use a Sum()/Count() type formula is if you will have the possibility of having text in the range.
     
  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/639296

  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