Apply Value only to certain list members where line item matches others in list.

cmast

Is there a way to apply a value to a certain list member and only the ones after, where a particular line item (where the adjustment was applied) is equal to?

In my example, List members are shown in order. On Cell G13, 1500 was applied to Sales ID 11. H12-H16 reflects the additional 1500.

Because Line 22-26 is also associated to Sales ID 11 and is after the list member that had the adjustment applied Col H also reflects the additional amount

TimPeterson

The function that best fits what you are looking for is RANKCUMULATE. It aggregates values over a list by groupings.

RANKCUMULATE(Cumulation values, Ranking values [, Direction] [, Include value] [, Ranking groups])

You cumulate the adjusted annual Q with ranking values of Orig Source Code (this orders them), direction is ascending, include all values (TRUE), and set the ranking groups to Sales ID.

If I were you I would probably set the original annual Q in a separate module dimensioned by just Sales ID, and pull in with a lookup against Sales ID. Otherwise I mocked up a formula to identify the original annual Q by Sales ID, then applied the Adjustment to it to come up with the Updated values.

Please let me know if this helps, or if you have any questions about what I did. There's a little flexibility in how you set up most of the build, but I think RANKCUMULATE is the function you are looking for to knock this out.