Give values to some elements

[caldera55]

Hello,
I would like to make a formula for the next:
I have seven differents elements: G1,G2,G3,G4,G5,G6,G7.
They are in column A in a random order.
What I want is that each time an element has been some rows without appear, it takes a different value. If it appears at the 13th time (from the start of the list, or from another row with the same element), in column B puts “5”, the 14th time puts “4”, the 15th time value “3”, the 16th “2”, the 17th “1”, the 18th “0” or no value, and from the 19th the value is “-6”.

For example, We have G1 in row 1,2,5,12,25,40, 67. In the row 1,2,5 and 12 we don't have any value, because the rows between this repetitions doesn't reach the 13th time. But in the row 25 we have 13 rows, and then we have in the column B the value 5. In the row 40 we have 15 rows between, then we have value 3 in column B. But in the row 67 we have 27 rows between, then, because it is more than 19 rows between, we have a value of -6.

I attached a worksheet as example of what I'm looking for. In this I doesn't put the results of the G7, because I'm not interested in this results, just for the G1 to G6.

Thank you very much for any help.

[lecxe]

Hi caldera55

In your example sheet, enter in C2:

=IF(ISNA(MATCH(A2,{"G1","G2","G3","G4","G5","G6"},0)),"",CHOOSE(MATCH(ROW(A2)-MAX(IF($A$1:A1=A2,ROW($A$1:A1))),{0,13,14,15,16,17,18,19}),"",5,4,3,2,1,0,-6))
confirm with CTRL+SHIFT+ENTER, not just ENTER.
Copy down

Comparing with your answers in column B there are (I believe yours) inconsistencies in rows 62,86,90,105. Please check.

HTH
lecxe

[caldera55]

Hi Lecxe,
it works great!

The inconsistencies in rows 62,86,90,105 are mine. Now it's better.

Thank you very much

Caldera55