'IF(AND())' advice....

[lucraft]

I am trying to create an index number and have the following IF statement:

=IF(AND(B1=B2,YEAR(C2)>2009),MAX($A$1:$A1)+1,IF(AND(B2=B3,YEAR(C2)>2009),MAX($A$1:$A1)+1,IF(YEAR(C2)>2009,MAX($A$1:$A1)+1,0)))

It seems long, but it works. However I would like to adapt the MAX($A$1:$A1)+1 to reset itself to 1 when something changes in one of the columns.

My explanation is a bit vague, hopefully the attached should explain a bit more

Any help much appreciated.

Thanks
Lucraft

[JBeaucaire]

Try this in B3 and copied down...

=IF(D3=0, 0, IF(C2=C3, LOOKUP(2, 1/($B$1:$B2>0), $B$1:$B2)+1, 1))

[lucraft]

Try this in B3 and copied down...

=IF(D3=0, 0, IF(C2=C3, LOOKUP(2, 1/($B$1:$B2>0), $B$1:$B2)+1, 1))

That seems to do the trick. How does it work??

[JBeaucaire]

The first part - If column D = 0, show 0.

The second part - if C2 does NOT equal C3, start a new list by entering 1.

If C2 DOES equal C3, then look UP column B for the last non-zero number and add 1.


LOOKUP(2, 1/($B$1:$B2>0), $B$1:$B2) ... this is the magic part. LOOKUP() has a unique characteristic in that it will ignore errors automatically without us having to do extra formulas, so it's a great function to use when you can, in this case we can.

This formula says - create an array of values listing all the cells above in column B that have a value other than 0. We then divide all the numbers in that array by 1 changing the array to series of numbers that are less than 1 (fractions) or errors. LOOKUP() expects arrays to be ordered ascending, so now it goes looking for the number "2" in that list, or it returns the last number it finds in the list less than 2. Since ALL the numbers in the array are less then one, it always finds the last number in the array.

Then we added 1 to it.

[lucraft]

Thats Brilliant, thanks for your help

[JBeaucaire]

Last tip... click on...oh... B8 and use the Evaluate Formula icon on the Formula Auditing toolbar to step through the formula. Watch it unfold one calc at a time, soon it will be clear exactly how it's doing it.

[lucraft]

Thanks for the advice

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[lucraft]

Putting the formaula into use I've come across a problem....

If the first index of an area is zero it adds 1 to the total. See attached.

Any ideas?

[JBeaucaire]

What should the results be? Use another column. Any more hurdles you need to add now?

[lucraft]

In the attachment above, if you look at Area column, and find Bearstead; The second instance of Bearstead has a date and first does not but instead of creating an index of 1 it adds 1 to the total of the last Area index.. returning 59?

[JBeaucaire]

Assuming the new sheet you uploaded now demonstrates all the problems that have to be dealt with, use an empty column to fill out exactly and completely how you want it numbered and explain anything that isn't glaringly obvious, explain it in your sample sheet.

[lucraft]

My apologies, I thought there would be enough information on the sheet.

Have a look at rows 73 & 74 in the attached.

[JBeaucaire]

Try this in B3, copied down:

=IF(D3=0, 0, IF(C3<>C2, 1, MAX(INDEX($B$1:$B2, MATCH($C3, $C$1:$C2, 0)):INDEX($B$1:$B2, MATCH($C3&"zzz",$C$1:$C2,1))) + 1))

[lucraft]

Thanks!