Need to formulate trigger once a certain value or milestone or threshold has been reached

Hello all,

I've stumbled upon a certain problem and have to solve it by using function/formulas. Any help would be greatly appreciated.

Let's assume, Jack and Mike have a joint account and they deposit their daily earnings everyday. Jack deposit his money ahead of Mike everyday.

'Who' earns 'What amount' at 'which' day is depicted in the attached spreadsheet.

Screenshot_2.jpg

The problem is:
1) Finding out who broke the 1000, 2000, 3000, 4000 and 5000 milestone/threshold first
2) Finding out in which days the thresholds have been broken

That's about it. Thanks a lot in advance!

Please review Rule #2 (titles)

This seems to work...

This array formula** entered in H5 and copied down to H9:

=IF(AND(SUM(C$3:C$22)<G5,SUM(D$3:D$22)<G5),"",IF(SUM(C$3:C$22)<G5,D$2,IF(SUM(D$3:D$22)<G5,C$2,IF(MATCH(TRUE,SUBTOTAL(9,OFFSET(C$3,,,ROW(C$3:C$22)-ROW(C$3)+1))>=G5,0)<=MATCH(TRUE,SUBTOTAL(9,OFFSET(D$3,,,ROW(D$3:D$22)-ROW(D$3)+1))>=G5,0),C$2,D$2))))

This array formula** entered in I5 and copied down to I9:

=IF(H5="","",INDEX(B$3:B$22,MATCH(TRUE,SUBTOTAL(9,OFFSET(IF(H5=C$2,C$3,D$3),,,ROW(D$3:D$22)-ROW(D$3)+1))>=G5,0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

In your sample file no one has reached those levels!

Jack's max level is 2712 and Mike's max level is 2315.

I see.

Well, that changes things dramatically!

Let me see if I can come up with something but don't hold your breath.

I used several helper columns (15).
D: Jack's cumulative (earnings) subtotal"=IFERROR(SUM(C$3:C4),C4)"

F: Mike's cumulative (earnings) subtotal

G: Cumulative Total "=SUM(D4,F4)"

H: Jack's current portion of total "=IFERROR(G3+C4,G4)"

I: Mike's current portion "=H4+E4"

In J3:S3 find where each crosses the milestones.
E.g. found where jack crosses 1k with: "=MATCH(1000,CumuTotjack,1)+1), etc

J4 compares jack/Mike 1k milestones and chooses the smaller:
"=IF(J$3<>K$3,IF(J$3<K$3,"Jack","Mike"),IF(J$3=K$3,IF(INDEX(JackEarns,J$3-1)<INDEX(MikeEarns,J$3-1),"Jack","Mike")))"

Corresponding day: "=INDEX(DepDate,MIN(J3:K3))"

data in J:S is used to fill your summary table.

OK, the only way I could get this to work was to use a helper column.

I used column E but you can use any column you want.

Enter this formula in E3:

=IFERROR(INDEX(C$3:D$22,INT((ROWS(E$3:E3)+1)/2),MOD(ROWS(E$3:E3)+1,2)+1),0)

Copy down until you start getting all 0s. I copied down to row 50.

Enter this array formula** in H5:

=IF(SUM(C$3:D$22)<G5,"",IF(MOD(MATCH(TRUE,SUBTOTAL(9,OFFSET(E$3,,,ROW(E$3:E$50)-ROW(E$3)+1))>=G5,0),2),C$2,D$2))

Enter this array formula** in I5:

=IF(H5="","",INDEX(B$3:B$22,CEILING(MATCH(TRUE,SUBTOTAL(9,OFFSET(E$3,,,ROW(E$3:E$50)-ROW(E$3)+1))>=G5,0)/2,1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select H5:I5 and copy down as needed.

You're welcome. Thanks for the feedback!