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 |
#21
|
|||
|
|||
Mail enroute. Unsure if it is possible. 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 |
#22
|
|||
|
|||
ugh!
g Biff "Max" wrote in message ... Believe essentially that you have dates running along from B1 across (B1, C1, D1, ...) which do not necessarily start from the 1st of the month in B1 Try this revision: =IF(S17=0,0,IF(AND(DAY(TODAY())=1,DAY(TODAY())=6 ),SUMPRODUCT((OFFSET(A9,,M ATCH(TODAY(),$1:$1,0)-1,,-(MATCH(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),M ONTH(TODAY()),1),$1:$1,0)+1))),(OFFSET(A17,,MATCH( TODAY(),$1:$1,0)-1,,-(MATC H(TODAY(),$1:$1,0)-MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$1:$1,0 )+1)))) /S17,SUMPRODUCT((OFFSET(A9,,MATCH(TODAY(),$1:$1,0)-1,,-7)),(OFFSET(A17,,MATC H(TODAY(),$1:$1,0)-1,,-7)))/S17)) The changes made are to the 2nd IF whe IF(AND(DAY(TODAY())=1,DAY(TODAY())=6) replaces the previous : IF(MATCH(TODAY(),$1:$1,0)-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 |
#23
|
|||
|
|||
"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 --- |
#24
|
|||
|
|||
"Biff" wrote
ugh! g Thanks ! g -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 --- |
#25
|
|||
|
|||
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 -- |
#26
|
|||
|
|||
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 -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=395995 |
#27
|
|||
|
|||
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 -- |
#28
|
|||
|
|||
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 -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=395995 |
#29
|
|||
|
|||
... 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" wrote in message ... 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 |
#30
|
|||
|
|||
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..... -- Kstalker ------------------------------------------------------------------------ Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699 View this thread: http://www.excelforum.com/showthread...hreadid=395995 |
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 |