If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Max, what do you see that I don't?
I would think this should be sufficient: =IF(COUNT(2:2)7,SUM(A2:AE2),................. Biff "Max" wrote in message ... And if there's the possibility that the number of historic data days in Sheet1's B1:AF1 is 7 days from "today", and you want for such situations to just sum the available historicals up till and inclusive of "today" (notwithstanding it's less than the specified 7 days), we could put instead in Sheet2's A2: =IF(MATCH(TODAY(),Sheet1!$1:$1,0)-17,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 -- |
#12
|
|||
|
|||
"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 -- |
#13
|
|||
|
|||
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)-17,SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),$1:$1, 0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1, 0)-1,,-7)))/T25))-1,,-(MATCH(TODAY(),$1:$1,0)-1))),SUMPRODUCT((OFFSET(A3,,MATCH(TODAY(),$1:$1, 0)-1,,-7)),(OFFSET(A25,,MATCH(TODAY(),$1:$1, 0)-1,,-7)))/T25) Again, if you get a minute could you take a look at the attached formula and poitn out the error in my ways. Thanks again. Kristan -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=395995 |
#14
|
|||
|
|||
Try instead :
=IF(T25=0,0,IF(MATCH(TODAY(),1:1,0)-17,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" wrote in message ... 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)-17,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 |
#15
|
|||
|
|||
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)-17,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 -- |
#16
|
|||
|
|||
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 -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=395995 |
#17
|
|||
|
|||
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)-17,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 -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=395995 |
#18
|
|||
|
|||
Believe essentially that you have dates running along from B1 across (B1,
C1, D1, ...) which do not necessarily start from the 1st of the month in B1 Try this revision: =IF(S17=0,0,IF(AND(DAY(TODAY())=1,DAY(TODAY())=6 ),SUMPRODUCT((OFFSET(A9,,M ATCH(TODAY(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),M ONTH(TODAY()),1),$1:$1,0)+1))),(OFFSET(A17,,MATCH( TODAY(),$1:$1,0)-1,,-(MATC H(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$1:$1,0 )+1)))) /S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY(),$1:$1,0)-1,,-7)),(OFFSET(A17,,MATC H(TODAY(),$1:$1,0)-1,,-7)))/S17)) The changes made are to the 2nd IF whe IF(AND(DAY(TODAY())=1,DAY(TODAY())=6) replaces the previous : IF(MATCH(TODAY(),$1:$1,0)-17 and to the width params of OFFSET within the 1st SUMPRODUCT, whe -(MATCH(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$1:$1,0 ) +1 replaces the previous: -(MATCH(TODAY(),$1:$1,0)-1 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Kstalker" wrote in message ... Alternatively.... Is it possible to add criteria to the equation below. The reason I ask is I am trying to stop the equation below (which works perfectly thanks Max) from going past the 1st of the month should i be trying to add a weekly summary from the 6th day of the month back to the 1st. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 .......etc (--------------) "=IF(S17=0,0,IF(MATCH(TODAY(),$1:$1,0)-17,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 |
#19
|
|||
|
|||
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 -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=395995 |
#20
|
|||
|
|||
.......... 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 -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy Cat Ain't Working | shep | Setting Up & Running Reports | 15 | September 12th, 2005 05:14 PM |
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | TC | Worksheet Functions | 1 | May 12th, 2005 02:06 AM |
Error Message - "Query is too complex" | Steve | Running & Setting Up Queries | 6 | May 6th, 2005 03:47 PM |
Using Validation to force entry into cells? | Mark | General Discussion | 16 | October 27th, 2004 09:23 PM |
Using date range in query criteria | Kathi | Running & Setting Up Queries | 4 | August 30th, 2004 03:10 PM |