K
KR
I have a complex array formula where I'm trying to add one condition, and I
can't get it to work- so after way too much frustration, I'm here to ask you
experts for some help/syntax. I need to be able to tell (for a given target
date) how many incidents were open as of that date (which is working) and
now, how old those incidents were- in this case, how many were less than 10
days old (not working);
In my source worksheet (Total04) I have the following information:
-------------------------------------------------------------------
Column B: many ID's, including one that I'm looking for
Column E: "Start date" which has a start date or may be
blank (I ignore all records that have no start date)
Column F: "End date" which may have an end date, or may
be blank (which indicates that it hasn't been closed
yet). I ignore entries where the end date preceeds
my target date, because those entries were already
closed by that date.
In my active worksheet ('Rept Age') I reference two cells; L2 has the ID I'm
matching, and B4 has the target date I'm using.
In my _working_ formula I am just looking to see how many events occurred
which started before my specific date and were still open as of that date.
This formula appears to be working;
=SUM((IF(Total04!B$6:B2000='REPT
Age'!L$2,1,0))*(IF(Total04!E$6:E2000>0,1,0))*(IF(Total04!F$6:F2000="",1,0)+I
F(Total04!F$6:F2000>=B4,1,0))*(IF(Total04!E$6:E2000<=B4,1,0)))
'First IF: does it match my target ID
'Second IF: is the start date >0 (e.g., does it have a start date)
'Third & Fourth IF: Either there is no end date, or, the end date is later
than the target date
'Fifth IF: Verify that start date is actually before my target date
Now I need to break it down into buckets of how old each event was as of my
target date- for example, 1-10 days, 11-20 days, etc.
So, I thought I'd add one more condition. Here is the same formula, where I
have added my one additional condition, but all I get is the #Value and I
haven't figured out why. Any help would be greatly appreciated!
=SUM( (IF(Total04!B$6:B2000='UIR Age'!L$2,1,0))
*(IF(Total04!E$6:E2000>0,1,0))
*(IF(Total04!F$6:F2000="",1,0)+IF(Total04!F$6:F2000>=B4,1,0))
*(IF(Total04!E$6:E2000<=B4,1,0))*IF(Total04!E$6:E2000+10>=B4,1,0))
So all I added was the condition (multiplied in) of:
IF(Total04!E$6:E2000+10>=B4,1,0)
because if the start date +10 [days] is greater than my target date, then I
know that the event was less than 10 days old as of my target date
(regardless of when or whether the event was ever closed).
All I get is "#Value". Does it have something to do with the +10 not being
in an array? If so, what is the most eloquent way to fix it?
help!?!
Thanks,
Keith
can't get it to work- so after way too much frustration, I'm here to ask you
experts for some help/syntax. I need to be able to tell (for a given target
date) how many incidents were open as of that date (which is working) and
now, how old those incidents were- in this case, how many were less than 10
days old (not working);
In my source worksheet (Total04) I have the following information:
-------------------------------------------------------------------
Column B: many ID's, including one that I'm looking for
Column E: "Start date" which has a start date or may be
blank (I ignore all records that have no start date)
Column F: "End date" which may have an end date, or may
be blank (which indicates that it hasn't been closed
yet). I ignore entries where the end date preceeds
my target date, because those entries were already
closed by that date.
In my active worksheet ('Rept Age') I reference two cells; L2 has the ID I'm
matching, and B4 has the target date I'm using.
In my _working_ formula I am just looking to see how many events occurred
which started before my specific date and were still open as of that date.
This formula appears to be working;
=SUM((IF(Total04!B$6:B2000='REPT
Age'!L$2,1,0))*(IF(Total04!E$6:E2000>0,1,0))*(IF(Total04!F$6:F2000="",1,0)+I
F(Total04!F$6:F2000>=B4,1,0))*(IF(Total04!E$6:E2000<=B4,1,0)))
'First IF: does it match my target ID
'Second IF: is the start date >0 (e.g., does it have a start date)
'Third & Fourth IF: Either there is no end date, or, the end date is later
than the target date
'Fifth IF: Verify that start date is actually before my target date
Now I need to break it down into buckets of how old each event was as of my
target date- for example, 1-10 days, 11-20 days, etc.
So, I thought I'd add one more condition. Here is the same formula, where I
have added my one additional condition, but all I get is the #Value and I
haven't figured out why. Any help would be greatly appreciated!
=SUM( (IF(Total04!B$6:B2000='UIR Age'!L$2,1,0))
*(IF(Total04!E$6:E2000>0,1,0))
*(IF(Total04!F$6:F2000="",1,0)+IF(Total04!F$6:F2000>=B4,1,0))
*(IF(Total04!E$6:E2000<=B4,1,0))*IF(Total04!E$6:E2000+10>=B4,1,0))
So all I added was the condition (multiplied in) of:
IF(Total04!E$6:E2000+10>=B4,1,0)
because if the start date +10 [days] is greater than my target date, then I
know that the event was less than 10 days old as of my target date
(regardless of when or whether the event was ever closed).
All I get is "#Value". Does it have something to do with the +10 not being
in an array? If so, what is the most eloquent way to fix it?
help!?!
Thanks,
Keith