Hello

I have a conundrum which is proving very difficult.

I have a month set of data which has each day of the month as a header
and then a series of metrics under each day. What I need to do is sum 7
days worth of historic data from and including today.

in laymans

" if the column header = today then sum inc today the previous 7 days
from this row. "

As you can see i am at a loss. Have tried count, sumproduct etc but
cannot pull it together.

Any help as always 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

One play ..

Assuming in Sheet1, you have real dates in B1:AF1
from say: 1-Aug-2005 to 31-Aug-2005
with the numbers (your metrics) below in B2:AF2, B3:AF3 etc

In Sheet2 (say), we could put in A2:
=SUM(OFFSET(Sheet1!A2,,MATCH(TODAY(),Sheet1!$1:$1, 0)-1,,-7))
and copy A2 down to return the desired results from the corresponding rows
in Sheet1
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" wrote in
message ...

Hello

I have a conundrum which is proving very difficult.

I have a month set of data which has each day of the month as a header
and then a series of metrics under each day. What I need to do is sum 7
days worth of historic data from and including today.

in laymans

" if the column header = today then sum inc today the previous 7 days
from this row. "

As you can see i am at a loss. Have tried count, sumproduct etc but
cannot pull it together.

Any help as always 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

And if there's the possibility that the number of historic data days in
Sheet1's B1:AF1 is < 7 days from "today", and you want for such situations
to just sum the available historicals up till and inclusive of "today"
(notwithstanding it's less than the specified 7 days), we could put instead
in Sheet2's A2:

=IF(MATCH(TODAY(),Sheet1!$1:$1,0)-1<7,SUM(OFFSET(Sheet1!A2,,MATCH(TODAY(),Sh
eet1!$1:$1,0)-1,,-(MATCH(TODAY(),Sheet1!$1:$1,0)-1))),SUM(OFFSET(Sheet1!A2,,
MATCH(TODAY(),Sheet1!$1:$1,0)-1,,-7)))

and copy down as before ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Excellent.

Thanks for that Max.

Regards

Kristan


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

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

Excellent.

Thanks for that Max.

Regards

Kristan

Max, what do you see that I don't?

I would think this should be sufficient:

=IF(COUNT(2:2)<7,SUM(A2:AE2),.................

Biff

"Max" wrote in message
...
And if there's the possibility that the number of historic data days in
Sheet1's B1:AF1 is < 7 days from "today", and you want for such situations
to just sum the available historicals up till and inclusive of "today"
(notwithstanding it's less than the specified 7 days), we could put
instead
in Sheet2's A2:

=IF(MATCH(TODAY(),Sheet1!$1:$1,0)-1<7,SUM(OFFSET(Sheet1!A2,,MATCH(TODAY(),Sh
eet1!$1:$1,0)-1,,-(MATCH(TODAY(),Sheet1!$1:$1,0)-1))),SUM(OFFSET(Sheet1!A2,,
MATCH(TODAY(),Sheet1!$1:$1,0)-1,,-7)))

and copy down as before ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"Biff" wrote
Max, what do you see that I don't?
I would think this should be sufficient:
=IF(COUNT(2:2)<7,SUM(A2:AE2),.................

Trouble is <g, I had assumed that there could be previous month's metrics
still residing within the data rows (to the right of the current date's
col), either missed* out in the monthly purging exercise despite the refresh
of the current month's dates in the header row
*or yet to be purged because the data entry method followed is to
progressively overwrite.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Works well thanks Biff and Max.

I have one other question relating to this... I am trying to
incorporate a sumproduct function into the formula and cannot quite get
it to behave how I would like.

=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),$1:$1,
0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,
0)-1,,-7)))/T25))-1,,-(MATCH(TODAY(),$1:$1,0)-1))),SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),$1:$1,
0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1, 0)-1,,-7)))/T25)


Again, if you get a minute could you take a look at the attached
formula and poitn out the error in my ways.

Thanks again.
Kristan


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

Hi!

So, what exactly do want to sum? (where is it?)

What if today is August 6? There aren't 7 days worth of data to sum!

One way.....

Assume row 1, A1:AE1, are the date headers in the format 8/1/2005

To sum the last 7 entries in row 2 from todays date (inclusive):

=IF(COUNT(2:2)<7,"Insufficient
Data",SUM(OFFSET(A2,,MATCH(TODAY(),A1:H1,0)-1,,-7)))

Biff

"Kstalker" wrote in
message ...

Hello

I have a conundrum which is proving very difficult.

I have a month set of data which has each day of the month as a header
and then a series of metrics under each day. What I need to do is sum 7
days worth of historic data from and including today.

in laymans

" if the column header = today then sum inc today the previous 7 days
from this row. "

As you can see i am at a loss. Have tried count, sumproduct etc but
cannot pull it together.

Any help as always 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

Ooops!

One of the cell references is wrong. Should be:

=IF(COUNT(2:2)<7,"Insufficient
Data",SUM(OFFSET(A2,,MATCH(TODAY(),A1:AE1,0)-1,,-7)))

Biff

"Biff" wrote in message
...
Hi!

So, what exactly do want to sum? (where is it?)

What if today is August 6? There aren't 7 days worth of data to sum!

One way.....

Assume row 1, A1:AE1, are the date headers in the format 8/1/2005

To sum the last 7 entries in row 2 from todays date (inclusive):

=IF(COUNT(2:2)<7,"Insufficient
Data",SUM(OFFSET(A2,,MATCH(TODAY(),A1:H1,0)-1,,-7)))

Biff

"Kstalker" wrote
in message ...

Hello

I have a conundrum which is proving very difficult.

I have a month set of data which has each day of the month as a header
and then a series of metrics under each day. What I need to do is sum 7
days worth of historic data from and including today.

in laymans

" if the column header = today then sum inc today the previous 7 days
from this row. "

As you can see i am at a loss. Have tried count, sumproduct etc but
cannot pull it together.

Any help as always 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

Cheers Max / Biff.

Biff as you pointed out there is the possibility of less than a weeks
worth of data if it is at the begining of the month.

If this occours i need to sum the week to dat figures even though there
are less than 7 entries.

If you have a minute, how can I acheive this?

Thanks

Kristan


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

If this occours i need to sum the week to dat figures even though there
are less than 7 entries.

That's what I thought you might want <g.
One way .. see my follow up response in the other branch
(think you just missed it !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi!

Try this:

=IF(COUNT(2:2)<7,SUM(A2:AE2),SUM(OFFSET(A2,,MATCH( TODAY(),A1:AE1,0)-1,,-7)))

Biff

"Kstalker" wrote in
message ...

Cheers Max / Biff.

Biff as you pointed out there is the possibility of less than a weeks
worth of data if it is at the begining of the month.

If this occours i need to sum the week to dat figures even though there
are less than 7 entries.

If you have a minute, how can I acheive this?

Thanks

Kristan


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