More Beginners help!

[juliekerin]

Hi there,

As I mentioned on a previous post I'm quite new to excel macros and s
far have only created them using excel itself and the recordin
function in it. I now need a macro which I dont think I can do usin
excel and might need to write in VB and would be grateful for som
help!

I have a file which has a 'to' and 'from' date on it (ie. fro
02/09/04 to 05/09/04), and also has a list of dates which each have
corresponding charge.
So what I need to do is for each date from the 'from' date up until th
'to' date I need to pull the corresponding charge and then average th
charges.
(I.e. in the above dates examples I would need to get the charge fo
02/09, 03/09,04/09 and then average them together).
The dates can be different as can the length of time between th
dates.

Can anyone help in how to do this?I think I would need a while loop o
something like that but not sure.

Thanks!
Juli

 

[Jack Schitt]

It sounds to me as though you should be able to achieve the answer without
relying on any Macro or VBA.

Suppose the "From" date is contained in a single-cell named range called
"From"
and the "To" date is contained in a single-cell named range called "To"
and the actual dates are contained in a single column named range called
"Date"
and the charges are contained in a single column named range called "Charge"
(same rows as "Date")

then something like

=SUM(Charge*(Date>=From)*(Date<=To))/SUM((Date>=From)*(Date<=To))
Array entered (ie Control/Shift/Enter when entering the formula)

I expect that someone else will provide a solution that uses SUMPRODUCT()
without array entering, and it may be possible to make it more elegant using
AVERAGE() somewhere in an array formula, but at least the above seems to
work (barring error values in any of the cells etc etc).

 

[Tom Ogilvy]

For average you would just array enter

=Average(Charge*(Date>=From)*(Date<=To))

for sumproduct you would just change your sums to sumproduct. (this
eliminates the need to array enter the formula, but it is an array formula
nonetheless - it is evaluated as an array formula by excel for all practical
purposes).

--
Regards,
Tom Ogilvy

It sounds to me as though you should be able to achieve the answer without
relying on any Macro or VBA.

Suppose the "From" date is contained in a single-cell named range called
"From"
and the "To" date is contained in a single-cell named range called "To"
and the actual dates are contained in a single column named range called
"Date"
and the charges are contained in a single column named range called "Charge"
(same rows as "Date")

then something like

=SUM(Charge*(Date>=From)*(Date<=To))/SUM((Date>=From)*(Date<=To))
Array entered (ie Control/Shift/Enter when entering the formula)

I expect that someone else will provide a solution that uses SUMPRODUCT()
without array entering, and it may be possible to make it more elegant using
AVERAGE() somewhere in an array formula, but at least the above seems to
work (barring error values in any of the cells etc etc).

 

[Jack Schitt]

Thanks Tom. I had a halfhearted attempt at sumproduct without immediate
success, but now have managed it by numberizing the booleans:

=SUMPRODUCT(Charge*N((Date>=From))*N((Date<=To)))/SUMPRODUCT(N((Date>=From))
*N((Date<=To)))

As to your AVERAGE() solution it comes up with a different value. To test:
From.value = 01 Mar 2003
To.value = 01 Aug 2003
Date.value = {01 Dec 2002,01 Jan 2003, 01 Feb 2003, .....,01 Sep 2003}
Charge.value = {1,2,3,...,10}

Formula should return the average of {4,5,6,7,8,9} = 6.5
The SUM() array formula and SUMPRODUCT() formula both return this value
The AVERAGE() array formula returns 3.9

For average you would just array enter

=Average(Charge*(Date>=From)*(Date<=To))

for sumproduct you would just change your sums to sumproduct. (this
eliminates the need to array enter the formula, but it is an array formula
nonetheless - it is evaluated as an array formula by excel for all practical
purposes).

 

[Jack Schitt]

Heh, forget the surplus parentheses in the SUMPRODUCT() version, of course

 

[Tom Ogilvy]

Your right - my mistake on the average

=Average(If((Date>=From)*(Date<=To),Charge))

Array Entered