Forum Discussion
How to assign a value to a next duplicate (sorted by dates) cell.
Hello there!
What an elegant "old-school" 🙂 solution! Thank you so much for your time! I had a feeling that it could be solved using just IF condition. It's a more programming approach to me.
Yes, you're absolutely right - I've made a mistake in the formula (legend section). My bad :-).
For entries G12-G14 and G25-G27 the table has three date entries (some days a team has more than just one task activity). As well as there some time entries discrepancies that I have to fix.
Once again thank you.
Cheers.
Do you have a sample of the worksheet you are having problems with so I can assist you better?
Thank you for your response.
I've attached a worksheet with short explanation of the problem.
After studying your table, I'm a bit confused. In your explanation to the right of the table you define OT(n) as:
[OT(n-1)-True Build Time(n-1)] - SDT(n-1)-UDT(n-1)
But when I look into e.g. cell G8 you seem to have it as:
[OT(n-1)-True Build Time(n-1)] - SDT(n)-UDT(n)
Assuming that the second formula is correct, the following formula in G2 and down, will produce the same values as you currently have. It's "old school" (as someone recently called it) as doesn't use structured table references. But it works (and I can't get it to work in any other way)...
=IF(NOT(A2=A1),D2-E2-F2,G1-I1-E2-F2)....except for cell G14, where you had entered and entirely different formula. That record was the third one on the same day, so that may explain why. But, in G27 there is another 3rd occurrence where my formula calculated the same value as yours. If a 3rd (or 4th and so on) occurrence on the same day is supposed to be treated differently from a 2nd, you need to explain the "rules" for such.
Hello there!
What an elegant "old-school" 🙂 solution! Thank you so much for your time! I had a feeling that it could be solved using just IF condition. It's a more programming approach to me.
Yes, you're absolutely right - I've made a mistake in the formula (legend section). My bad :-).
For entries G12-G14 and G25-G27 the table has three date entries (some days a team has more than just one task activity). As well as there some time entries discrepancies that I have to fix.
Once again thank you.
Cheers.
