New To Mac-Forums?

Welcome to our community! Join the discussion today by registering your FREE account. If you have any problems with the registration process, please contact us!

Get your questions answered by community gurus Advice and insight from world-class Apple enthusiasts Exclusive access to members-only contests, giveaways and deals

Join today!

 
Start a Discussion
 

Mac-Forums Brief

Subscribe to Mac-Forums Brief to receive special offers from Mac-Forums partners and sponsors

Join the conversation RSS
Schweb's Lounge Forum for general conversation, chit chat, or most topics that don't fit in another forum.

Excel Question


Post Reply New Thread Subscribe

 
Thread Tools
CoolCheech

 
CoolCheech's Avatar
 
Member Since: Jul 04, 2008
Location: Sapporo, Japan
Posts: 71
CoolCheech is on a distinguished road
Mac Specs: mid-2010 15" MacBook Pro 2.8 i7, 8GB, OS X 10.6.8

CoolCheech is offline
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.
QUOTE Thanks
rogerinlondon

 
rogerinlondon's Avatar
 
Member Since: Sep 02, 2006
Location: London, England
Posts: 397
rogerinlondon will become famous soon enough
Mac Specs: MacPro 2,66GHz 10GB RAM NV 8800GT Cinema 30" * MBP 15" Glossy 2.33 GHz 2 Duo 3GB RAM 160GB HD

rogerinlondon is offline
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.
QUOTE Thanks
fleurya

 
fleurya's Avatar
 
Member Since: Nov 18, 2006
Location: Anytown, USA
Posts: 4,900
fleurya is a name known to allfleurya is a name known to allfleurya is a name known to allfleurya is a name known to allfleurya is a name known to allfleurya is a name known to allfleurya is a name known to all
Mac Specs: 27" iMac 2.7GHz Core i5, iPhone 4S, 3rd gen iPad

fleurya is offline
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"
QUOTE Thanks
CoolCheech

 
CoolCheech's Avatar
 
Member Since: Jul 04, 2008
Location: Sapporo, Japan
Posts: 71
CoolCheech is on a distinguished road
Mac Specs: mid-2010 15" MacBook Pro 2.8 i7, 8GB, OS X 10.6.8

CoolCheech is offline
That worked, thanks. I knew that it had to be something simple.
QUOTE Thanks

Post Reply New Thread Subscribe


« Using a HDMI cable on an old xbox 360 model? | Merry Christmas Mac forums! »
Thread Tools

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off
Forum Jump

Similar Threads
Thread
Thread Starter
Forum
Replies
Last Post
The QUESTION Game ToddG Schweb's Lounge 159 08-15-2007 05:25 PM
Crazy Mac Excel Question tbgiles OS X - Apps and Games 5 03-04-2007 08:11 AM
Ask 'me' a question... eric Schweb's Lounge 22 01-12-2007 06:33 AM
Video production question macinsandiego Movies and Video 4 11-17-2004 07:39 AM
Postscript question battleangel444 OS X - Operating System 2 07-09-2004 12:45 PM

All times are GMT -4. The time now is 11:59 AM.

Powered by vBulletin
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
X

Welcome to Mac-Forums.com

Create your username to jump into the discussion!

New members like you have made this community the ultimate source for your Mac since 2003!


(4 digit year)

Already a member?