ugh!

<g

Biff

"Max" wrote in message
...
Believe essentially that you have dates running along from B1 across (B1,
C1, D1, ...) which do not necessarily start from the 1st of the month in
B1

Try this revision:

=IF(S17=0,0,IF(AND(DAY(TODAY())=1,DAY(TODAY())<=6 ),SUMPRODUCT((OFFSET(A9,,M
ATCH(TODAY(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),M
ONTH(TODAY()),1),$1:$1,0)+1))),(OFFSET(A17,,MATCH( TODAY(),$1:$1,0)-1,,-(MATC
H(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$1:$1,0 )+1))))
/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY(),$1:$1,0)-1,,-7)),(OFFSET(A17,,MATC
H(TODAY(),$1:$1,0)-1,,-7)))/S17))

The changes made are to the 2nd IF whe

IF(AND(DAY(TODAY())=1,DAY(TODAY())<=6)

replaces the previous :

IF(MATCH(TODAY(),$1:$1,0)-1<7

and to the width params of OFFSET within the 1st SUMPRODUCT, whe

-(MATCH(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$1:$1,0 )
+1

replaces the previous:

-(MATCH(TODAY(),$1:$1,0)-1

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" wrote
in
message ...

Alternatively.... Is it possible to add criteria to the equation below.

The reason I ask is I am trying to stop the equation below (which works
perfectly thanks Max) from going past the 1st of the month should i be
trying to add a weekly summary from the 6th day of the month back to
the 1st.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 .......etc
(--------------)


"=IF(S17=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A9,,MATCH(TODA
Y(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A17,,MATCH(TODAY(),$1:
$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TOD
AY(),$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))"

All help greatly appreciated


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
View this thread:
http://www.excelforum.com/showthread...hreadid=395995