Counting consecutive non-zero values

Hi,

I am struggling to find a way to count a number of consecutive non-zero values across 33 columns without resorting to a horrid nested-IF formula.

I'm using Excel 2003 and each row I have is an account (there are 5572 accounts). I have been tasked to check if a customer has made 6 consecutive payments (held on each row in columns K to AQ.

If a customer has made less than 6 consecutive payments then I need to multiply the total collected by 40%. If the customer has made more than 6 consecutive payments then I need to multiply the first six payments by 40% and then each subsequent payment (consecutive or otherwise) by 20% and total the figure.

Can anyone help?

If you want to determine whether 6 consecutive payments occur anywhere in the row (not necessarily the latest payments) and non-payment is zero or blank, try this "array formula" in row 2 copied down

=MAX(FREQUENCY(IF(K2:AQ2>0,COLUMN(K2:AQ2)),IF( K2:AQ2=0,COLUMN(K2:AQ2))))>5

needs to be confirmed with CTRL+SHIFT+ENTER [hold down CTRL and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar]

gives TRUE if there are 6 consecutive, otherwise FALSE

Thats fantastic thanks - now I just need to work out a way to find the reference of the sixth consecutive payment and sum everything to the left of it (including itself) and sum everything to the right of it.

What if there are two or more runs of 6 consecutive payments (or more), are you looking for the first?

Originally Posted by daddylonglegs

What if there are two or more runs of 6 consecutive payments (or more), are you looking for the first?

Only the first one is of any consequence.