Counting new record years correctly

See attachment.

2 issues.

1. The formula counts J2 as a new record year even if there was no dividend paid any previous years. I want to start to count on K2 instead as this is the first year it records a year that has a higher number than the previous year.

2. It counts L2 as a new record year even if it was L1 was identical in value as K1. I want it to only count if the next number is higher than the previous number AND higher than the previous highest number in the dataset.

Example of dataset:

Year: 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10
Dividend: 0 - 1 - 2 - 3 - 4 - 3 - 5 - 6 - 7 - 7
Number of years of new record dividend: 1 - 2 - 3 - - 4 - 5 - So this should be counted as 5 new years of record dividend.

Hope this helps.

The current formula I use now for Number of years of new record dividend is =IF(MAX($B158:J$158)=J158;MAX($A159:I$159)+1;"")

I want it to only count if the next number is higher than the previous number AND higher than the previous highest number in the dataset.

You don't need the portion before the AND, since if the second part is correct, "higher than the previous highest number in the dataset" means it will be higher than the previous by default.

In your example in your description (not your attachment), you're saying you don't want to count year 2 as the highest even though 1 is greater than 0, right? Even if this is correct, you're only showing 5 new years of record dividend. Why isn't it 6? To me it seems that years 3,4,5,7,8,9 (6 years) all have new highs.

Under your first year, you don't need a formula as this will never be a number. Therefore under your second year, you can use what you have but change the first range to one cell BEFORE your current cell, and use < instead of =. For instance, try this where your formula above is (then copy across to all years but year 1).

(changes in RED)

=IF(MAX($B158:I$158)<J158;MAX($A159:I$159)+1;"")

Thanks. You are absolutely correct. My mistake.

In the dataset above I want it to be recorded 6 years of record dividends. Starting to count at year 3 in this example. The increase from 1 to 2. If year number 1 had a number higher than 0 and year 2 dividend was higher than year 1 dividend, I would want it to start to count a new record year at year 2.

I tried you formula but it didn't work. I think I did something wrong in the "dividend" attachment. I tried to adjust your formula to my cell, because with your formula it was covering too many cells that I was not using etc..

So I will attach another excel file with a dataset and my modification of your formula. Can you please see what is wrong? Thanks.

? I use 2013, which is written in my profile

Since you say that you don't want to show an increase in year 2 if year 1 is 0 or blank, the only thing I would add to Glenn's formula is an IF to check that:

=IF($B$8=0,"",IF(C8>MAX($B8:B8),MAX($B12:B12)+1,""))

Originally Posted by Glenn Kennedy

?? c12, copied across. refer to the file.

=IF(C8>MAX($B8:B8),MAX($B12:B12)+1,"")

This formula works. Thanks!

Originally Posted by Gregb11

Since you say that you don't want to show an increase in year 2 if year 1 is 0 or blank, the only thing I would add to Glenn's formula is an IF to check that:

=IF($B$8=0,"",IF(C8>MAX($B8:B8),MAX($B12:B12)+1,""))

Ufortunately when I use your formula and drag it out it only gives me blank cells for the whole dataset.

That's because I put an anchor on the cell and shouldn't have. Should have been:

=IF(B8=0,"",IF(C8>MAX($B8:B8),MAX($B12:B12)+1,""))

This assumes that once you start having dividends, it never goes to 0.

Originally Posted by Gregb11

That's because I put an anchor on the cell and shouldn't have. Should have been:

=IF(B8=0,"",IF(C8>MAX($B8:B8),MAX($B12:B12)+1,""))

This assumes that once you start having dividends, it never goes to 0.

Now it worked. However it might be that there will be a year with 0 in dividends. (after some year with dividends) Then I noticed it didn't count the next year with 5 in dividends as a new record year.
Is it possible with a solution for that scenario?

@stockman,

Then use this:

=IF(B8="","",IF(C8>MAX($B8:B8),MAX($B12:B12)+1,""))

This works when the first cell is blank.
If there is a 0 in the cell it counts already the first number after that as a new record year.
Actually in my case no dividend will be market with a ––
So how to not make it count the first number after either a blank, 0 and -- ?

Originally Posted by Glenn Kennedy

Stockman... who are you talking to? If you don't say.. we can't tell!

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Sorry. The message was a reply to @Gregb11

I think you should use this then:

=IF(B8="––","",IF(C8>MAX($B8:B8),MAX($B12:B12)+1,""))

And always mark where no dividend offered with "––"

You can't say if there is a 0 not to count it because in Post #12 you said to count if prior year 0. It needs a way to differentiate between a dividend of 0, and no dividends offered.

Pick a method to differentiate it and stick with it. If you want to include blanks as well, then try this:

=IF(OR(B8="––",B8=""),"",IF(C8>MAX($B8:B8),MAX($B12:B12)+1,""))

Glenn,

Apologies. It was that it began to count the first positive year of dividend as a new record year when the first year was 0 or blank. I wanted it not to count unless the first number was a positive number.

However after thinking about it for some time I rather want to go with the original solution that you have. That it counts the first positive year as a new record year.

So I consider the case solved. Thanks you very much for your help both of you!