-1

I would like to create a metric that will work for each risk ID. The risk contains 4 phases: 4 rows. After the 4th row. I want the function to consider a new risk. What's the best way to kind of group the spreadsheet into buckets of 4 rows; each bucket for a risk and have the function reinitialized after the 4th row. I tried to use the group button but it doesn't seem to work when I do the function.

enter image description here Please find the spreadsheet attached for more details. It shows also the desired output.

Thank you for your suggestions,

Faical
  • 45
  • 7
  • 1
    That's easier to answer, if you can give the formula you're using. Also, will the phases always be consecutive? – Mistella Apr 17 '19 at 19:10
  • Phases: I created a custom sort. So I dont know how they will behave when data is refreshed but for now, Yes, they are always consecutive. The function will be: for the 1st row(scope): =days past due(scope) / for the second row (plan): = 1st row + 2nd row // for third row ( Execute) = 2nd row + 3rd row // for 4th row (operate) = 3rd row + 4th row . – Faical Apr 17 '19 at 19:13
  • Sorting note: if you're using PQ, you can sort the data as the last step, and it should load sorted. – Mistella Apr 17 '19 at 19:24
  • I am using PQ, I will try to load it sorted. – Faical Apr 17 '19 at 20:47
  • Since you're using PQ, assuming that the "Days Past Due" column is part of what PQ loads, you may be able to do it within PQ. Since I believe it's essentially a running total by groups, you could check out this [answer](https://stackoverflow.com/a/34348277/9259306) – Mistella Apr 17 '19 at 21:08

1 Answers1

0

From the image, description, and assumption that the phases for each risk will always be consecutive; I believe you want a formula like the following (assuming it will initially be put into cell "I2"):

= IFERROR(IF(A2=A1, I1 + G2, G2),G2)

What this formula does, is two-part:

  1. IF(A2=A1, I1 + G2, G2) Checks that the Project column for the current row (2) matches the Project from the previous row. If it matches, add the current row's "Days Past Due" to the previous row's "New FUNCTION". If it doesn't, just use the current row's "Days Past Due"

  2. IFERROR(..., G2) will return the current row's "Days Past Due" if there is an error in the inner IF-function. This would primarily occur in row 2, because row 1 is text (column header).

Mistella
  • 1,718
  • 2
  • 11
  • 20
  • Thank you for your suggestion but that doesn't fix the main issue: having the function reset for every 4 rows. – Faical Apr 17 '19 at 20:48
  • @Faical Not directly, but it should "reset" every time the value in the Project column changes, which looks like it happens every four rows. Could you try using it, so you can give me feed-back on when it doesn't give the values it should? Right now, I believe it should do as requested, so some explanation on where it isn't would be helpful. – Mistella Apr 17 '19 at 21:02
  • @Faical If you truly want a series of functions that repeats every four rows, you may need to use VBA (or PQ) to just reset it. – Mistella Apr 17 '19 at 21:05