Running Total and Resets

**HyMay1180** · 04-18-2011, 12:19 PM

Hello All,

Pleased to be here. I'm a bit confused how to accompish a complex running total. I attached my example file for review. Currently in Column S I have a running total, of what is in Column I, it resets when column N = "Change". I would like it to reset to column I also when Column S is equal to or greater than 60 and if column K has changed. If column K hasn't changed keep the running total going. I hope I was able to explain myself. If I can explain further if you need it. Thanks in advanced.

James

**ChemistB** · 04-18-2011, 01:12 PM

Does this do what you want?
In S2;
=IF(OR(O2="Change",S1>=60,K2<>K1),I2,S1+I2)

**HyMay1180** · 04-19-2011, 11:28 AM

Originally Posted by ChemistB

Does this do what you want?
In S2;
=IF(OR(O2="Change",S1>=60,K2<>K1),I2,S1+I2)

Thanks Chemist. I'm working through your formula, I think you meant O2 to be N2? Thanks.

**ChemistB** · 04-19-2011, 01:01 PM

Yes, sorry about that.

**HyMay1180** · 04-19-2011, 03:20 PM

Hi Chemist. Thanks for the formula. It is reseting too many times. It is reseting everytime column k is changing. I don't want it to reset every time k changes.

If N is equal to "Change", that means the order has changed so restart the running total every time. But if N is not equal to "Change" then the order is not the same so continue the running total, only reset the count when column s is greater or equal to 60 and if column k has changed.

Order of operation:

First look at column n to look for "Change"
Then look at column s for 60 or greater
Then look at column k for a change

Thanks for you help.

**HyMay1180** · 04-19-2011, 03:21 PM

Originally Posted by ChemistB

Yes, sorry about that.

Hi Chemist. Thanks for the formula. It is reseting too many times. It is reseting everytime column k is changing. I don't want it to reset every time k changes.

If N is equal to "Change", that means the order has changed so restart the running total every time. But if N is not equal to "Change" then the order is not the same so continue the running total, only reset the count when column s is greater or equal to 60 and if column k has changed.

Order of operation:

First look at column n to look for "Change"
Then look at column s for 60 or greater
Then look at column k for a change

Thanks for you help.

**ChemistB** · 04-19-2011, 03:33 PM

First look at column n to look for "Change"
Then look at column s for 60 or greater
Then look at column k for a change

So do all 3 conditions need to be true for the reset?
Or either Condition 1 or
Condition 2 AND condition 3?

**HyMay1180** · 04-19-2011, 03:50 PM

Condition 1 has priority, whenever this condition is met reset everytime. Condition 2 and 3 have to be true together. Thanks

**ChemistB** · 04-19-2011, 03:51 PM

How about this?

=IF(OR(N2="Change",AND(S1>=60,K2<>K1)),I2,S1+I2)

**HyMay1180** · 04-20-2011, 12:59 PM

Hi Chemist,

I really appreciate your help. The formulas are good, but then I get some unexpected results in some cases, which is okay because you going off what I'm saying. I have attached a new document with the desired results so that might help us out. Desired results are in column T. Again, thanks.

James

**ChemistB** · 04-20-2011, 02:58 PM

Okay, I put my equation in Col T (adjusted accordingly). First question, why do you expect a change at Row 21?

**HyMay1180** · 04-20-2011, 03:49 PM

Sorry it was a typo. The formula works perfectly! Yes! Thanks for all your help!

Running Total and Resets

LinkBack

Thread Tools

Rate This Thread

Display

Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Re: Running Total and Resets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions