Cumulative percent complete

**zooom** · 01-08-2011, 04:03 AM

Please see attached the JPEG and Excel files.

I have four columns in the excel spreadsheet. They are;
1 - Seq #
2 - % Chg
3 - *** % Chg
4 - For Seq#

The formula should start calculting cummulative percent complete from next row (for example for sequence # 1 - cummulative should begin from row #2 as highlighted in red in attached excel sheet) and keep on calculating *** % complete till the criteria of = or > 10% is achieved then the cummulative percent complete value is displayed in *** % Chg column and the sequence number for which cumulative percent change criteria is met should appear in For Seq# column.

Your help will be highly appreciated.

Thanks

Zooom

**DonkeyOte** · 01-08-2011, 05:17 AM

zooom, welcome to the board.

To be honest, in my opinion/experience, you'd be better off storing the cumulative % in each cell and using Custom Formatting to hide those % < 10%

The reason for this is that in turn you avoid a lot of "double evaluation" and permit very straightforward/efficient calculations:

What I remain a little unclear on is the Seq# to be returned.

The first sequence is straightforward enough, however, where would the 2nd occurrence take place - my guess would be row 21 but what would be the sequence no. 6 or 7 ?
I guess I am unclear as to why the first entry is omitted altogether.

In terms of explaining basic thought process...

Please Login or Register  to view this content.

applying a Custom Format to C2:C256 of: [<0]"";[<0.1]"";0.00%

I very much suspect the above will need tweaking but we need to see a few more expected results (2nd seq, 3rd seq. etc...) to fully understand the minute detail of your requirements.

**zooom** · 01-08-2011, 01:06 PM

Thanks for welcome on forum and your response. Regarding results of seq #2 and seq # 3, please see attached the excel file. I hope that will help to understand the problem.

I am trying to use what you have suggested and will get back to you.

Thanks

**DonkeyOte** · 01-08-2011, 01:11 PM

OK, so I think you're saying you're looking for a result for each seq # (1 to n), correct ?

Your latest sample raises a fundamental point as yet uncovered:

Q: what happens where you have multiple sequences achieving >= 10% at the same point ?
In your example SEQ 1 & 3 both occur at the same point.

**zooom** · 01-08-2011, 08:48 PM

Thanks my friend,

OK, so I think you're saying you're looking for a result for each seq # (1 to n), Yes that is correct

Q: what happens where you have multiple sequences achieving >= 10% at the same point ?
In your example SEQ 1 & 3 both occur at the same point. Good catch my friend. For multiple sequences meeting criteria at same point results will be displayed in next two columns titled *** % Chg and For Seq#. I dont have any better way to handle this. Please suggest if something can be more efficient.

Looking forward for your expert advise and opinion.

**DonkeyOte** · 01-09-2011, 07:36 AM

If by the above you're implying delimited strings in both % Chg & Seq columns then I would say a VBA approach is something of a no-brainer
(a formula driven approach would be both convoluted and inefficient)

Taking the VBA route further... given the nature of the process I would err towards a Sub Routine (run as and when) as opposed to UDF [repetitive calls & overhead]
(you could add ChangeEvent to invoke the Sub Routine if necessary)

Using your most recent file as example below would be one possible route:

Please Login or Register  to view this content.

the above would be stored in a standard module and run as and when (or invoked via Event as nec.)

I have taken the liberty of attaching your data with above code in place and with button present on sheet used to invoke the routine.
(you must ensure Macros are enabled of course)