How to Monthly Usage Average

I have a row of cells that will have the date (cell formatted for date)
and also a qty ordered cell. How often we make entries within a mont
will vary. I help in creating a formula that will produce the followin
results: Monthly Total of Qty Ordered, Monthly Average. Any tips on ho
I can get started on this would be appreciated.

Thank you,
Raymon

--
Message posted from http://www.ExcelForum.com

0
4/8/2004 8:04:59 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
486 Views

Similar Articles

[PageSpeed] 44

Hi
you may have a look at pivot tables:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html


--
Regards
Frank Kabel
Frankfurt, Germany


> I have a row of cells that will have the date (cell formatted for
> date), and also a qty ordered cell. How often we make entries within
> a month will vary. I help in creating a formula that will produce the
> following results: Monthly Total of Qty Ordered, Monthly Average. Any
> tips on how I can get started on this would be appreciated.
>
> Thank you,
> Raymond
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
4/8/2004 8:16:21 PM
Reply:

Similar Artilces:

Daily / Monthly Chart
Hi, Is it possible that I have 2 sets of data, but I can switch between viewing either of them in 1 chart using a combo box? Daily Monthly Column A Column B Column D Column E 3 Jan 2005 1.2590 Jan 2005 1.2090 4 Jan 2005 1.2590 Feb 2005 1.2650 5 Jan 2005 1.2260 Mar 2005 1.1930 The list will be updated daily. What happens is that the data for monthly is derived from the last possible day from the daily portion, which mi...

Outlook98 100% CPU Usage
I've seen 'fixes' for Outlook 2000, but have searched in vain for something for Outlook 98. Can anyone please direct me to a possible solution. System : NT4 SP6a, Office 97, Outlook 98 Thanks degs ...

Convert years:months to months
Hello- I am hoping to use Excel to convert data that inputted in a years:months format to just months. For example, I want to translate 5:3 to 63 months. I'm running into one hiccup: Excel views the inputted data as time and is translating it into 5:30 AM, making calculations challenging Does anyone have any tips? =HOUR(A1)*12+MINUTE(A1)/10 you may need to format the cell a General as Excel can be overhelpful and use a time format best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Jen" <jvarley@u.washington.edu> wrote in ...

Average in a Pivot Table
Hello, I have a set of data that looks like this: Rep Month Day Datapoint Value Bob 3 1 In Calls 25 Steve 3 1 In Calls 18 Bob 3 1 Out Calls 27 Steve 3 1 Out Calls 32 Bob 3 2 In Calls 19 Steve 3 2 In Calls 36 Bob 3 2 Out Calls 29 Steve 3 2 Out Calls 31 I have a pivot table where I am trying to show Total Calls by Datapoint (i.e. total In Calls and total Out Calls) per month, Daily Average per rep by datapoint and Overall Daily Average by Datapoint. Datapoint is my row field, and Month is my Column Field The Total and the Daily Average Per Rep are easy (Sum of Value and Average of Value). Th...

help creating average formula
I have a spreadsheet that has 29 clients information for the year 2004, broken down by month and they've asked me to average out collections, services provided etc. I need to have this reflect the average per month. I first tried totalling all columns and then put basic average formula like this in =average(d4:d541) and figured, but I'm not sure that using this formula has given me the average per month. Would this have given me the average per month for the entire year? Thanks in advance for your help Deb, To accomplish this you should use the subtotal feature. Make sure yo...

moving averages
Is it possible to add and subtract a fixed amount to a moving average line and have the results plotted? The chart doesn't do this sort of arithmetic. Derive a new series in your worksheet, and plot that. -- David Biddulph "fred allen" <fred allen@discussions.microsoft.com> wrote in message news:97D15748-C76B-4917-8299-5CA2888BC690@microsoft.com... > Is it possible to add and subtract a fixed amount to a moving average line > and have the results plotted? ...

Weighted Average
Hey guys, problem will follow but wanted to take out a minute to let you know that you are the best and this is best Excel resource for problems or tips or tricks I have ever seen. Keep it up. Now for the problem. I want to calculate weighted average Group A- Music CDs Province $ ON - 300 (60% of Group A) QC - 200 (40% of Group A) Total - 500 Group B- Music CDs ON - 400 (80% of Group B) QC - 100 (20% of Group B) Total - 500 This is what I have, percentages within the Group. How do I calculate the Weighted Average on ...

OLAP cubes with Time scale : only 6 months
Hi, I built several OLAP cubes views (Data analysis) with the dimension Time. I built the cube with a date range from 1st january to 31st December 2010. In some views I can display the values for the 12 months of the year. For some views, I can just display the first 6 months (2 quarters) while I selected all the 12 months. What could be the issue? Thanks Xiao: It would really help if you were a bit more specific when you post, such as naming the cubes you're working with and specifying the version of the software you're using, so I' have to guess a little t...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

formula for averaging
what is the formula for calculating a rolling average Hi a little bit more information would be helpful. Probably a combination of AVERAGE and OFFSET would do -- Regards Frank Kabel Frankfurt, Germany tbone wrote: > what is the formula for calculating a rolling average Depends on how your data is laid out... One way: Assume data in column A, and want a 10-value rolling average in column B. B10: =AVERAGE(A1:A10) and copy down. XL will adjust the cell references. In article <62B38332-F93C-4920-91B7-E00D9FA90C0E@microsoft.com>, "tbone" <anonymous@discussi...

sumif color cells with same month
My file have two columns A - Month B - Amount I will change the color to red of certain cells in column B Can I use the sumif function to total column B of those cells with same month and color red ? Many thanks eva cheng You can't sum by color (is there some criteria you could use, such as greater than x value?), but you can sum by month like this: =SUMPRODUCT(--(TEXT(A2:A100,"mmm")="Apr"),B2:B100) -- Best Regards, Luke M "eva cheng" <evacheng@discussions.microsoft.com> wrote in message news:769CE3C9-1043-4D16-B872-E80B07E47C73@m...

How to pick date and month?
Hi All - Could you please help me on this? In a worksheet "A" column containing birthdays(date, dd/mm/yyyy). How can I write a code to show date & month("A" column) matching with todays date & month in the respective "B" column.? Thanks a lot in advance for your helps... Ratheesh Hi Ratheesh, You don't really need any code for this, a simple formula will work as I understand your question. Try this: =IF(A1=TODAY(),A1,"") Enter this into cell B1 and copy it down the rows that you need it. It will display the date in th...

monthly budget/check registry spreadsheet formula
I'm working on a monthly budget/check registry spreadsheet. What I want to do is have the check register link to the allowed balances in the monthly budget. I have two fields in my monthly budget for each item. A projected cost and actual cost. I want to link entries in the check registry to the actual cost of the fields in the monthly budget. I plan to use account numbers to link these fields to each other. For example . . . Say I buy groceries for $50. When I enter this into the check registry I enter all the necessary information along with an account number that links this tra...

averaging data in multiple columns
I'm looking for a formula to lookup criteria in column A and average the results in columns B, C, D, E, etc. For example I would like to know the average of how many "CATS" have been sold in a given year and I will be adding addtional months as time progresses and do not want to have to adjust my range or formula each time a new months worth of data is added. For example. column A column B column C column D column E DOGS 100 200 150 50 CATS 200 300 100 0 FISH 50 ...

Trying To Average cells Need Help!!!
This is what i have & works well {=IF(AR6="L",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")} Now what im trying to do is add AW6 IF name is entered I tried these but not working {=IF(AR6="L",IF(AW6>"",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")} {=IF(AR6="L",OR(IF(AW6>"",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")} I get a Name or False error( CAN SOMEONE PLEASE HELP) On Sat, 30 Jan 2010 10:52:01 -0800, Mike <Mike@discussions.microsoft.com> wrote: >This is what i have & works wel...

How do I make an average on a report out of formula totals?
I've made a report titled HR Efficiency Report. This report contains the employee name, their pay rate and all of the information needed to calculate formulas. I have successfully created a text box titled Total Eff that properly displays the formula =[On Std Earned]/[Total Hourly Pay]. This report is based on a query and I would like the user to be able to run the query based on a single employee (which I have successfully done as well). However, I am running into a problem when I try to get an average for my Total Eff text box. Every time I try to open it it opens an Enter Parame...

Dispay next month month
On a report i would like to dispay next month month For example it its 29th Nov 2007 i would like to dispaly December 2007 and for 5 December 2007 i would like to dispay January 2008 Thanks Simon Simon One approach would be to use the DateSerial() function to get to "next month", and the Format() function to display a date in next month as MMMM, YYYY. Regards Jeff Boyce Microsoft Office/Access MVP "Simon" <S.Dickson@shos.co.uk> wrote in message news:6cdd428b-309e-40b5-bcee-b9ac8b17ba06@s8g2000prg.googlegroups.com... > On a report i would like to dispay...

Qry Running Average
I am really stuck. I have tried this a dozen different ways without results. I want to take a simple query (Query has date formatted to month and a summed quantity) and create a running average by 12 month groupings as well as extend out to 6 months beyond for running average forecast; as shown below: [The 12 Month Running Avg are my own calculations, this is where I am stuck] Part Nbr Month SumOfOrd Qty 12 Month Running Avg 51009 Mar-06 8 51009 Apr-06 1 51008 May-06 1 51008 Jun-07 0 51008 Jul-07 0 51008 Aug-06 3 51008 Sep-06 0 51008 Oct-06 12 51008 Nov-06 2 51008 Dec-06 7 51008 Jan...

count records each month ?
I have a table called TReport which have a date field called daReportDate (yyyy-mm-dd). I want to count how many times there is a record for each month in the table. I tried it this way - SELECT daReportDate, count(daReportDate) FROM TReport WHERE year(daReportDate)=(2007) group by month(daReportDate) But it does not work. I'm not sure about the count and about the Group by. SELECT Month(daReportDate), Count(daReportDate) FROM TReport WHERE Year(daReportDate)=(2007) GROUP BY Month(daReportDate) ; In a nutshell, what you select needs to also be part of the group by except for the aggre...

Allocation of values to months
Just cannot seem to get a simple way to do this. I have three values in each row eg A2=3, B2=4, C2=300. I then have 12 columns which represent the months of the year headed 1,2 3 etc. A2 represents the month, C2 with 300 represents the value to allocate to that month, and B2 gives the number of months that this value will be allocated to , starting at month 3. This should finish up with 300 in columns 3,4,5 and 6. If the columns started in column D for example this would mean that F2, G2, H2 and I2 were each 300. This would carry on for about 150 rows with different values in A3, B3, C3 et...

Average various variable ranges
Hi, I posted this question before, but I cannot find the thread of it anywhere, so I am posting it again. If it is duplicated, please forgive me. I have 90 000 rows of data that is in the following format: CW001P01-SH-C: Time Duration Partition Utilization % 20/03/2010 23:56 900 86.3521441 21/03/2010 00:11 901 86.35425916 21/03/2010 00:26 899 86.35738494 21/03/2010 00:41 902 86.3596435 21/03/2010 00:56 901 86.36061494 21/03/2010 01:11 901 86.33145463 21/03/2010 01:26 912 86.33382161 CW001P12-SH-F: Time Duration Partition Utilization % 20/03/2010 23:56 900 2.55062256 21/03/...

Post to wrong month
What are my options when we receive items into the wrong month. We have a situation where a user was attempting to receive some items into March, changed their system date, and when posted, the items are showing up with a April post date. oops, What is the best way to fix this. Thanks ...

Average ()
Friends, How do get Average of the values from 6 Different Fields fromsame row? [Not the avg of rows from the same field} Andy On Fri, 18 Jan 2008 14:28:00 -0800, Andy <Andy@discussions.microsoft.com> wrote: >Friends, >How do get Average of the values from 6 Different Fields fromsame row? [Not >the avg of rows from the same field} >Andy If none of them will ever be NULL it's easy: ([Field1] + [Field2] + [Field3] + [Field4] + [Field5] + [Field6]) / 6 If you want to ignore nulls, i.e. the average of 1, 2, 3, NULL, NULL, NULL should be 2: (NZ([Field1]) + NZ([Fiel...

Weighted Average for Indirect Cost also variance
I have four data column with over 5000(rows of grants), I'm trying to compute the weighted average for indirect cost. Column Headings are: Column A Column B Column C Column D EIN(employer id #)Activity Code Indirect Cost Direct Cost First,is there a logical formula or statistical formula for Weighted Average.And second is there a variance feature as well. Please display how to use feature for each. Urgent Help!!!! Allison What, and where, are the weighting factors Regards Mark Graesse mark_graesser@yahoo.com ----- Allison wrote: ---- I h...

Problem Changing from Average Costing to FIFO Perpetual (Version 9
Our company changed our inventory valuation method from FIFO Perpetual to Average Costing. After two months we reverted back to FIFO Perpetual. We now have a problem when we create a new SOP document. The cost that the system is retrieving on the document is the CURRCOST from IV00101, not the STNDCOST as we would expect. We have run the Change valuation method utility and have checked links and reconciled, but this behaviour continues. If we create a new item that have not gone through this FIFO to AVG to FIFO chance cycle it picks up the STNDCOST correctly. Any ideas? Thanks, J...