Combined ARRAY and IF function

**z3tls0** · 12-07-2010, 01:25 PM

I hope I don't confuse you with this question?
Currently I'm using this ARRAY to find a MAX value accoss several columns.
=MAX(FREQUENCY(IF(G6:AH6=1,COLUMN(G6:AH6)),IF(G6:AH6<>1,COLUMN(G6:AH6)))) Example: 0 1 1 1 0 1 1 0 1 1 1 0 1 1 0 1 1 1 0 1 1 0 1 1 1 0 1 1 = 3
0's represent the days a store orders product from a supplier. This could vary by store.
Some stores may order more often or less and on different days. 1's represent the amount of days between orders.New Challenge: The example ARRAY above displays only the days between 1 order. IF a store wants to calucalate the days between 2, 3 or 4 orders using a drop down combo box, how do I write an IF function to look back past the 1st, 2nd and 3rd 0's and still calculate the days between orders? Your assistance would be greatly appreciated.

**NBVC** · 12-07-2010, 02:12 PM

Can you give an example of what is selected in the drop down and what would then need to be considered in the sample set you show, as well as the outcome.

Also, is there always one 0 in between the 1's and if not, how to consider those?

**z3tls0** · 12-07-2010, 02:54 PM

The drop down example would be a qty of 1,2,3, or 4. Two, 3 and 4 is where this becomes challenging. There should always be a least 1 zero in the ARRAY If not, I have written a function for that. Is it possible to write an IF function to change the the 1st zero after the MAX value to 1 if 2 is selected from the drop down? This could be repeated for 3 and 4 also.