+ Reply to Thread
Results 1 to 13 of 13

2nd attempt ~ Faster/Shorter formula

  1. #1
    Luke
    Guest

    2nd attempt ~ Faster/Shorter formula

    Is there a way to add the numbers in row 2 that have the same date so that if
    the result is Equal to, OR, minus 1 to 3 before, OR, plus 1 to 3 over (say
    30), then diplay the result, otherwise display nothing?
    Formula needs to be in row 3 and if the date are different, as in B1, then
    the calculation would start over with the new date in the next column.
    Desired results are shown in row 3
    My attempts have resulted in formula that is too long.
    A B C D
    1 Date 8/29/02 11/6/05 11/6/05
    2 Draft 16 20 7
    3 New 16 27

    Thank you
    Luke


  2. #2
    Harlan Grove
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    "Luke" <[email protected]> wrote...
    >Is there a way to add the numbers in row 2 that have the same date
    >so that if the result is Equal to, OR, minus 1 to 3 before, OR,
    >plus 1 to 3 over (say 30), then diplay the result, otherwise
    >display nothing? Formula needs to be in row 3 and if the date are
    >different, as in B1, then the calculation would start over with
    >the new date in the next column.
    >Desired results are shown in row 3
    >My attempts have resulted in formula that is too long.
    > A B C D
    >1 Date 8/29/02 11/6/05 11/6/05
    >2 Draft 16 20 7
    >3 New 16 27


    Would the row 1 dates always be ordered older to newer left to right? If so,
    enter the following formula in B3.

    B3:
    =IF(COUNTIF($A$1:A$1,B$1)=0,SUMIF(B$1:$IV$1,B$1,B$2:$IV$2),"")

    Fill B3 right as far as needed.



  3. #3
    Luke
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    Harlan, Thanks for looking.
    Yes that's right, date older to never.
    Your formula is great for adding the Row 2 together progressively. However,
    I need it to add the numbers in row 2 that have the same date, Then if the
    result is Equal to, say 30 days, OR, minus 1 to 3 before, OR, plus 1 to 3
    over 30 days, then diplay the result, otherwise display nothing.

    For example, I have added columns to the original post.
    Notice C2=20, C3="" then D2=7, D3=27 (3 days before total of 30 days), and
    in E2=5, E3=32 but F2=2, F3="" (because the result would be 34, 1 day overthe
    desired criteria)
    e.g. In D2 the numbers 7 thru 13 would have results in D3 (30-3=27, 30-2=28,
    30-1=29, 30-0=30, 30+1=31, 30+2=32 and 30+3=33). Anything else would result
    in "".
    A B C D
    1 Date 8/29/02 11/6/03 11/8/03
    2 Draft 16 20 7
    3 New 16 27

    A E F G
    1 Date 12/5/03 12/9/03 11/6/05
    2 Draft 5 2 7
    3 New 32

    I'll keep pestering it to see if I can get it to fester.
    Thanks for your response Harlan
    Luke

    "Harlan Grove" wrote:

    > "Luke" <[email protected]> wrote...
    > >Is there a way to add the numbers in row 2 that have the same date
    > >so that if the result is Equal to, OR, minus 1 to 3 before, OR,
    > >plus 1 to 3 over (say 30), then diplay the result, otherwise
    > >display nothing? Formula needs to be in row 3 and if the date are
    > >different, as in B1, then the calculation would start over with
    > >the new date in the next column.
    > >Desired results are shown in row 3
    > >My attempts have resulted in formula that is too long.
    > > A B C D
    > >1 Date 8/29/02 11/6/05 11/6/05
    > >2 Draft 16 20 7
    > >3 New 16 27

    >
    > Would the row 1 dates always be ordered older to newer left to right? If so,
    > enter the following formula in B3.
    >
    > B3:
    > =IF(COUNTIF($A$1:A$1,B$1)=0,SUMIF(B$1:$IV$1,B$1,B$2:$IV$2),"")
    >
    > Fill B3 right as far as needed.
    >
    >
    >


  4. #4
    Harlan Grove
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    Luke wrote...
    >Yes that's right, date older to never.

    ....
    >I need it to add the numbers in row 2 that have the same date, Then if the
    >result is Equal to, say 30 days, OR, minus 1 to 3 before, OR, plus 1 to 3
    >over 30 days, then diplay the result, otherwise display nothing.


    So you need to catch the range 27 to 33. Outside that range display
    nothing ("").

    >For example, I have added columns to the original post.
    >Notice C2=20, C3="" then D2=7, D3=27 (3 days before total of 30 days), and
    >in E2=5, E3=32 but F2=2, F3="" (because the result would be 34, 1 day overthe
    >desired criteria)


    But C1 and D1 differ. Are you changing specs, or is that a typo?

    >e.g. In D2 the numbers 7 thru 13 would have results in D3 (30-3=27, 30-2=28,
    >30-1=29, 30-0=30, 30+1=31, 30+2=32 and 30+3=33). Anything else would result
    >in "".
    > A B C D
    >1 Date 8/29/02 11/6/03 11/8/03
    >2 Draft 16 20 7
    >3 New 16 27
    >
    > A E F G
    >1 Date 12/5/03 12/9/03 11/6/05
    >2 Draft 5 2 7
    >3 New 32

    ....

    This is unclear. First you mention 'same date', but your examples show
    different dates. You either need to fix your examples or your specs. If
    the dates in the examples above are correct, then you're *not* matching
    same dates.


  5. #5
    Luke
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    Harlan,
    So Sorry, I Should have said "Same Year". I bow in disgrace.
    Luke

    "Harlan Grove" <[email protected]> wrote in message
    news:[email protected]...
    > Luke wrote...
    >>Yes that's right, date older to never.

    > ...
    >>I need it to add the numbers in row 2 that have the same date, Then if the
    >>result is Equal to, say 30 days, OR, minus 1 to 3 before, OR, plus 1 to 3
    >>over 30 days, then diplay the result, otherwise display nothing.

    >
    > So you need to catch the range 27 to 33. Outside that range display
    > nothing ("").
    >
    >>For example, I have added columns to the original post.
    >>Notice C2=20, C3="" then D2=7, D3=27 (3 days before total of 30 days), and
    >>in E2=5, E3=32 but F2=2, F3="" (because the result would be 34, 1 day
    >>overthe
    >>desired criteria)

    >
    > But C1 and D1 differ. Are you changing specs, or is that a typo?
    >
    >>e.g. In D2 the numbers 7 thru 13 would have results in D3 (30-3=27,
    >>30-2=28,
    >>30-1=29, 30-0=30, 30+1=31, 30+2=32 and 30+3=33). Anything else would
    >>result
    >>in "".
    >> A B C D
    >>1 Date 8/29/02 11/6/03 11/8/03
    >>2 Draft 16 20 7
    >>3 New 16 27
    >>
    >> A E F G
    >>1 Date 12/5/03 12/9/03 11/6/05
    >>2 Draft 5 2 7
    >>3 New 32

    > ...
    >
    > This is unclear. First you mention 'same date', but your examples show
    > different dates. You either need to fix your examples or your specs. If
    > the dates in the examples above are correct, then you're *not* matching
    > same dates.
    >




  6. #6
    Luke
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    Harlan,
    So Sorry, I Should have said "Same Year". I bow in disgrace.
    Luke

    "Harlan Grove" <[email protected]> wrote in message
    news:[email protected]...
    > Luke wrote...
    >>Yes that's right, date older to never.

    > ...
    >>I need it to add the numbers in row 2 that have the same date, Then if the
    >>result is Equal to, say 30 days, OR, minus 1 to 3 before, OR, plus 1 to 3
    >>over 30 days, then diplay the result, otherwise display nothing.

    >
    > So you need to catch the range 27 to 33. Outside that range display
    > nothing ("").
    >
    >>For example, I have added columns to the original post.
    >>Notice C2=20, C3="" then D2=7, D3=27 (3 days before total of 30 days), and
    >>in E2=5, E3=32 but F2=2, F3="" (because the result would be 34, 1 day
    >>overthe
    >>desired criteria)

    >
    > But C1 and D1 differ. Are you changing specs, or is that a typo?
    >
    >>e.g. In D2 the numbers 7 thru 13 would have results in D3 (30-3=27,
    >>30-2=28,
    >>30-1=29, 30-0=30, 30+1=31, 30+2=32 and 30+3=33). Anything else would
    >>result
    >>in "".
    >> A B C D
    >>1 Date 8/29/02 11/6/03 11/8/03
    >>2 Draft 16 20 7
    >>3 New 16 27
    >>
    >> A E F G
    >>1 Date 12/5/03 12/9/03 11/6/05
    >>2 Draft 5 2 7
    >>3 New 32

    > ...
    >
    > This is unclear. First you mention 'same date', but your examples show
    > different dates. You either need to fix your examples or your specs. If
    > the dates in the examples above are correct, then you're *not* matching
    > same dates.
    >




  7. #7
    Harlan Grove
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    "Luke" <[email protected]> wrote...
    >So Sorry, I Should have said "Same Year". I bow in disgrace.

    ....

    Using years makes the formulas a bit more complicated.

    B3:
    =IF(AND(SUMPRODUCT(--(YEAR($A$1:A$1)=YEAR(B$1)))=0,
    ABS(SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2)-30)<=3),
    SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2),"")

    Fill right as needed.



  8. #8
    Luke
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    Yes, a bit complicated. Formula in B3 (fill right) returns #VALUE! If I
    jockey the formula around a bit and put it in C3 (fill right) I get all
    blanks in row 3.
    I will keep after it. Please let me know if you see something that needs
    corrected. I'll do the same.


    "Harlan Grove" <[email protected]> wrote in message
    news:[email protected]...
    > "Luke" <[email protected]> wrote...
    >>So Sorry, I Should have said "Same Year". I bow in disgrace.

    > ...
    >
    > Using years makes the formulas a bit more complicated.
    >
    > B3:

    =IF(AND(SUMPRODUCT(--(YEAR($A$1:A$1)=YEAR(B$1)))=0,ABS(SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2)-30)<=3),SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2),"")
    >
    > Fill right as needed.
    >




  9. #9
    Luke
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    I'm stumped

    "Luke" <[email protected]> wrote in message
    news:[email protected]...
    > Yes, a bit complicated. Formula in B3 (fill right) returns #VALUE! If I
    > jockey the formula around a bit and put it in C3 (fill right) I get all
    > blanks in row 3.
    > I will keep after it. Please let me know if you see something that needs
    > corrected. I'll do the same.
    >
    >
    > "Harlan Grove" <[email protected]> wrote in message
    > news:[email protected]...
    >> "Luke" <[email protected]> wrote...
    >>>So Sorry, I Should have said "Same Year". I bow in disgrace.

    >> ...
    >>
    >> Using years makes the formulas a bit more complicated.
    >>
    >> B3:

    > =IF(AND(SUMPRODUCT(--(YEAR($A$1:A$1)=YEAR(B$1)))=0,ABS(SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2)-30)<=3),SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2),"")
    >>
    >> Fill right as needed.
    >>

    >
    >




  10. #10
    Luke
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    Im stumped but I think I'll try using the first formula and manipulating
    that somehow.

    "Luke" <[email protected]> wrote in message
    news:[email protected]...
    > Yes, a bit complicated. Formula in B3 (fill right) returns #VALUE! If I
    > jockey the formula around a bit and put it in C3 (fill right) I get all
    > blanks in row 3.
    > I will keep after it. Please let me know if you see something that needs
    > corrected. I'll do the same.
    >
    >
    > "Harlan Grove" <[email protected]> wrote in message
    > news:[email protected]...
    >> "Luke" <[email protected]> wrote...
    >>>So Sorry, I Should have said "Same Year". I bow in disgrace.

    >> ...
    >>
    >> Using years makes the formulas a bit more complicated.
    >>
    >> B3:

    > =IF(AND(SUMPRODUCT(--(YEAR($A$1:A$1)=YEAR(B$1)))=0,ABS(SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2)-30)<=3),SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2),"")
    >>
    >> Fill right as needed.
    >>

    >
    >




  11. #11
    Harlan Grove
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    Luke wrote...
    >Yes, a bit complicated. Formula in B3 (fill right) returns #VALUE! If I
    >jockey the formula around a bit and put it in C3 (fill right) I get all
    >blanks in row 3.

    ....

    My fault. Didn't test. The col B formula can't be the same as the col C
    and further right formulas.

    B3:
    =IF(ABS(SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2)-30)<=3,
    SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2),"")

    C3:
    =IF(AND(SUMPRODUCT(--(YEAR($B$1:B$1)=YEAR(C$1)))=0,
    ABS(SUMPRODUCT(--(YEAR(C$1:$IV$1)=YEAR(C$1)),C$2:$IV$2)-30)<=3),
    SUMPRODUCT(--(YEAR(C$1:$IV$1)=YEAR(C$1)),C$2:$IV$2),"")


  12. #12
    Luke
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula

    "Harlan Grove" wrote:

    > My fault. Didn't test. The col B formula can't be the same as the col C
    > and further right formulas.


    I see this as the column B3 formula to go into B3 and stay put. The Column
    C3 formula to go into C3 and can be filled right as needed.
    If that is correct I get blank cells throughout. I tried some other
    variations with no luck.
    However, I am on my work pc (different excel version) at this moment so I
    will give it a go on my pc at home in a couple hours.

    B3:
    =IF(ABS(SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2)-30)<=3,
    SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2),"")

    C3:
    =IF(AND(SUMPRODUCT(--(YEAR($B$1:B$1)=YEAR(C$1)))=0,
    ABS(SUMPRODUCT(--(YEAR(C$1:$IV$1)=YEAR(C$1)),C$2:$IV$2)-30)<=3),
    SUMPRODUCT(--(YEAR(C$1:$IV$1)=YEAR(C$1)),C$2:$IV$2),"")



  13. #13
    Luke
    Guest

    Re: 2nd attempt ~ Faster/Shorter formula


    "Harlan Grove" <[email protected]> wrote in message
    news:[email protected]...
    My fault. Didn't test. The col B formula can't be the same as the col C
    and further right formulas.

    Curious enough, if I put, in D2, either of the numbers 0 to 6 then C3
    displays 27 to 33 respectively. This is very close to the goal but because
    C2+D2=27 (30-3) then D3 should display 27 rather than C3, (i.e. To get
    results in D3, D2 can = 7 to13, therefore D3 would = 27 to 33 respectively).

    What I see happening, C3 is adding C2+D2 and if the sum is <20 or >27 then
    results are "", but if the results are >=20 and <=26 then I see reults.
    Again however in C3 rather than the desired D3 (intended >=27 and <=33)

    I dare to point out that if by chance D2 was a 1 (rather than a 7) would the
    formula then sum C2+D2+E2 to see if its sum is the intended >=27 and <=33,
    if not, then sum C2+D2+E2+F2 and so on (keep in mind the year) and
    displaying the results in the final cell i.e F2.

    In this case a revelation takes place (the following table changed for the
    sake of argument and to correct an issue I didn't catch earlier). I
    appologize, I got so wrapped up in this that I didn't make sure date (days)
    were consistant with the count in row 2, and, B3 should have NEVER equalled
    16.

    A B C D
    1 Date 8/29/02 11/6/03 11/8/03
    2 Draft 16 20 2
    3 New

    A E F G
    1 Date 11/13/03 11/18/03 11/25/05
    2 Draft 5 5 7
    3 New 26 31


    ORIGINAL TABLE:
    A B C D
    1 Date 8/29/02 11/6/03 11/8/03
    2 Draft 16 20 7
    3 New 16 27

    A E F G
    1 Date 12/5/03 12/9/03 11/6/05
    2 Draft 5 2 7
    3 New 32




    B3:
    =IF(ABS(SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2)-30)<=3,
    SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2),"")

    C3:
    =IF(AND(SUMPRODUCT(--(YEAR($B$1:B$1)=YEAR(C$1)))=0,
    ABS(SUMPRODUCT(--(YEAR(C$1:$IV$1)=YEAR(C$1)),C$2:$IV$2)-30)<=3),
    SUMPRODUCT(--(YEAR(C$1:$IV$1)=YEAR(C$1)),C$2:$IV$2),"")



+ Reply to Thread

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