Running total with IF statements
 

Scenario:
Our company has a customer send orders with multiple products and destinations each day. We have to palletize and assign a pallet number based on the destination.
Variables:
- Column N has customers: Customers are palletized seperately.
- Column AK has points assign based on the size of the product. No pallet can hold over 150 points.
- Column AL is my attempt to have a running total that resets itself after a new customer name begins or a customers quantity hits 150. (this is the step I need help with)
CURRENT:
=IF(OR((N5=N4)),SUM(AK$5:AK5),AK5)
I have gotten the first cell of the new customer to work, but the sum function keeps summing up from the first cell because of A$5. How do I get it to recognize the first cell for a new customer e.g. CELL A33 and beginning running a sum from there? When new pallet begins, I want the sum to go 1, 2, 3, 5, etc - not 1, 176, 177, 178, 180, etc.

- Column AM is the contains a formula that palletizes orders - this will be updated once I get a correct running total figured.

Each day we receive an order - I should be able to paste any order into excel and have it calculate, this is why I need column AL to have a set formula for any given order.
N AK AL AM
CUST. PTS SUM PALLET
C1 _02 1.0 140.0 61812004
C1 _02 1.0 141.0 61812004
C1 _02 1.0 142.0 61812004
C1 _02 1.0 143.0 61812004
Z3 _03 8.0 8.0 61812005
Z3 _03 8.0 159.0 61812005
C2 _02 1.0 1.0 61812006
C2 _02 1.0 161.0 61812006
C2 _02 1.0 162.0 61812006
C2 _02 1.0 163.0 61812006
C2 _02 1.0 164.0 61812006
C2 _02 1.0 165.0 61812006
C2 _02 1.0 166.0 61812006
C2 _02 2.0 168.0 61812006
C2 _02 2.0 170.0 61812006
C2 _02 2.0 172.0 61812006
C2 _02 2.0 174.0 61812006
Z2 _03 1.0 1.0 61812007
Z2 _03 1.0 176.0 61812007
Z2 _03 1.0 177.0 61812007
Z2 _03 1.0 178.0 61812007
Z2 _03 1.0 179.0 61812007
Z2 _03 1.0 180.0 61812007


Please let me know if you have any more clarifying questions. Thanks in advance and good luck!

Running total with IF statements
 

Hi,

Am Thu, 14 Jun 2012 23:30:02 +0000 schrieb BDAvs:

Scenario:
Our company has a customer send orders with multiple products and
destinations each day. We have to palletize and assign a pallet number
based on the destination.
Variables:
- Column N has customers: Customers are palletized seperately.
- Column AK has points assign based on the size of the product. No
pallet can hold over 150 points.
- Column AL is my attempt to have a running total that resets itself
after a new customer name begins or a customers quantity hits 150.
(this is the step I need help with)
CURRENT:
=IF(OR((N5=N4)),SUM(AK$5:AK5),AK5)
I have gotten the first cell of the new customer to work, but the sum
function keeps summing up from the first cell because of A$5. How do I
get it to recognize the first cell for a new customer e.g. CELL A33 and
beginning running a sum from there? When new pallet begins, I want the
sum to go 1, 2, 3, 5, etc - not 1, 176, 177, 178, 180, etc.

try:
=IF(N5=N4,SUMIF($N$2:N5,N5,$AK$2:AK5),AK5)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you Claus! That's a big help and I'm getting closer.
Now I run the problem that once it realizes the pallet change, it continues to assign more pallets after. In the example below...the last half should be on the same pallet 61812007. The running total in AL is fine until it hits the 150 limit, then it resets and does not add from the previous line(s). Once a new customer begins the problem goes away...until it hits the 150 limit.

CURRENT FORMULA:
=IF(AND((N6=N5),(M6=M5),(SUMIF($N$2:N6,N6,$AK$2:AK 6)<=150)),SUMIF($N$2:N6,N6,$AK$2:AK6),IF((SUMIF($N $2:N6,N6,$AK$2:AK6))<150,SUMIF($N$2:N6,N6,$AK$2:AK 6),AK6))

N AK AL AM
LN CUST PTS TOTAL PALLET
05 C2 _02 1.0 143.0 61812006
06 C2 _02 2.0 144.0 61812006
07 C2 _02 2.0 146.0 61812006
08 C2 _02 2.0 148.0 61812006
09 C2 _02 2.0 150.0 61812006
10 Z2 _03 1.0 1.0 61812007
11 Z2 _03 1.0 1.0 61812008
12 Z2 _03 2.0 2.0 61812009
13 Z2 _03 1.0 1.0 618120010
14 Z2 _03 1.0 1.0 618120011
15 Z2 _03 1.0 1.0 618120012

WANT:
N AK AL AM
LN CUST PTS TOTAL PALLET
05 C2 _02 1.0 143.0 61812006
06 C2 _02 2.0 144.0 61812006
07 C2 _02 2.0 146.0 61812006
08 C2 _02 2.0 148.0 61812006
09 C2 _02 2.0 150.0 61812006
10 Z2 _03 1.0 1.0 61812007
11 Z2 _03 1.0 2.0 61812007
12 Z2 _03 2.0 4.0 61812007
13 Z2 _03 1.0 5.0 61812007
14 Z2 _03 1.0 6.0 61812007
15 Z2 _03 1.0 7.0 61812007


Thanks for any help!

BD