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

Options

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

Best Answer

  • TimPeterson
    Answer ✓
    Options

    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.

Answers

  • TimPeterson
    Options

    Hello. Is Customer EE also supposed to be adjusted by 1500 in Column H? Or should the adjustment only apply to Customer CC?

  • cmast
    Options

    Yes, Customer EE would be increased by the 1500 (thus 6500 in col Q). Sales ID would use the 6500 until another adjustment, then the amount forward (column H) would be the new amount.

    I mocked up another example showing the adjustment on line 11, thus line 14-16 (Same sales id) shows the increase in Col I. Line 20-22 shows another adjustment, so Line I (from that point forward) is increased by the 2nd adjustment in col H

    One thing I added was a sequential number (Orig Source Code) from the original data source (sequential number). One thought I had taking the source code of the item and finding one that lower (and same sales id) to bring back the adjustment amount.

    For example, looking at line 14, the code shows 1500 for sales id 11. since code 400 (lower) for sales id 11 has an adjustment, then I would want to take the adjustment from line 11 and apply it to line 14.

    I'm just not sure how to do this within the same module.

  • cmast
    Options

    This is exactly what I'm looking for. Thank you !!