+ Reply to Thread
Page 3 of 3 FirstFirst 1 2 3
Results 401 to 413 of 413

date criteria to select range

  1. #401
    Biff
    Guest

    Re: date criteria to select range

    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
    > --
    >
    >
    >




  2. #402
    Max
    Guest

    Re: date criteria to select range

    "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
    --



  3. #403
    Max
    Guest

    Re: date criteria to select range

    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
    >




  4. #404
    Max
    Guest

    Re: date criteria to select range

    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
    --



  5. #405
    Max
    Guest

    Re: date criteria to select range

    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
    >




  6. #406
    Max
    Guest

    Re: date criteria to select range

    > .......... 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
    --



  7. #407
    Biff
    Guest

    Re: date criteria to select range

    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
    >>

    >
    >




  8. #408
    Max
    Guest

    Re: date criteria to select range

    "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
    ---



  9. #409
    Max
    Guest

    Re: date criteria to select range

    "Biff" wrote
    > ugh!
    > <g>


    Thanks ! <g>
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    ---



  10. #410
    Max
    Guest

    Re: date criteria to select range

    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
    --



  11. #411
    Max
    Guest

    Re: date criteria to select range

    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
    --



  12. #412
    Max
    Guest

    Re: date criteria to select range

    > ... 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
    >




  13. #413
    Max
    Guest

    Re: date criteria to select range

    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
    >




+ Reply to Thread
Page 3 of 3 FirstFirst 1 2 3

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1