Conditional Average Possible? - RESOLVED

[mapakunk]

I would like to average a range of numbers if the dates are between 4 weeks ago and today? any ideas.
The dates are in column d12:d100 and the values are in column p12:p100
any help would be greatly appreciated. Also if I can do this without VBA I would appreciate it beacuse I do not know VBA!

As far as I know there is no averageif function.

_________________
mapakunk

[ This Message was edited by: mapakunk on 2002-05-16 09:50 ]

[Steve Hartman]

I would like to average a range of numbers if the dates are between 4 weeks ago and today? any ideas.
The dates are in column d12:d100 and the values are in column p12:p100

=AVERAGE(IF(D12:D100>TODAY()-28,P12:P100))


This is an array formula and must be entered into the cell by pressing Ctrl-Shift-Enter at the same time.






[ This Message was edited by: Steve Hartman on 2002-05-08 11:43 ]

[Aladin Akyurek]

On 2002-05-08 08:32, mapakunk wrote:
I would like to average a range of numbers if the dates are between 4 weeks ago and today? any ideas.
The dates are in column d12:d100 and the values are in column p12:p100
any help would be greatly appreciated. Also if I can do this without VBA I would appreciate it beacuse I do not know VBA!

As far as I know there is no averageif function.

=SUMPRODUCT((D12:D100>=A1-28)*(D12:D100<=A1),P12:P100)/MAX(1,SUMPRODUCT((D12:D100>=A1-28)*(D12:D100<=A1)))

where A1 holds the formula: =TODAY().

Aladin

[mapakunk]

This seems to pick the right columns, however the values this would be are about 9000 and the average that is returned is 1384.80?? I copied your formula, pasted it, deleted the space, and rpessed Ctrl+shft+enter at the end to make it an array formula maybe there is something I should be telling you that I'm not??

Thanks for the SPEEDY reply!!!!

[mapakunk]

Aladin,

That was the ticket! Thank you very much!
This is one to scribble down in the back of my excel2000 bible!!!!

:>

[Steve Hartman]

On 2002-05-08 08:49, mapakunk wrote:
This seems to pick the right columns, however the values this would be are about 9000 and the average that is returned is 1384.80?? I copied your formula, pasted it, deleted the space, and rpessed Ctrl+shft+enter at the end to make it an array formula maybe there is something I should be telling you that I'm not??

Nope, something I should have told you. Thanks to the edit function, the formula you tried was not the right one. I realised after I posted it that the "less than" should have been a "greater than" and I edited the post. Unfortunately it was after you had seen the original.

[IML]

I think you could also go with

=SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/COUNTIF(D12:D100,">="&TODAY()-28)

if you notice any performance issues.

good luck

edited for the cool denominator trick
=SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/MAX(COUNTIF(D12:D100,">="&TODAY()-28),1)

[ This Message was edited by: IML on 2002-05-08 09:43 ]

[Aladin Akyurek]

On 2002-05-08 09:41, IML wrote:
I think you could also go with

=SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/COUNTIF(D12:D100,">="&TODAY()-28)

if you notice any performance issues.

good luck

edited for the cool denominator trick
=SUMIF(D12:D100,">="&TODAY()-28,P12:P1002)/MAX(COUNTIF(D12:D100,">="&TODAY()-28),1)

[ This Message was edited by: IML on 2002-05-08 09:43 ]

Makapunk,

I'd suggest using the above SUMIF version. & it's not (just) a performance issue as Ian modestly puts. I just overlooked the fact that today is not a fixed date, so there is no need to take that as a second condition which necessitated SUMPRODUCT (you might still keep it on the back of Excel bible, though).

Regards,

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-08 10:03 ]

[mapakunk]

I guess i could use the sumproduct formula, keeping today() in A1, and then change that date if I wanted to see an average for 4 weeks from any given date.

Thanks for the help, this BB is VERY Helpful.
especially because it is free. Maybe someday I'll be able to return the favor...

Tanks everybody,

mapakunk