Formula Challenge
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.
Zach:
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.
7 September 2007, 11:42 amAlderaic:
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…
7 September 2007, 11:56 amusing multiple columns + choose could be another solution.
most difficult part is two handle the case where more than 1 group can be the solution.
Excel Training Boise » Blog Archive » Formula Brainteaser| Microsoft Excel Classes & Training Seminars in Boise, Idaho | Free Tips!:
[…] this formula challenge from Daily Dose of […]
7 September 2007, 12:29 pmMichael:
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
7 September 2007, 1:09 pmMichael:
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 September 2007, 1:13 pmfzz:
test
7 September 2007, 11:07 pmdermot:
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
8 September 2007, 12:18 am=(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.
Ola:
This spreadsheet works with 3 steps. I could have made with two steps or perhaps one (SingleCellFormula). But then nobody would understand it (including me).
8 September 2007, 4:12 am//Ola
http://web.omnidrive.com/APIServer/public/8qr1RsuHYalOM7wsHSHS4J8w/FormulaChallenge.xls
fzz:
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.
8 September 2007, 12:59 pmTom:
Dick
8 September 2007, 6:27 pmHow about publishing the UDF as well?
Gary Waters:
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)
11 September 2007, 6:12 pmGary Waters:
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)
11 September 2007, 6:26 pmGary Waters:
Dick,
Hopefully, third times a charm. Trying with VB Tags:
Michael:
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
12 September 2007, 6:29 amGary Waters:
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)
12 September 2007, 2:42 pmOla:
I have updated the file with the 3 proposals but...
fzz, I could not get your formula to work. So please let me know what could be wrong.
Gary, the formula looks impressivly short but I couldn't find the link to E4 (consec. Days: 2).
//Ola
http://web.omnidrive.com/APIServer/public/8qr1RsuHYalOM7wsHSHS4J8w/FormulaChallenge.xls
13 September 2007, 4:07 pmOla:
The Excel file should be accessable now. Omnidrive or Zoho can not open Excel with these formulas. The new link is a zip file with the Excel file inside.
13 September 2007, 4:54 pm//Ola
http://web.omnidrive.com/APIServer/public/8qr1RsuHYalOM7wsHSHS4J8w/FormulaChallenge.xls.zip
Gary Waters:
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)
13 September 2007, 8:52 pmGary Waters:
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)
13 September 2007, 9:35 pmGary Waters:
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))))
16 September 2007, 8:39 pmJohn K:
=SUMPRODUCT(--(A2:A17>=E2),--(A2:A17=E5),--(OFFSET(B2:B17,E4-1,0)>=400))
If You still needed help...
2 October 2007, 4:01 pmJohn K:
Hmm... issues with the formula completely posting...
So we'll do this one step at a time... all in one sumproduct formula
2 October 2007, 4:06 pmA2: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
John:
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
16 May 2008, 1:31 amJohn:
Hmm, I missed step 2.. I'm sure you guessed it, it uses column H to determine if we are over the threshhold:
16 May 2008, 1:33 am=IF(B2>=$E$5,"Yes", "no")
Jon Peltier:
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).
17 May 2008, 8:19 am