Average in a month/year

```I am trying to get the average of a set of numbers if they occur in a given
month. I get the infamous #N/A. As an array.......

=AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))

I would also like to count the number.  I would assume I could swap out
average for count.
```
 0
Utf
12/6/2009 5:36:01 PM
excel.worksheet.functions 4936 articles. 2 followers.

8 Replies
860 Views

Similar Articles

[PageSpeed] 41

```=SUMPRODUCT(--(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),--(Work!\$R\$3:\$R\$4293<>""),Work!\$C\$2:\$C\$4293)/
SUMPRODUCT(--(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),--(Work!\$R\$3:\$R\$4293<>""))

But you'll need to change the formula to make all the arrays the same
length.
--
David Biddulph

"PAL" <PAL@discussions.microsoft.com> wrote in message
news:51C06CE7-32D4-436C-9460-E23EDE1890FF@microsoft.com...
>I am trying to get the average of a set of numbers if they occur in a given
> month. I get the infamous #N/A. As an array.......
>
> =AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))
>
> I would also like to count the number.  I would assume I could swap out
> average for count.

```
 0
David
12/6/2009 6:45:48 PM
```> =AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))

You have a misplaced ")" and the average range starts on row 2 while all the
other ranges start on row 3.

Try this (array entered):

=AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$3:\$C\$4293)))

>I would also like to count the number.

Maybe this:

=SUMPRODUCT(--(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),--(Work!\$R\$3:\$R\$4293<>""),--(ISNUMBER(Work!\$C\$3:\$C\$4293)))

--
Biff
Microsoft Excel MVP

"PAL" <PAL@discussions.microsoft.com> wrote in message
news:51C06CE7-32D4-436C-9460-E23EDE1890FF@microsoft.com...
>I am trying to get the average of a set of numbers if they occur in a given
> month. I get the infamous #N/A. As an array.......
>
> =AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))
>
> I would also like to count the number.  I would assume I could swap out
> average for count.

```
 0
T
12/6/2009 6:46:43 PM
```Hi,

The average formula, ARRAY entered

=AVERAGE(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))

and the COUNT formula., once again ARRAY entered

=COUNT(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))

Mike

"PAL" wrote:

> I am trying to get the average of a set of numbers if they occur in a given
> month. I get the infamous #N/A. As an array.......
>
> =AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))
>
> I would also like to count the number.  I would assume I could swap out
> average for count.
```
 0
Utf
12/6/2009 6:51:02 PM
```Not quite right.  I am getting the #DIV/0 error.  I know there should be data
based on the original formula I used.  Which is below.  I have some many
tables with multiple rows it was too cumbersone.  I hope to be able to figure
out what is wrong with the formula you and T. Valko provided.

=AVERAGE(IF(Work!\$Q\$3:\$Q\$4293>=DATE(2008,11,1),IF(Work!\$R\$3:\$R\$4293<>"",IF(Work!\$Q\$3:\$Q\$4293<DATE(2008,12,1),Work!\$C\$3:\$C\$4293,""))))

"Mike H" wrote:

> Hi,
>
> The average formula, ARRAY entered
>
> =AVERAGE(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
>
> and the COUNT formula., once again ARRAY entered
>
> =COUNT(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
>
> Mike
>
>
>
> "PAL" wrote:
>
> > I am trying to get the average of a set of numbers if they occur in a given
> > month. I get the infamous #N/A. As an array.......
> >
> > =AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))
> >
> > I would also like to count the number.  I would assume I could swap out
> > average for count.
```
 0
Utf
12/7/2009 1:39:01 AM
```>I am getting the #DIV/0 error.
>=AVERAGE(IF(Work!\$Q\$3:\$Q\$4293>=DATE(2008,11,1),IF(Work!\$R\$3:\$R\$4293<>"",IF(Work!\$Q\$3:\$Q\$4293<DATE(2008,12,1),Work!\$C\$3:\$C\$4293,""))))

There's nothing wrong with that formula.

>I know there should be data

Then there's a problem with your data. Are you sure your dates are true
Excel dates? What result do you get with this formula:

=SUMPRODUCT(--(MONTH(Work!Q3:Q4293)=11),--(YEAR(Work!Q3:Q4293)=2008))

If you get 0 that means that either your dates are not true Excel dates or
there are no dates for Nov 2008.

--
Biff
Microsoft Excel MVP

"PAL" <PAL@discussions.microsoft.com> wrote in message
news:DF4B025D-78CB-415F-8724-31ED0271DB8E@microsoft.com...
> Not quite right.  I am getting the #DIV/0 error.  I know there should be
> data
> based on the original formula I used.  Which is below.  I have some many
> tables with multiple rows it was too cumbersone.  I hope to be able to
> figure
> out what is wrong with the formula you and T. Valko provided.
>
>
> =AVERAGE(IF(Work!\$Q\$3:\$Q\$4293>=DATE(2008,11,1),IF(Work!\$R\$3:\$R\$4293<>"",IF(Work!\$Q\$3:\$Q\$4293<DATE(2008,12,1),Work!\$C\$3:\$C\$4293,""))))
>
>
>
> "Mike H" wrote:
>
>> Hi,
>>
>> The average formula, ARRAY entered
>>
>> =AVERAGE(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
>>
>> and the COUNT formula., once again ARRAY entered
>>
>> =COUNT(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
>>
>> Mike
>>
>>
>>
>> "PAL" wrote:
>>
>> > I am trying to get the average of a set of numbers if they occur in a
>> > given
>> > month. I get the infamous #N/A. As an array.......
>> >
>> > =AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))
>> >
>> > I would also like to count the number.  I would assume I could swap out
>> > average for count.

```
 0
T
12/7/2009 2:07:26 AM
```Your sumproduct formula gives me exactly what is expected.

"T. Valko" wrote:

> >I am getting the #DIV/0 error.
> >=AVERAGE(IF(Work!\$Q\$3:\$Q\$4293>=DATE(2008,11,1),IF(Work!\$R\$3:\$R\$4293<>"",IF(Work!\$Q\$3:\$Q\$4293<DATE(2008,12,1),Work!\$C\$3:\$C\$4293,""))))
>
> There's nothing wrong with that formula.
>
> >I know there should be data
>
> Then there's a problem with your data. Are you sure your dates are true
> Excel dates? What result do you get with this formula:
>
> =SUMPRODUCT(--(MONTH(Work!Q3:Q4293)=11),--(YEAR(Work!Q3:Q4293)=2008))
>
> If you get 0 that means that either your dates are not true Excel dates or
> there are no dates for Nov 2008.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "PAL" <PAL@discussions.microsoft.com> wrote in message
> news:DF4B025D-78CB-415F-8724-31ED0271DB8E@microsoft.com...
> > Not quite right.  I am getting the #DIV/0 error.  I know there should be
> > data
> > based on the original formula I used.  Which is below.  I have some many
> > tables with multiple rows it was too cumbersone.  I hope to be able to
> > figure
> > out what is wrong with the formula you and T. Valko provided.
> >
> >
> > =AVERAGE(IF(Work!\$Q\$3:\$Q\$4293>=DATE(2008,11,1),IF(Work!\$R\$3:\$R\$4293<>"",IF(Work!\$Q\$3:\$Q\$4293<DATE(2008,12,1),Work!\$C\$3:\$C\$4293,""))))
> >
> >
> >
> > "Mike H" wrote:
> >
> >> Hi,
> >>
> >> The average formula, ARRAY entered
> >>
> >> =AVERAGE(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
> >>
> >> and the COUNT formula., once again ARRAY entered
> >>
> >> =COUNT(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
> >>
> >> Mike
> >>
> >>
> >>
> >> "PAL" wrote:
> >>
> >> > I am trying to get the average of a set of numbers if they occur in a
> >> > given
> >> > month. I get the infamous #N/A. As an array.......
> >> >
> >> > =AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))
> >> >
> >> > I would also like to count the number.  I would assume I could swap out
> >> > average for count.
>
>
> .
>
```
 0
Utf
12/7/2009 3:04:01 AM
```Got it.  The format in A6 was messed up.  Thanks.

"T. Valko" wrote:

> >I am getting the #DIV/0 error.
> >=AVERAGE(IF(Work!\$Q\$3:\$Q\$4293>=DATE(2008,11,1),IF(Work!\$R\$3:\$R\$4293<>"",IF(Work!\$Q\$3:\$Q\$4293<DATE(2008,12,1),Work!\$C\$3:\$C\$4293,""))))
>
> There's nothing wrong with that formula.
>
> >I know there should be data
>
> Then there's a problem with your data. Are you sure your dates are true
> Excel dates? What result do you get with this formula:
>
> =SUMPRODUCT(--(MONTH(Work!Q3:Q4293)=11),--(YEAR(Work!Q3:Q4293)=2008))
>
> If you get 0 that means that either your dates are not true Excel dates or
> there are no dates for Nov 2008.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "PAL" <PAL@discussions.microsoft.com> wrote in message
> news:DF4B025D-78CB-415F-8724-31ED0271DB8E@microsoft.com...
> > Not quite right.  I am getting the #DIV/0 error.  I know there should be
> > data
> > based on the original formula I used.  Which is below.  I have some many
> > tables with multiple rows it was too cumbersone.  I hope to be able to
> > figure
> > out what is wrong with the formula you and T. Valko provided.
> >
> >
> > =AVERAGE(IF(Work!\$Q\$3:\$Q\$4293>=DATE(2008,11,1),IF(Work!\$R\$3:\$R\$4293<>"",IF(Work!\$Q\$3:\$Q\$4293<DATE(2008,12,1),Work!\$C\$3:\$C\$4293,""))))
> >
> >
> >
> > "Mike H" wrote:
> >
> >> Hi,
> >>
> >> The average formula, ARRAY entered
> >>
> >> =AVERAGE(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
> >>
> >> and the COUNT formula., once again ARRAY entered
> >>
> >> =COUNT(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
> >>
> >> Mike
> >>
> >>
> >>
> >> "PAL" wrote:
> >>
> >> > I am trying to get the average of a set of numbers if they occur in a
> >> > given
> >> > month. I get the infamous #N/A. As an array.......
> >> >
> >> > =AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))
> >> >
> >> > I would also like to count the number.  I would assume I could swap out
> >> > average for count.
>
>
> .
>
```
 0
Utf
12/7/2009 3:10:01 AM
```Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"PAL" <PAL@discussions.microsoft.com> wrote in message
news:02155015-42FF-44CA-AE44-EDC6DDC93216@microsoft.com...
> Got it.  The format in A6 was messed up.  Thanks.
>
> "T. Valko" wrote:
>
>> >I am getting the #DIV/0 error.
>> >=AVERAGE(IF(Work!\$Q\$3:\$Q\$4293>=DATE(2008,11,1),IF(Work!\$R\$3:\$R\$4293<>"",IF(Work!\$Q\$3:\$Q\$4293<DATE(2008,12,1),Work!\$C\$3:\$C\$4293,""))))
>>
>> There's nothing wrong with that formula.
>>
>> >I know there should be data
>>
>> Then there's a problem with your data. Are you sure your dates are true
>> Excel dates? What result do you get with this formula:
>>
>> =SUMPRODUCT(--(MONTH(Work!Q3:Q4293)=11),--(YEAR(Work!Q3:Q4293)=2008))
>>
>> If you get 0 that means that either your dates are not true Excel dates
>> or
>> there are no dates for Nov 2008.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "PAL" <PAL@discussions.microsoft.com> wrote in message
>> news:DF4B025D-78CB-415F-8724-31ED0271DB8E@microsoft.com...
>> > Not quite right.  I am getting the #DIV/0 error.  I know there should
>> > be
>> > data
>> > based on the original formula I used.  Which is below.  I have some
>> > many
>> > tables with multiple rows it was too cumbersone.  I hope to be able to
>> > figure
>> > out what is wrong with the formula you and T. Valko provided.
>> >
>> >
>> > =AVERAGE(IF(Work!\$Q\$3:\$Q\$4293>=DATE(2008,11,1),IF(Work!\$R\$3:\$R\$4293<>"",IF(Work!\$Q\$3:\$Q\$4293<DATE(2008,12,1),Work!\$C\$3:\$C\$4293,""))))
>> >
>> >
>> >
>> > "Mike H" wrote:
>> >
>> >> Hi,
>> >>
>> >> The average formula, ARRAY entered
>> >>
>> >> =AVERAGE(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
>> >>
>> >> and the COUNT formula., once again ARRAY entered
>> >>
>> >> =COUNT(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))
>> >>
>> >> Mike
>> >>
>> >>
>> >>
>> >> "PAL" wrote:
>> >>
>> >> > I am trying to get the average of a set of numbers if they occur in
>> >> > a
>> >> > given
>> >> > month. I get the infamous #N/A. As an array.......
>> >> >
>> >> > =AVERAGE(IF(TEXT(Work!\$Q\$3:\$Q\$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!\$R\$3:\$R\$4293<>"",Work!\$C\$2:\$C\$4293)))
>> >> >
>> >> > I would also like to count the number.  I would assume I could swap
>> >> > out
>> >> > average for count.
>>
>>
>> .
>>

```
 0
T
12/7/2009 3:42:09 AM

Similar Artilces:

Closed accounts appear in the current month report
In the current month report in "Investment Performance" section closed accounts appear in the performance chart. How do I prevent that without deleting closed accounts? Thanks, Vadym This options is not available in a monthly report (Reports -> Monthly Reports - Report for August, 2003) Unless I miss something. Thanks, Vadym "Grey" <bbb@ccc.com> wrote in message news:EYycnc0sq-nQlsyiXTWJkw@comcast.com... > Customize your report by removing unwanted accounts. Go to "Customize" and > find the tab that lists the accounts that the report is genera...

Can you improve this: Converting Year,Month,Day (all INT's) to DATETIME
I am trying to convert three INT values to a DATETTIME The following TSQL works, but there must be a better way? DECLARE @year INT = 1776, @month INT = 7, @day INT = 1 SELECT CONVERT(DATETIME, CONVERT(CHAR(4), @year)+'-'+CONVERT(CHAR(2), @month)+'-'+ CONVERT(CHAR(2), @day)) Thanks Russell Mangel Las Vegas, NV SELECT CONVERT(DATETIME, RTRIM(@year*10000 + @month*100 + @day)) http://www.eggheadcafe.com/software/aspnet/31322028/convering-year-month-an.aspx "Russell Mangel" <russell@tymer.net> wrote in message news:ufJY0FypKHA.4532@TK...

Hours Weekly To Hours Monthly Problem
I maintain a database that tracks employee hours entered into time-sheets. In essence, one column accumulates the number of hours entered daily. Another column tells me the number of hours each employee is required to enter weekly (for example, 40). I set up a couple of parameters (Start Date and End Date). I can then easily collect the hours entered by an employee for a week and compare that with his required weekly hours. An automated mail merge sends emails notifying each employee who has missing hours. My problem is this. I want to create a query that collects the entered hours fo...

Trying to compare % to date across several years
I have a simple need. I have calculated cummulative % of full year's sales for several years and want to compare the sales patterns. The chart would show each year's sales from 0% up to 100% with % as Y axis and day of year as X axis. Can't make any date formats allow such a comparison, so I have calculated a day of year by subtracting the date of each sale from the 12/31 date of the prior year, therefore each data point is a value between 0 and 100% and a "date" between 1 and 365. Seems like a routine chart, but nothing seems to make the x axis display corr...

Extracting averages
Hi, can anybody help me on this problem: I have a column with a LOT of numbers and I want to know the average of each three (in for instance A1-A3, A4-A6, A7-A9 and so on) and put these average values in a new column (say, B), but this new column should not contain any blanc fields, ie. I want the length of new column (B) to be only one-third of the length of the original column (the A-column)... Thank you /Mette Mette You need to use the OFFSET function. If you send me your email address to nicholasward@kpmg.com.au, I will send you an example spreadsheet. Cheers Nick. >-----Ori...

Converting Mortgage from Monthly to Semi-monthly
I have recently changed my mortgage from monthly payments to semi-monthly (2nd and 15th of the month). What is the best way to set that up in Money 2007, or is it not even possible? All interest is still based off on the due date (the 2nd), so in reality, the payment on the 15th of the month prior to the due date and the payment on the 2nd, which is the due date, constitute a payment in full. Should I just create a dummy account to put the withdrawl totals into on the 15th and the 2nd and then make the Mortgage payment from the dummy account? Thanks, Merg -- Today's ...

graph by month/year a count of a list of dates
I have a list of full dates (mm/dd/yyyy) that I want to graph to show how many dates in the list are within a specific mmm/yyyy. e.g., if the list include dates of 12/2/2010 and 12/15/2010, the graph will show "2" for " Dec 2010". The chart is looking at the full date and I cannot figure out how to reduce it to just a month/year level to chart. ...

Year end inventory
Help! We are entering corrected quantities on the physical inventory screen. Page 89, the final page, we were bounced out to main screen. All work lost! 3 hours. What happened? The only clue I can give is we set the system date to 12/31/04. This is what we have done in the past and it has caused no problem. It allows us to end our year cleanly. We then enter sales from 01/03/05 forward. Any ideas? Thank you Gary NEVER set your system date back when using SQL. A definate no-no. Probably the cause of the issue too I bet. SQL is date/time driven and knows when the date has been &#...

Undo fixed asset year end close
Is there any way to undo fixed asset year end close? Customer accidently goes to tools-routine-fixed asset and close year 2010. Is there any way to reopen 2010? On Wed, 03 Feb 2010 19:13:43 -0800, Teresa Wen <user@msgroups.net/> wrote: >Is there any way to undo fixed asset year end close? Customer accidently goes to tools-routine-fixed asset and close year 2010. Is there any way to reopen 2010? > >--- >frmsrcurl: http://msgroups.net/microsoft.public.access Is this a question about a Microsoft Access database? If so, you'll need to be more specific: someone might...

calculating months
hello peeps i'm trying to calculate the number of months from a list of increasing dates, b3= 21-Jan-06 b4= 03-Feb-06 and so on b16= 14-Jul-06 b17= and onwards ar blank b25= DISPLAY No. MONTHS i'll be adding more dates in cells b17 to b24 at a later date so would like it to allow for this to happen i enter the date as 14/8/06 and it gets displayed as 14-Jul-06 if that helps ? i've tryed =datevalue(b16)-datevalue(b3) and get this ############## Thanks inadvance. Hi DarkNight, Use the DATEDIF() function: =DATEDIF(B3,B16,"m") DATEDIF() is only explaind in Help in XL...

Recurring deposit on second wednesday of very month -- how to set up??
I have Microsoft Money 2000 (version 8). Is there a way to set up a recurring deposit that arrives on the second wednesday of every month?? (I see options for monthly, weekly, etc.) Thanks. Barb Hi Barb On 2K business the menu that comes up shows monthly as the first option. If you move the "slide bar" in the side "every other week" shows up. Lew "striped_moose" <striped_moose@hotmail.com> wrote in message news:28542eee.0402141312.26633ba9@posting.google.com... > I have Microsoft Money 2000 (version 8). > > Is there a way to set up a recurr...

Half Year Averaging Convention
Hi! I need help in understanding GP's depreciation calculations. My client's fiscal year is from 4/25/05 to 4/30/07. They entered an asset in 8/31/05 worth 20,000. The original life years is 9 and the depreciation method is straight line original life, half-year averaging convention. We ran depreciation until 10/23/05 (end of Period 6) and in the Projection Inquiry window, we got the ff: 8/31 - 9/25 (Part of Pd 5): 130.13 9/26 - 10/23 (Pd 6): 140.14 Pd 5 is 8/29 to 9/25. Can anyone help me understand how GP got those figures? THANKS!!! -- Marisol Mortera Is your cli...

Count dates by monthes ?
hi, i have to columns date and name (ex : Andy - 03/01/79) i would like to count all Andy's cells of every monthe (january=3,february=2 ....) How can I do it ? Make sure you have headers, then select the table and do data>pivot table and pivotchart report. Click next twice and select layout, drag names and dates into the row field and drag names into the data field as well, right click any of the cells with a date in the pivot table and select group, then group by month -- Regards, Peo Sjoblom "hasdou" <hasdou@discussions.microsoft.com> wrote in message ...

Car loan = downpayment and monthly payments in essential bidget
car purchase price \$18,000 downpayment = \$2,000 Car loan \$16,000 Essential budget Car asset account "Chevy" set up ok car loan citibank setup ok 1) what is correct category for check issued for downpayment? At present I have it as Transfer: Chevy, but on the Chevy account setttings I have to indicate purchase price as \$16,000 (not the \$18,000 it cost). Is that correct or should I indicate some other category for the downpayment? 2) Citibank loan setup ok, and each payment show up as category "Loan Payment : Chevy car loan", but I can't make this categgory appear...

Average- Expanding column length
I have a range of data from A16 to K??. In cell A15 I have a COUNTA function which returns the number of rows of data (the range is continually being added to). In F10 I would like to have an average function for the numeric values in the Range(F16:F(whatever the COUNTA value is)+15). How can I do this? Thanks Sandy One way: =AVERAGE(OFFSET(F16,,,A15,1)) In article <uLoqtwA4HHA.2108@TK2MSFTNGP02.phx.gbl>, "Sandy" <sandy_stephen@DELETEhotmail.com> wrote: > I have a range of data from A16 to K??. > In cell A15 I have a COUNTA function which returns the...

Resource Hours by Month
How can I see the number of hours allocated per day, week or month? The hours per task are easily found as are the hours by resource. However, without a weekly/monthly total it's difficult/impossible to see if the total program matches the funding/spending profile. Hi, YOu haev to modify teh timescale (Format, Timescale) You get the totals based on the minor scale. HTH -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online.be/prom-ade/Calendar.pdf "Phil" <Phil@discussions.microsoft...

Handling Transfers in Monthly Budget Report
How do I get Money to produce the Monthly Budget Report and exclude transfers? If I sell some stock in one of my IRA's and it gets deposited in the Money Market portion of my IRA, then it shows as Income in these transfers. I could live with that if it didn't then include that in the Total line for Income. Same goes for expenses. I tried the Customize...., but nothing in there that will let me exclude this. Any help? Thanks, Mark ...

can't display multiple months in Navigation pane
I don't understand why this should be so difficult - I'm trying to display multiple months on the left side of the calendar view. I can get 2, but that's it. All the buttons are minimized, as I read to do in a previous post, but this doesn't help. This stupid thing called "my calendars" is taking up 1/2 of the room, and it's something I don't need. But you can't get rid of it. Sweet Job Microsoft! Anyone know a solution? Thanks in advance Great Dane <Great Dane@discussions.microsoft.com> wrote: > I don't understand why this shou...

16-year old completes Sail Around the World in 7 months
Australian teen completes round-the-world sail May 15, 5:36 AM (ET) By KRISTEN GELINEAU SYDNEY (AP) - A 16-year-old Australian who spent seven months at sea in her pink yacht sailed across the finish line of her round-the-world journey Saturday, becoming the youngest sailor to circle the globe solo, nonstop and unassisted. Thousands of spectators erupted into cheers as Jessica Watson sailed into Sydney Harbour, the finale to an epic adventure in which she battled 40-foot (12-meter) waves, homesickness and critics who said she'd never make it home alive. "She said ...

Error while testing GP 9.0 and end-of-year update w/sp4
MicrosoftDynamicsGP-KB957740-v9-ENU.msp I've installed GP 9.0 sp4 on a test Server (snapshot of production), installer runs fine, Companies converted just fine. I get into GP Utilities just fine and system database and companies are at "the current version". However when I log onto the client on the server, directly or through GP utilities, I keep getting the following message: "A Product installed on your computer is on a different version than the database version. You will not be able to use the application until this issue is resolved. Use the GP_LoginErrors.log...

Number of months between 2 dates
I can not remove this double thread ... sorry -- john liem ...

group data according to month/year
hi, i have two columns of data. one column has date in that and the other has value in it. I like to group them according to month/year and find the subtotal of each month in one year. I like to accomplish it without using pivot table. is that possible? Mosaddeq You could add a helper column with a formula like: =text(a2,"yyyymm") and then sort your data by this column. Then apply subtotals to the range using that helper column as the key. Mosaddeq Aziz wrote: > > hi, > > i have two columns of data. one column has date in that and the other has > value in ...

Top 50% Grouped by Month
Hi, I have two fields in a query which are Month and MedTime. I want to get the top 50% of the values or top half by month. The query returns about 200 records with about 15-20 records per month. So if October 09 has 20 records, I want to return the highest ten MedTime values. If November has 15 records, I want to return the top 7-8 records. So I would want the top half values for MedTime grouped by month. Can someone help? Thanks, You'll need a unique key column in the table to do this. If you don't already have one add an autonumber column. Then use a subquery...

Month Display
Hello In Calendar/Month Display Sat and Sun is "half size". Is it possible to display all the days of the week the same size? Regards, Ron View, Current View, Customize Current View. Other Settings. Uncheck the box marked "compress weekend days" -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza http://www.cardozasolutions.com "Ron Wood" <ronwood50@ti...

Sumproduct entire month from mmddyyy?
In A1 I have the date: mmddyyy. I need a sumproduct formula to return the sales for a sales person for the entire month and year for the date in A1 even though just one day is entered into A1. Unfortunately on this report, I can only reference the mmddyyy date in A1. A2:A1000 has dates mmddyyy for each sale made B2:B1000 has salesperson that made the sale C2:C1000 has sales amount of each sale Did you check your earlier post for the answer -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "wx4usa" <wx4usa@gmail.com> wrote in message ...