Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > function tweak?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 155
Default function tweak?
Hi there,

I use the following SumProduct formula to search for all values in a
specified date range, in this case, for all values in january, then sum those
values which fall into that date range:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)

Question: I need to be able to adapt this formula for averaging a set of
values for a specified month range.

can anyone help me on this?
--
Carlee
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 857
Default function tweak?
maybe like this:

I modified your original formula like this to get just the count:

SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01"))


And then, divided the sum with the count:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master
Log'!CB3:CB400)/SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01"))





--
Hope that helps.

Vergel Adriano


"Carlee" wrote:

Hi there,

I use the following SumProduct formula to search for all values in a
specified date range, in this case, for all values in january, then sum those
values which fall into that date range:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)

Question: I need to be able to adapt this formula for averaging a set of
values for a specified month range.

can anyone help me on this?
--
Carlee
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default function tweak?
One way:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)/
SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")))



"Carlee" wrote:

Hi there,

I use the following SumProduct formula to search for all values in a
specified date range, in this case, for all values in january, then sum those
values which fall into that date range:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)

Question: I need to be able to adapt this formula for averaging a set of
values for a specified month range.

can anyone help me on this?
--
Carlee
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 857
Default function tweak?
The formulas were missing one closing parenthesis. The formula to get the
count should be:

SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")))


The formula to get the average should be:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master
Log'!CB3:CB400)/SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")))



--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

maybe like this:

I modified your original formula like this to get just the count:

SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01"))


And then, divided the sum with the count:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master
Log'!CB3:CB400)/SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01"))





--
Hope that helps.

Vergel Adriano


"Carlee" wrote:

Hi there,

I use the following SumProduct formula to search for all values in a
specified date range, in this case, for all values in january, then sum those
values which fall into that date range:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)

Question: I need to be able to adapt this formula for averaging a set of
values for a specified month range.

can anyone help me on this?
--
Carlee
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,651
Default function tweak?
On Mon, 16 Apr 2007 17:02:01 -0700, Carlee
wrote:

Hi there,

I use the following SumProduct formula to search for all values in a
specified date range, in this case, for all values in january, then sum those
values which fall into that date range:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)

Question: I need to be able to adapt this formula for averaging a set of
values for a specified month range.

can anyone help me on this?

How about something like:


=(SUMIF(DtRng,"="&DATE(2007,1,1),ValRng)-
SUMIF(DtRng,""&DATE(2007,2,0),ValRng))/
(COUNTIF(DtRng,"="&DATE(2007,1,1))-
COUNTIF(DtRng,""&DATE(2007,2,0)))

where

DtRng = Log'!B3:B400
ValRng = 'Daily Reading Master Log'!CB3:CB400)


In the above formula, I used the 0th day of the month following instead of the
last day of the current month, as it's a bit simpler to compute -- you don't
have to know how many days are in the current month.


--ron
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 155
Default function tweak?
This worked brilliantly. Many thanks to everyone...you all rock!
--
Carlee


"Barb Reinhardt" wrote:

One way:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)/
SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")))



"Carlee" wrote:

Hi there,

I use the following SumProduct formula to search for all values in a
specified date range, in this case, for all values in january, then sum those
values which fall into that date range:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)

Question: I need to be able to adapt this formula for averaging a set of
values for a specified month range.

can anyone help me on this?
--
Carlee
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need someone to help tweak a code JB Excel Discussion (Misc queries) 13 January 17th 08 03:04 PM
VBA File Search Function tweak Nate[_3_] Excel Discussion (Misc queries) 1 December 7th 07 06:12 PM
SUMPRODUCT - Tweak Sam via OfficeKB.com Excel Worksheet Functions 2 September 6th 07 04:12 PM
Array Help Tweak Luke Excel Worksheet Functions 13 November 22nd 06 10:29 AM
Code Tweak bodhisatvaofboogie Excel Programming 1 July 21st 06 04:37 PM


All times are GMT +1. The time now is 12:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement