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
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
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
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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,,MA
TCH(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
>
Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)
=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Excellent.
It works a treat.
I have another question relating to this entire process.
The formula below is being used to sumproduct any data where the criteria "stop" is met. However I can only sum this data (sumif) which is not accurate as if more than one coumn contains the criteria it is summed . Is there a way that I can set the criteria as in using the sumif function but still produce the sumproduct results??
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30))
Thanks again
Thanks
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(TODAY(),$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(TODAY(),$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))"
All help greatly appreciated
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 where:
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, where:
-(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
Thanks again Max.
Just about there..... My date range starts at the 1st and then through to however many days of the month. However I need to offer a week to date summary of the metrics. If the week to date happens to fall on the 6th, 5th, 4th, 3rd , 2nd or 1st then the formula will run over into the columns to the left of the data. ( i am running left to right 1st ==> 31st.
So what I need to do is stop the formula when there is no date in the reference date. Unfortunately i have various other calcs running in these spaces that are numeric which will throw the weighted averages.......Is this possible?
Regards
Kristan
> .......... Is this possible?
Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Mail enroute.
Unsure if it is possible.
Thanks
Kristan
ugh!
<g>
Biff
"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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 where:
>
> 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, where:
>
> -(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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>
"Kstalker" wrote:
> Mail enroute.
> Unsure if it is possible.
Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.
In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
> The formula below is being used to sumproduct any data where the
> criteria "stop" is met. However I can only sum this data (sumif) which
> is not accurate as if more than one coumn contains the criteria it is
> summed . Is there a way that I can set the criteria as in using the
> sumif function but still produce the sumproduct results??
>
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))
Think I might have lost my way somewhere here ..
As it stands, your posted formula is equivalent to:
=IF(S30=0,0,SUMIF($A$2:$Q$2,"stop",A19:Q19)*SUMIF($A$2:$Q$2,"stop",A30:Q30)/
S30)
(i.e. with the "*" replacing the SUMPRODUCT( ..))
So, supposing you have:
In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
(All other cells within the posted formula are assumed blank)
Your posted formula will return: 4500 (nothing wrong here), viz.:
SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500
What is your expected result ?
If it's another value, pl explain how the expected result is computed
And if we clear say, cell C2, so that it becomes
In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
"Biff" wrote
> ugh!
> <g>
Thanks ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
Kristan,
Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.
Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month
In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).
*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Brilliant
Max.
Works perfectly. An error on my behalf prevented the reply you posted a few back from working so appologies for using up more of your time and not using the formula correctly.
Yet again there has been a wealth of useful information on and provided through this site, I regularly recommend it to others.
Thanks again for your input Max.
Regards
Kristan
You're welcome, Kristan !
Glad it finally worked out ok for you ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
No problem with all the attached solutions to this thread but have an enhancement that needs to be made and was wondering how to acheive this.
Instead of running from todays date I need to run from yesterdays date. If anybody has the patience could you advise on how i reconfigure the today component of:
=IF(S17=0,0,IF(AND(DAY(TODAY())>=1,DAY(TODAY())<=6),SUMPRODUCT((OFFSET(A9,,MATCH(TODAY(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$1:$1,0)+1))),(OFFSET(A17,,MATCH( TODAY(),$1:$1,0)-1,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
to search on yesterdays date instead?
I am certain it is simple but cannot crack it.
Thanks in advance
Kristan
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> 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.
I though it was as simple as that but I keep coming up with a 'false' result using the formula below.....
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODAY()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1),19:19,0)+1))))
Absoultely stumped.....
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
--
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
--
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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,,MA
TCH(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
>
Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)
=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
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 where:
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, where:
-(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> .......... Is this possible?
Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
ugh!
<g>
Biff
"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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 where:
>
> 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, where:
>
> -(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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>
"Kstalker" wrote:
> Mail enroute.
> Unsure if it is possible.
Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.
In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
> The formula below is being used to sumproduct any data where the
> criteria "stop" is met. However I can only sum this data (sumif) which
> is not accurate as if more than one coumn contains the criteria it is
> summed . Is there a way that I can set the criteria as in using the
> sumif function but still produce the sumproduct results??
>
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))
Think I might have lost my way somewhere here ..
As it stands, your posted formula is equivalent to:
=IF(S30=0,0,SUMIF($A$2:$Q$2,"stop",A19:Q19)*SUMIF($A$2:$Q$2,"stop",A30:Q30)/
S30)
(i.e. with the "*" replacing the SUMPRODUCT( ..))
So, supposing you have:
In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
(All other cells within the posted formula are assumed blank)
Your posted formula will return: 4500 (nothing wrong here), viz.:
SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500
What is your expected result ?
If it's another value, pl explain how the expected result is computed
And if we clear say, cell C2, so that it becomes
In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
"Biff" wrote
> ugh!
> <g>
Thanks ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
Kristan,
Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.
Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month
In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).
*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
You're welcome, Kristan !
Glad it finally worked out ok for you ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
--
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
--
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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,,MA
TCH(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
>
Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)
=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
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 where:
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, where:
-(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> .......... Is this possible?
Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
ugh!
<g>
Biff
"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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 where:
>
> 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, where:
>
> -(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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>
"Kstalker" wrote:
> Mail enroute.
> Unsure if it is possible.
Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.
In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
> The formula below is being used to sumproduct any data where the
> criteria "stop" is met. However I can only sum this data (sumif) which
> is not accurate as if more than one coumn contains the criteria it is
> summed . Is there a way that I can set the criteria as in using the
> sumif function but still produce the sumproduct results??
>
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))
Think I might have lost my way somewhere here ..
As it stands, your posted formula is equivalent to:
=IF(S30=0,0,SUMIF($A$2:$Q$2,"stop",A19:Q19)*SUMIF($A$2:$Q$2,"stop",A30:Q30)/
S30)
(i.e. with the "*" replacing the SUMPRODUCT( ..))
So, supposing you have:
In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
(All other cells within the posted formula are assumed blank)
Your posted formula will return: 4500 (nothing wrong here), viz.:
SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500
What is your expected result ?
If it's another value, pl explain how the expected result is computed
And if we clear say, cell C2, so that it becomes
In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
"Biff" wrote
> ugh!
> <g>
Thanks ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
Kristan,
Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.
Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month
In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).
*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
You're welcome, Kristan !
Glad it finally worked out ok for you ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
--
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
--
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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,,MA
TCH(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
>
Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)
=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
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 where:
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, where:
-(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> .......... Is this possible?
Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
ugh!
<g>
Biff
"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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 where:
>
> 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, where:
>
> -(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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>
"Kstalker" wrote:
> Mail enroute.
> Unsure if it is possible.
Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.
In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
> The formula below is being used to sumproduct any data where the
> criteria "stop" is met. However I can only sum this data (sumif) which
> is not accurate as if more than one coumn contains the criteria it is
> summed . Is there a way that I can set the criteria as in using the
> sumif function but still produce the sumproduct results??
>
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))
Think I might have lost my way somewhere here ..
As it stands, your posted formula is equivalent to:
=IF(S30=0,0,SUMIF($A$2:$Q$2,"stop",A19:Q19)*SUMIF($A$2:$Q$2,"stop",A30:Q30)/
S30)
(i.e. with the "*" replacing the SUMPRODUCT( ..))
So, supposing you have:
In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
(All other cells within the posted formula are assumed blank)
Your posted formula will return: 4500 (nothing wrong here), viz.:
SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500
What is your expected result ?
If it's another value, pl explain how the expected result is computed
And if we clear say, cell C2, so that it becomes
In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
"Biff" wrote
> ugh!
> <g>
Thanks ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
Kristan,
Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.
Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month
In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).
*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
You're welcome, Kristan !
Glad it finally worked out ok for you ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
--
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
--
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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,,MA
TCH(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
>
Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)
=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
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 where:
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, where:
-(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> .......... Is this possible?
Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
ugh!
<g>
Biff
"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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 where:
>
> 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, where:
>
> -(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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>
"Kstalker" wrote:
> Mail enroute.
> Unsure if it is possible.
Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.
In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
> The formula below is being used to sumproduct any data where the
> criteria "stop" is met. However I can only sum this data (sumif) which
> is not accurate as if more than one coumn contains the criteria it is
> summed . Is there a way that I can set the criteria as in using the
> sumif function but still produce the sumproduct results??
>
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))
Think I might have lost my way somewhere here ..
As it stands, your posted formula is equivalent to:
=IF(S30=0,0,SUMIF($A$2:$Q$2,"stop",A19:Q19)*SUMIF($A$2:$Q$2,"stop",A30:Q30)/
S30)
(i.e. with the "*" replacing the SUMPRODUCT( ..))
So, supposing you have:
In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
(All other cells within the posted formula are assumed blank)
Your posted formula will return: 4500 (nothing wrong here), viz.:
SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500
What is your expected result ?
If it's another value, pl explain how the expected result is computed
And if we clear say, cell C2, so that it becomes
In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
"Biff" wrote
> ugh!
> <g>
Thanks ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
Kristan,
Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.
Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month
In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).
*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
You're welcome, Kristan !
Glad it finally worked out ok for you ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
--
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
--
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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,,MA
TCH(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
>
Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)
=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
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 where:
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, where:
-(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> .......... Is this possible?
Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
ugh!
<g>
Biff
"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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 where:
>
> 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, where:
>
> -(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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>
"Kstalker" wrote:
> Mail enroute.
> Unsure if it is possible.
Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.
In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
> The formula below is being used to sumproduct any data where the
> criteria "stop" is met. However I can only sum this data (sumif) which
> is not accurate as if more than one coumn contains the criteria it is
> summed . Is there a way that I can set the criteria as in using the
> sumif function but still produce the sumproduct results??
>
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))
Think I might have lost my way somewhere here ..
As it stands, your posted formula is equivalent to:
=IF(S30=0,0,SUMIF($A$2:$Q$2,"stop",A19:Q19)*SUMIF($A$2:$Q$2,"stop",A30:Q30)/
S30)
(i.e. with the "*" replacing the SUMPRODUCT( ..))
So, supposing you have:
In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
(All other cells within the posted formula are assumed blank)
Your posted formula will return: 4500 (nothing wrong here), viz.:
SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500
What is your expected result ?
If it's another value, pl explain how the expected result is computed
And if we clear say, cell C2, so that it becomes
In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
"Biff" wrote
> ugh!
> <g>
Thanks ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
Kristan,
Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.
Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month
In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).
*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
You're welcome, Kristan !
Glad it finally worked out ok for you ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
--
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
--
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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,,MA
TCH(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
>
Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)
=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
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 where:
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, where:
-(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> .......... Is this possible?
Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
ugh!
<g>
Biff
"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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 where:
>
> 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, where:
>
> -(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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>
"Kstalker" wrote:
> Mail enroute.
> Unsure if it is possible.
Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.
In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
> The formula below is being used to sumproduct any data where the
> criteria "stop" is met. However I can only sum this data (sumif) which
> is not accurate as if more than one coumn contains the criteria it is
> summed . Is there a way that I can set the criteria as in using the
> sumif function but still produce the sumproduct results??
>
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))
Think I might have lost my way somewhere here ..
As it stands, your posted formula is equivalent to:
=IF(S30=0,0,SUMIF($A$2:$Q$2,"stop",A19:Q19)*SUMIF($A$2:$Q$2,"stop",A30:Q30)/
S30)
(i.e. with the "*" replacing the SUMPRODUCT( ..))
So, supposing you have:
In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
(All other cells within the posted formula are assumed blank)
Your posted formula will return: 4500 (nothing wrong here), viz.:
SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500
What is your expected result ?
If it's another value, pl explain how the expected result is computed
And if we clear say, cell C2, so that it becomes
In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
"Biff" wrote
> ugh!
> <g>
Thanks ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
Kristan,
Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.
Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month
In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).
*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
You're welcome, Kristan !
Glad it finally worked out ok for you ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
--
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
--
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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,,MA
TCH(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
>
Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)
=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
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 where:
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, where:
-(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> .......... Is this possible?
Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
ugh!
<g>
Biff
"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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 where:
>
> 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, where:
>
> -(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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>
"Kstalker" wrote:
> Mail enroute.
> Unsure if it is possible.
Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.
In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
> The formula below is being used to sumproduct any data where the
> criteria "stop" is met. However I can only sum this data (sumif) which
> is not accurate as if more than one coumn contains the criteria it is
> summed . Is there a way that I can set the criteria as in using the
> sumif function but still produce the sumproduct results??
>
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))
Think I might have lost my way somewhere here ..
As it stands, your posted formula is equivalent to:
=IF(S30=0,0,SUMIF($A$2:$Q$2,"stop",A19:Q19)*SUMIF($A$2:$Q$2,"stop",A30:Q30)/
S30)
(i.e. with the "*" replacing the SUMPRODUCT( ..))
So, supposing you have:
In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
(All other cells within the posted formula are assumed blank)
Your posted formula will return: 4500 (nothing wrong here), viz.:
SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500
What is your expected result ?
If it's another value, pl explain how the expected result is computed
And if we clear say, cell C2, so that it becomes
In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
"Biff" wrote
> ugh!
> <g>
Thanks ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
Kristan,
Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.
Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month
In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).
*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
You're welcome, Kristan !
Glad it finally worked out ok for you ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
--
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
--
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY()
,1:1,0)-1,,-(MATCH(TODAY(),1:1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,
-(MATCH(TODAY(),1:1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),1:1,0)
-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),1:1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> 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,,MA
TCH(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
>
Try this instead
(replaced: "1:1" with "$1:$1", to sync with your posted formula)
=IF(T25=0,0,IF(MATCH(TODAY(),$1:$1,0)-1<7,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY
(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))),(OFFSET(A25,,MATCH(TODAY(),$1:$
1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-1))))/T25,SUMPRODUCT((OFFSET(A3,,MATCH(TODA
Y(),$1:$1,0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/T25))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
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 where:
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, where:
-(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
> .......... Is this possible?
Could you send over a copy of your file?
High-time for me to sync-in exactly what's happening over there <g>
Email to: demechanik <at> yahoo <dot> com
Post a response here to alert me when you send ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
ugh!
<g>
Biff
"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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 where:
>
> 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, where:
>
> -(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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>
"Kstalker" wrote:
> Mail enroute.
> Unsure if it is possible.
Thanks for alert. I can only access my yahoo acc in about 10 hours time, so
hang in there awhile. I will post back the findings either way <g>.
In the interim ... FWIW I had actually prepared a response (below) to your
earlier post yesterday on the new issue raised before your 2nd post came
through. In case you would like to see it through:
> The formula below is being used to sumproduct any data where the
> criteria "stop" is met. However I can only sum this data (sumif) which
> is not accurate as if more than one coumn contains the criteria it is
> summed . Is there a way that I can set the criteria as in using the
> sumif function but still produce the sumproduct results??
>
=IF(S30=0,0,SUMPRODUCT(SUMIF($A$2:$Q$2,"stop",A19:Q19),SUMIF($A$2:$Q$2,"stop
",A30:Q30)/S30))
Think I might have lost my way somewhere here ..
As it stands, your posted formula is equivalent to:
=IF(S30=0,0,SUMIF($A$2:$Q$2,"stop",A19:Q19)*SUMIF($A$2:$Q$2,"stop",A30:Q30)/
S30)
(i.e. with the "*" replacing the SUMPRODUCT( ..))
So, supposing you have:
In A2:C2: stop stop stop
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
(All other cells within the posted formula are assumed blank)
Your posted formula will return: 4500 (nothing wrong here), viz.:
SUMIF($A$2:$Q$2,"stop",A19:Q19) returns: 30
SUMIF($A$2:$Q$2,"stop",A30:Q30)/S30 returns: 150
and then SUMPRODUCT(30,150) will return: 30 x 150 = 4500
What is your expected result ?
If it's another value, pl explain how the expected result is computed
And if we clear say, cell C2, so that it becomes
In A2:C2: stop stop <blank>
In A19:C19: 10 10 10
In A30:C30: 100 100 100
In S30: 2
Your posted formula will return: 2000 (again, nothing wrong here)
What is your expected result ?
If it's another value, pl explain how the expected result is computed
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
"Biff" wrote
> ugh!
> <g>
Thanks ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---
Kristan,
Ok, I've had a few really close looks and re-tested it
several times, but I couldn't find anything wrong with
the latest formula which was suggested. The formula
doesn't pick up what's to the left of the 1st day of
the month.
Please refer to the attached file*, where I've
simulated the test in the sheet: Begining month
In K14 is the same formula as in K11
but with "TODAY()" replaced by: "DATE(2005,8,4)" to
simulate as if "today" is 4th Aug 2005. You'll see
that the correct result is returned in K14 (same
answer as your K9).
*File: Kristan2a_Example.xls
http://savefile.com/files/8213621
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
You're welcome, Kristan !
Glad it finally worked out ok for you ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
> ... to search on yesterdays date instead?
Think we just need to do an Edit > Replace on the
cell with the posted working formula
Find what: TODAY()
Replace with: TODAY()-1
which yields:
=IF(S17=0,0,IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),SUMPRODUCT((OFFSET(A
9,,MATCH(TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TO
DAY()-1),MONTH(TODAY()-1),1),$1:$1,0 )+1))),(OFFSET(A17,,MATCH(
TODAY()-1,$1:$1,0)-1,,-(MATCH(TODAY()-1,$1:$1,0)-MATCH(DATE(YEAR(TODAY()-1),
MONTH(TODAY()-1),1),$1:$1,0 )+1))))/S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY()
-1,$1:$1,0)-1,,-7)),(OFFSET(A17,,MATCH(TODAY()-1,$1:$1,0)-1,,-7)))/S17))
(Above lightly tested in order <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> No problem with all the attached solutions to this thread but have an
> enhancement that needs to be made and was wondering how to acheive
> this.
>
> Instead of running from todays date I need to run from yesterdays date.
> If anybody has the patience could you advise on how i reconfigure the
> today component of:
>
>
=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,,-(MATCH(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,,MATCH(TODAY(),$1:$1,0)-1,,-7)))/S17))
>
>
> to search on yesterdays date instead?
>
> I am certain it is simple but cannot crack it.
>
> Thanks in advance
>
> Kristan
>
>
> --
> Kstalker
> ------------------------------------------------------------------------
> Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
> View this thread: http://www.excelforum.com/showthread...hreadid=395995
>
Try instead:
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),$338:$338,0)+1))),"Formula_OR_Value_if_FALSE")
Replaced : " 19:19 " in your posted formula
with: " $338:$338 "
Believe the above is the main error part
... how on earth did the " 19:19 " creep into the picture ? <g>
I've also added a phrase: "Formula_OR_Value_if_FALSE"
since your posted formula was w/o this value
(just change the phrase to whatever
you want returned as the value if FALSE.)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Cheers Max.
>
> I though it was as simple as that but I keep coming up with a 'false'
> result using the formula below.....
>
>
=IF(AND(DAY(TODAY()-1)>=1,DAY(TODAY()-1)<=6),AVERAGE(OFFSET(A536,,MATCH(TODA
Y()-1,$338:$338,0)-1,,-(MATCH(TODAY()-1,$338:$338,0)-MATCH(DATE(YEAR(TODAY()
-1),MONTH(TODAY()-1),1),19:19,0)+1))))
>
> Absoultely stumped.....
>
>
> --
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
--
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You're welcome, Kristan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kstalker" <[email protected]> wrote in
message news:[email protected]...
>
> Excellent.
>
> Thanks for that Max.
>
> Regards
>
> Kristan
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks