Formula to count number of cells that are greater than the one before it

[deraj727]

Hey,

Im looking for a formula to count the number of times that a cell is greater than the one before it. Below is a simplified version. But that answer in this case would be 3.

Thanks for your help!!


0
0
0
0
0
0
0
0
0
0
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
3
3
3
3

[FlameRetired]

Try array entering this.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[FlameRetired]

Another way still array entered

Please Login or Register  to view this content.

[Tony Valko]

With your data in the range A2:A35...

=SUMPRODUCT(--(A3:A35>A2:A34))

Note how the ranges are offset.

[Phuocam]

Regular formula ...

=SUMPRODUCT(--($A$1:$A$33<$A$2:$A$34))

[deraj727]

Thanks, that worked perfectly. Do you know how to base it on a criteria. So that it counts the number of increases based on the left columns. So in this case for 0.1=1., 0.2=1 and 0.5=2.

Thanks!!

0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.5 3
0.5 3
0.5 4
0.5 4
0.5 4

[Phuocam]

Try this ...

=SUMPRODUCT(--($A$1:$A$33<$B$1:$B$33))

[FlameRetired]

Not certain I follow your meaning, but try array entering this.

Please Login or Register  to view this content.

[FlameRetired]

I think I see it now. Just change Tony and Phuocam's formula to this.

Please Login or Register  to view this content.

It returns 4.

[deraj727]

Thanks for your reply, but i don't think I made my meaning clear haha

I am trying to use that sumproduct formula. But to count the number of cells based on another criteria (the probabilites eg 0.1, 0.2, 0.5) Hopes that makes a bit more sense!!

Thanks

[FlameRetired]

Then does this do it?

Please Login or Register  to view this content.

[deraj727]

Unfortunately not.

I have a table at the top of the spread sheet that looks like this. And because the probability changes (.1,.2, .5) want to make a formula to automatically count the number of increases in the column at certain probabilities. So how many increases there are when the probability is .1. How many when the probability is .2 ect. Does that make sense?

Items
.1 1
.2 1
.5 1

[FlameRetired]

Unfortunately not.

I have a table at the top of the spread sheet that looks like this. And because the probability changes (.1,.2, .5) want to make a formula to automatically count the number of increases in the column at certain probabilities. So how many increases there are when the probability is .1. How many when the probability is .2 ect. Does that make sense?

Items
.1 1
.2 1
.5 1

So where does this from post #6 fit in?

So in this case for 0.1=1., 0.2=1 and 0.5=2.

[deraj727]

that was in example of the correct answer if the data was. So the table (Items is a summary of the data
.1 1
.2 1
.5 1)

0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.5 3
0.5 3
0.5 4
0.5 4
0.5 4

[FlameRetired]

I've fired off a help flare ... community help. I am too confused.

[deraj727]

haha, fair enough, ill summarize the problem.

At the top of the excel spread sheet is a table that summaries the data.

Probability Number of increase
0.1 1
0.2 1
0.5 2

Then there is the data below with 2 column. The probability and number column. I want a formula that automatically calculates the number of times a cell is greater than the one before it. With the help of this forum that has been achieved. But I also want to count the number of increases for each range of probabilities. So how many times is a cell bigger than the one before it when the probability is .1. How many when the probability is .02. In the example below the correct answer is that when the probability is .1 the answer is 1. Probability is .2 the answer is 1. And when the probability is .5 the answer is 2.

0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 0
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.1 1
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.2 2
0.5 3
0.5 3
0.5 4
0.5 4
0.5 4

[Crooza]

This will give you the answers you want but I'm not convinced it's right!! Note you can put 0.1, 0.2 or 0.5 into D1 to get the appropriate result

=SUMPRODUCT(--($B$1:$B$34<$B$2:$B$35),--(D1=$A$2:$A$35))

I would have thought the number of changes for 0.1 was 2 not 1 and for 0.5 the answer was 1 change not 2 so the formula I would use is

=SUMPRODUCT(--($B$1:$B$34<$B$2:$B$35),--(D1=$A$1:$A$34))

[Crooza]

Infact further to my last post I reckon this is the true formula which gives 1 change for 0.1, 0 changes for 0.2 and 1 change for 0.5

=SUMPRODUCT(--($B$1:$B$34<$B$2:$B$35),--(D1=$A$1:$A$34),--(D1=$A$2:$A$35))

[snb]

What 's the purpose of all this ??