Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code a long array formula
Hi,
I'm trying to code a program to write a formula array into a spreadsheet; to get the right calculation, I need the sum of the values in column "B" that fall on each date in a monthly period and between two times on each day. The following formula works fine when manually typed, but is beyond the XL limit for coding the entry of array formulas: =SUM(IF(LEFT('UMS-IT'!$L$2:$L$71805,11)="AMSCOT ARLD",IF(TEXT('UMS-IT'!$M$2:$M$71805,"MM-DD-YYYY HH:MM:SS")"04-12-2009 17:00:00",IF(TEXT('UMS-IT'!$M$2:$M$71805,"MM-DD-YYYY HH:MM:SS")<"04-12-2009 24:00:01",'UMS-IT'!$B$2:$B$71805)))) Anyone have any ideas as to how I can code this? Or is it possible to internally calculate and just write the result (if so how?)? Thanks much for your assistance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code a long array formula
If I were using VBA...
Take a look at Dick Kusleika's site: http://www.dailydoseofexcel.com/arch...rmulas-in-vba/ It's a technique to workaround that .formulaarray length limit. xp wrote: Hi, I'm trying to code a program to write a formula array into a spreadsheet; to get the right calculation, I need the sum of the values in column "B" that fall on each date in a monthly period and between two times on each day. The following formula works fine when manually typed, but is beyond the XL limit for coding the entry of array formulas: =SUM(IF(LEFT('UMS-IT'!$L$2:$L$71805,11)="AMSCOT ARLD",IF(TEXT('UMS-IT'!$M$2:$M$71805,"MM-DD-YYYY HH:MM:SS")"04-12-2009 17:00:00",IF(TEXT('UMS-IT'!$M$2:$M$71805,"MM-DD-YYYY HH:MM:SS")<"04-12-2009 24:00:01",'UMS-IT'!$B$2:$B$71805)))) Anyone have any ideas as to how I can code this? Or is it possible to internally calculate and just write the result (if so how?)? Thanks much for your assistance! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code a long array formula
Hi
Try this Formula, where B2 and B3 on formula sheet have the start / end date and time: =SUMPRODUCT((--LEFT('UMS-IT'!$L$2:$L$100000,11)="AMSCOTARLD"),--(TEXT('UMS-IT'!$M$2:$M$100000,"MM-DD-YYYY HH:MM:SS"B2)),--(TEXT('UMS-IT'!$M$2:$M$100000;"MM-DD-YYYY HH:MM:SS"<B3)),'UMS-IT'!$B$2:$B$100000) Hopes this helps. --- Per "xp" skrev i meddelelsen ... Hi, I'm trying to code a program to write a formula array into a spreadsheet; to get the right calculation, I need the sum of the values in column "B" that fall on each date in a monthly period and between two times on each day. The following formula works fine when manually typed, but is beyond the XL limit for coding the entry of array formulas: =SUM(IF(LEFT('UMS-IT'!$L$2:$L$71805,11)="AMSCOT ARLD",IF(TEXT('UMS-IT'!$M$2:$M$71805,"MM-DD-YYYY HH:MM:SS")"04-12-2009 17:00:00",IF(TEXT('UMS-IT'!$M$2:$M$71805,"MM-DD-YYYY HH:MM:SS")<"04-12-2009 24:00:01",'UMS-IT'!$B$2:$B$71805)))) Anyone have any ideas as to how I can code this? Or is it possible to internally calculate and just write the result (if so how?)? Thanks much for your assistance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace html code - formula too long | Excel Discussion (Misc queries) | |||
Array formula to code | Excel Programming | |||
Evalute Array Formula in VBA code | Excel Programming | |||
Code for Array formula | Excel Programming | |||
Challenging long ARRAY formula needed - Can this be done? | Excel Programming |