Results 1 to 4 of 4

Thread: Excel Question

  1. #1

    CoolCheech's Avatar
    Member Since
    Jul 04, 2008
    Location
    Sapporo, Japan
    Posts
    73
    Specs:
    mid-2010 15" MacBook Pro 2.8 i7, 8GB, OS X 10.6.8
    Excel Question
    Maybe some excel gurus can help me out here, or maybe just someone who's not retarded like me.

    I have an excel worksheet that list my store's daily totals for the month.
    I want to get an figure to show me what I average, per day, for the current month. Easy except that if the month isn't over I don't want it to average the days that haven't happened yet.

    Example: cells A1 to A31 have a dollar figure for each day of the month (1-31).
    If I do just a regular average formula it will look like this: =AVERAGE(A1:A31), this will add up all the totals and divide it by 31.
    My dilemma is this, take today, it's December 26th, so to get the daily average it should divide the total sales by 26 in order to get the real time average, but with the regular formula it will always divide by the total number of days in the month, rather than only just the days that have elapsed.

    Thanks for the help.

  2. #2

    rogerinlondon's Avatar
    Member Since
    Sep 02, 2006
    Location
    London, England
    Posts
    398
    Specs:
    iMac Retina 5K 4GHz i7 24GB RAM MacBook Air 11-inch 1.6GHz 4GB RAM
    There is probably a more elegant solution to this, but I would make a new column where the cells next to your daily total has a IF-condition that says if your total for the day is larger than zero, then show 1.

    If you then are on day 26, you will have 26 cells with a 1 in them. To get the average for the month so far, use the sum of the full range of the new cells to divide with and you will always use the number of days for which you have entered a total.

    Is it a bit difficult to describe in writing like this, but it will work so just ask if anything is unclear.

  3. #3

    fleurya's Avatar
    Member Since
    Nov 18, 2006
    Location
    Anytown, USA
    Posts
    4,925
    Specs:
    27" iMac 2.7GHz Core i5, iPhone 4S, 3rd gen iPad
    If the cells have nothing in them, it should only average the cells that have amounts in them. So even though you have 31 cells selected, Excel will only average the 26 that have amounts in them and will include the next days day and total when you enter it in. So, it would probably be easiest to leave the cells representing days in the future with no sales completely blank instead of with a $0 so Excel won't count them.
    "Give so much time to the improvement of yourself that you have no time to criticize others"

  4. #4

    CoolCheech's Avatar
    Member Since
    Jul 04, 2008
    Location
    Sapporo, Japan
    Posts
    73
    Specs:
    mid-2010 15" MacBook Pro 2.8 i7, 8GB, OS X 10.6.8
    That worked, thanks. I knew that it had to be something simple.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with excel sheet copy question please
    By bmackstuff in forum Switcher Hangout
    Replies: 2
    Last Post: 01-14-2010, 06:34 PM
  2. excel for mac 2004 question
    By Terry1121 in forum Other Hardware and Peripherals
    Replies: 12
    Last Post: 12-01-2009, 09:47 AM
  3. Excel Question
    By dhyde in forum Running Windows (or anything else) on your Mac
    Replies: 1
    Last Post: 08-16-2009, 05:25 PM
  4. Stupid Excel Question on Mac
    By Itchy in forum Switcher Hangout
    Replies: 2
    Last Post: 06-25-2008, 11:50 AM
  5. Excel/Numbers question
    By brianwmay in forum OS X - Apps and Games
    Replies: 3
    Last Post: 04-12-2008, 05:55 AM

Posting Permissions

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