Formula Challenge

Posted on by Dick Kusleika

I wrote a VBA user-defined function for this task, but I can’t help but think there’s a worksheet formula that can do the job. In case you were looking for something to do this weekend, here’s something to occupy your time.

You get a two column range, the starting group, the ending group, the number of days, and the threshold. The formula should return the number of groups between 2 (E2) and 4 (E3) inclusive that have at least 2 (E4) consecutive amounts greater than or equal to 400 (E5).

The answer is 2. Groups 1 and 5 are outside the range, so they don’t count. Group 2 only has 1 consecutive amount over the threshold. Groups 3 and 4 both have 2 consecutive amounts over the threshold. I should be able to change the inputs in column E to change the answer.

Have fun.

Posted in Uncategorized

29 thoughts on “Formula Challenge”

  1. Until I realized consecutive days was a variable I thought it might be possible, though a stretch, to get it into one formula. But I’m going to go out on a limb and say this is impossible.

  2. I think that using offset and sumproduct you can do it, though you would be limited in the number of days combinations (lets set from 2 days consec to 4 or 5 max) because you would need every combination and the max formula size would be reached (maybe even before 4)

    ie create an offset range of the beginning to the end (easy enough) use it with if to multiply it to the same range +1 row etc… etc…
    using multiple columns + choose could be another solution.
    most difficult part is two handle the case where more than 1 group can be the solution.

  4. This is as close as I can get. It returns the number of pairs of consecutive days, ie three consecutive days is two pairs. It’s totally immune to E4, so this can’t be all of it (if there is an all of it)

    =SUMPRODUCT(–($A$2:$A$17>=$E$2),–($A$2:$A$17=$E$5),–($B$1:$B$16>=$E5),–($A$1:$A$16=$A$2:$A$17))

    I can’t see how to make E4 matter.

    …mrt

  5. That’s munged. Drat the escapes.

    =SUMPRODUCT(–($A$2:$A$17 GTE $E$2),–($A$2:$A$17 LTE $E$3),–($B$2:$B$17 GTE $E$5),–($B$1:$B$16 GTE $E5),–($A$1:$A$16=$A$2:$A$17))

  7. The problem I have with this is, is that even if it’s possible, which seems unlikely, the formula would be totally incomprehensible. In business, it’s crucial that everything is clear and simple to avoid error.

    I would therefore add a column or two at the right of the data, one to calculate how many consecutive days have exceeded the threshold,eg in cell C3 I would have
    =(A3>=Start)*(A3Thresh)+C2)
    which tests the group is valid, and that it is the same as the previous group above, and counts the number of consecutive days. You would then need to count the unique groups which had a number >= the selected number of days.

  9. Sorry for the previous test message, but my responses have otherwise been blocked or lost.

    This can be done with a single array formula.

    =SUM((INDEX(Group,1):INDEX(Group,ROWS(Group)-N+1)
    =INDEX(Group,N):INDEX(Group,ROWS(Group)))
    *(MMULT(–(ABS(TRANSPOSE(ROW(Amount))
    -ROW(INDEX(Amount,1):INDEX(Amount,ROWS(Amount)-N+1))-(N-1)/2) .LT. N/2),
    (Group .GTE. Start)*(Group .LTE. End)*(Amount .GTE. T))=N))

    with faked operators .LT. for less than, .GTE. for greater than or equal to and .LTE. for less than or equal to. Also with groups column named Group, amount column named Amount, N for number of consecutive days, T for threshold amount, Start for starting group and End for ending group.

  11. Dick,

    I believe this array formula will work (include ctrl-shift-enter):

    =INDEX(FREQUENCY(FREQUENCY(IF((A2:A17>=E2)*(A2:A17=E5),A2:A17,””),ROW(INDIRECT(E2&”:”&E3))),{1}),2)

  12. Dick,

    It looks like part of my formula got chopped off somehow. Let’s try this again:

    =INDEX(FREQUENCY(FREQUENCY(IF((A2:A17>=E2)*(A2:A17=E5),A2:A17,””),ROW(INDIRECT(E2&”:”&E3))),{1}),2)

  13. Dick,

    Hopefully, third times a charm. Trying with VB Tags:

    =INDEX(FREQUENCY(FREQUENCY(IF((A2:A17>=E2)*(A2:A17=E5),A2:A17,“”),ROW(INDIRECT(E2&“:”&E3))),{1}),2)

  14. Hi Gary –

    I believe your formula is still truncated–as posted it returns 0, and as posted, does not include E4. The problem is probably not the VB tags but rather the escape characters needed to indicate “greater than or equals” and “less than or equals” etc.

    …Michael

  15. Michael,

    I tried getting around that. Well, here’s a 4th try (from Excel 2003 Script Debugger):

    =INDEX(FREQUENCY(FREQUENCY(IF((A2:A17>=E2)*(A2:A17<=E3)*(B2:B17>=E5),A2:A17,””),ROW(INDIRECT(E2&”:”&E3))),{1}),2)

  18. Ola,

    Here is the formula with E4 (not as short though):

    =INDEX(FREQUENCY(FREQUENCY(IF((A2:A17>=E2)*(A2:A17<=E3)*(B2:B17>=E5),A2:A17,””),ROW(INDIRECT(E2&”:”&E3))),ROW(INDIRECT((E4-1)&”:”&(E4-1)))),2)

  19. Dick,

    Sorry, but hopefully last simplication to my formula:

    =INDEX(FREQUENCY(FREQUENCY(IF((A2:A17>=E2)*(A2:A17<=E3)*(B2:B17>=E5),A2:A17,””),ROW(INDIRECT(E2&”:”&E3))),E4-1),2)

  20. Dick,

    My formula that I have been submitting did not do consecutive days. So I have one more, but uses an offset of Group and Amount columns. With Ctrl-Shift-Enter:

    =SUMPRODUCT(1*(FREQUENCY(IF((A2:A17>=E2)*(A2:A17<=E3)*(B2:B17>=E5)*IF(E4=1,1,(B3:B18>=E5)*(A2:A17-A3:A18=0)),A2:A17,””),ROW(INDIRECT(E2&”:”&E3)))>IF(E4=1,0,(E4-2))))

  21. =SUMPRODUCT(–(A2:A17>=E2),–(A2:A17=E5),–(OFFSET(B2:B17,E4-1,0)>=400))

    If You still needed help…

  22. Hmm… issues with the formula completely posting…

    So we’ll do this one step at a time… all in one sumproduct formula
    A2:A17 Greater than or Equal to E2
    A2:A17 Less than or Equal to E3
    A2:A17 = OFFSET(A2:A17, E4-1,0)
    B2:B17 Greater than or Equal to E5
    OFFSET(B2:B17, E4-1,0) Greater than or Equal to E5

  23. Well all those fancy shmancy formulae are too complexicated for me.. I did it in about 10 minutes by breaking it down to a few extra simple columns:

    1: Use column G to determine if a value is within the range of the start and end groups:
    =IF(AND(A2>=$E$2,A2=$E$5,”Yes”, “no”)

    3: Use column I to determine if a value is BOTH inside the range AND inside the threshhold:
    =IF(AND(G2=”Yes”,H2=”Yes”),”YES”,”No”)

    4: Use column J to append group information into column I’s values
    =A2&” “&I2

    5: Use column K to record the group number for every value which IS a consecutive value greater than or equal to the threshhold
    =IF(AND(J2=J1, COUNTIF(J2,”*YES*”)>0),A2,””)

    6: Count the number of unique values in column J
    =SUM(IF(FREQUENCY(K2:K17,K2:K17)>0,1))

    7: Hide columns G through K, knowing that these could easily be consolidated into one or 2 formulae, but seeing no need to do so

  24. Hmm, I missed step 2.. I’m sure you guessed it, it uses column H to determine if we are over the threshhold:
    =IF(B2>=$E$5,”Yes”, “no”)

  25. John –

    Use booleans instead of all the Yes and No. For example:

    Instead of =IF(AND(A2>=$E$2,A2=$E$5,”Yes”, “no”) use =AND(A2>=$E$2,A2=$E$5) to get True/False.

    Instead of =IF(AND(G2=”Yes”,H2=”Yes”),”YES”,”No”) use =AND(G2,H2).

  26. Sometimes we complicate things so much … Here is my response …
    Put the following formula in cell C3 and copy the same throughout the data range (Here, C2 to C17) …

    =IF(AND(A3>=$E$2,A3=$E$5,A2>=$E$2,A2=$E$5,A2=A3),1,0)

    Now, put the following formula in any cell and you’ll get the result as 2 …

    =SUM(C2:C65536)

  27. I’m not sure why the formula didn’t paste correctly, pasting it again (Formula in cell C3) …

    =IF(AND(A3>=$E$2,A3=$E$5,A2>=$E$2,A2=$E$5,A2=A3),1,0)

  28. Sorry but there seems to be some problem … trying it for last time …

    IF(AND(A3>=$E$2,A3=$E$5,A2>=$E$2,A2=$E$5,A2=A3),1,0)

    you can contact me at my email for any further clarification (jasmitsingh79@gmail.com)

  29. Formula :

    =FLOOR(DCOUNT(A1:B16,B1,E11:G12),2)

    E11 = “Group” E12 =”>=”&E2
    F11 = “Group” F12 =”=”&E5

    Regards,
    Dharmendra Makwana

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.