-1

Edited per teylyn's comment, plus I realized there was an error in the original screenshot image. Sorry about that.:

I'm trying to use column D to distribute "exceeded" amounts from column C like an overflow. The image shows the kind of scenarios that are common where sometimes

I need a cell to distribute down, sometimes up and sometimes both up and down.

The arrows in the image show the original value it's distributing across the adjacent column and the direction in which it's distributing (up, down or both).

teylyn asked: "What have you tried - this isn't a freeloader site!" (my word's not his but to that extent - he wasn't being rude.) That's completely understandable, but it's also extremely difficult because I've tried so much in several helper columns, and then trying to get col D to determine which of the adjacent cell/cols to use. I can get it to distribute in one direction - I've settled on down, but I had up at one time, but I can't seem to do both.

Here's what I tried that eventually... Okay, it's getting too late, so I'll have to come back tomorrow and finish this to explain the tremendous amount of things that I tried, but basically, it's a bunch of helper cell using =IF(This>=That,This,That,OrTheOtherThing) and then summing them several columns downs for column D to reference. Like I said, it will work distributing down but not up or both up and down when it should.

Overflow Scenarios

Jeff Jensen
  • 65
  • 6
  • This requires recursion, which is probable not possible to do with Excel formulas. You'd need to do that with VBA. Oh, and the always popular: What have you tried? Because this site is not a free script writing service. – teylyn Sep 11 '18 at 04:56
  • 1
    @Jeff - what decides whether it's "distributing" up or down? – ashleedawg Sep 11 '18 at 09:01

1 Answers1

1

I'm unclear on what logic would decide when it "distributes up" so perhaps this down-only method will get you started and help you figure out which logic is, well, logical.

It's just a running total that deducts the sum of the cells "above" the current cell.

In cell B1: =IF(SUM($A$1:A1)>48,48,SUM($A$1:A1)-SUM($B1:B$1))

In cell B2: =IF(SUM($A$1:A2)-SUM($B$1:B1)>48,48,SUM($A$1:A2)-SUM($B$1:B1))

..then copy or fill B2 down to the rest of the column.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • First of all, thanks for your help here. I really do appreciate it! The formulas you provided are way better than the several helper columns I had going - I think I over complicated it. I had a method that went up, down and both that I used several years ago at my old job but wont let access get it from them. As for logic: It should go up whenever there's less than 48 above (which I call "enough room") and spill whatever doesn't fit upstream into the nearest cells downstream. I got help with this years ago from one of the MVP's in the Microsoft excel forums - I can't find those forums anymore. – Jeff Jensen Sep 11 '18 at 21:04