0

I was hoping to get help with the attached workbook. The end goal is to be able to flag the Customers that make up up to 70% of the rep's cummulative activity. I've done this manually in column H but would like a solid formula to get this accomplished. I'm was thinking about a stack like an Offset/Indirect/Cell"Address"/Index/Match/Match/Countifs or CountA would work but am a bit befuddled. The original source data is in columns B-D and is made up of a PowerPivot.

Any ideas through PowerQuery would be welcomed as well and I can provide any details necessary for that.

THANK YOU VERY MUCH!!!

Jordan

Workbook Screenshot

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
JCOLE
  • 1
  • so if I have this right, the list is grouped by rep 1st, and sorted in descending order by loads shipped. You want to flag the rows from the largest shipped )1st row of group) to the row exceeds row who's running total first exceeds 70% of all the groups shipments? – Forward Ed Aug 03 '19 at 17:42
  • FYI there is something wrong with your cumulative total. ROW 15 should be 100%. In row 16 the value in the cumulative row (f) should match the value in row (e) since its the first occurrence in the group. – Forward Ed Aug 03 '19 at 18:12

1 Answers1

1

You can try a fairly simple pull-down formula starting in H3:

=IF((SUMIF(B$3:B3,B3,D$3:D3)-D3)/SUMIF(B$3:B$20,B3,D$3:D$20)<0.7,"Y","")

enter image description here

(doesn't give the same results as you for ABAKER because I don't have complete data for this rep)

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • 1
    FYI if you enter 112,110, and 109 for rows 21-23 you will get the same % and results for ABAKER. need an additional 331 trips for ABAKER that are each less than 119. – Forward Ed Aug 03 '19 at 18:06
  • Tom - you rock. Thank you for helping me with this logic. Knew I was overthinking it. I believe this will work perfectly for what I was trying to get at. Again - thank you all very much. – JCOLE Aug 04 '19 at 20:14