8

I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers.

What I want to do is check whether the numbers on each row is increasing or not. For example, imagine this is row one (with each number being a separate column):

13. 17. 25. 37. 39. 40. 53. 61. 68. 71

As you can see, the numbers are all increasing. Now let's imagine that this is row two:

13   5.  3. 18. 34. 17. 49. 83. 63. 71

In this case the starting and ending numbers are the same as the first set of numbers, but the path taken is quite different.

What I would like is a formula to assess how continuous is the growth.

Any ideas?

Moshe
  • 225

4 Answers4

31

You can use array formulae to accomplish this:

Array formula

Assuming your data is in A1:J1, then enter the following formula into a cell =AND(B1:J1>A1:I1) and press Ctrl+Shift+Enter to create an array formula - notice that excel will wrap the formula in {} automatically (you can't do the wrapping manually!). In Google Sheets, you can use ArrayFormula inside of the AND function instead of the excel shortcut: AND(ArrayFormula(B1:J1>A1:I1)).

This will do element-wise calculations of >, and then check that they are all true. Notice that the two ranges are offset by one cell, but are the same length, so B1 and A1 will be compared, C1 and B1 compared, and so on. Then the resulting array will be passed in to AND to check they are all true.

Once the array formula is inserted for the first row, if you have multiple rows, you can simply drag the formula down for each row.

Alternatively, if for some reason you don't wish to use arrays, you can do this without by adding another range of cells below or beside (or anywhere to be honest) the list of numbers. In this extra range of cells, you can do the comparison of each cell.

Comparison formulea

With the data starting at A1, then, in say cell B2, add the formula (B1 > A1), then drag the formula across to match the width of the data (formulas will update references automatically). Finally you just need to check if all of those cells are true which can be done with AND(B2:J2).

Tom Carpenter
  • 1,791
8

Since you tagged google-spreadsheets, you can also use an ArrayFormula to simplify this in one formula:

=AND(ARRAYFORMULA(B1:J1>A1:I1))

You can adjust the J1 and I1 accordingly if the number of rows changes.

If all columns to the right are blank, you could do =AND(ARRAYFORMULA((B1:Z1>A1:Y1)+(ISBLANK(B1:Z1)))) which will automatically work on up to 26 columns as long as whatever columns follow are blank.

thshea
  • 181
2
=IFERROR(IFS(B6<A6,FALSE,C6<B6,FALSE,D6<C6,FALSE,E6<D6,FALSE,F6<E6,FALSE,G6<F6,FALSE,H6<G6,FALSE,I6<H6,FALSE,J6<I6,FALSE),TRUE)

I believe that would be the easiest way to do it.

IFS checks each number against the prior number, if it is lower then the prior, it will return FALSE.

If all the numbers are greater then the prior number, then IFS will not produce an output, causing an NA error.

ISERROR will convert the NA Error to TRUE, meaning the numbers are all increasing.

To get the Count of how many numbers are greater then the prior number in the sequence:

=COUNTIF(B11,">" & A11)+COUNTIF(C11,">" & B11)+COUNTIF(D11,">" & C11)+COUNTIF(E11,">" & D11)+COUNTIF(F11,">" & E11)+COUNTIF(G11,">" & F11)+COUNTIF(H11,">" & G11)+COUNTIF(I11,">" & H11)+COUNTIF(J11,">" & I11)

Each count if, compares the current number to the prior number, and will only count if the number is greater, then add all the counts together.

Luke Attard
  • 671
1

These answers will work if you have dynamic array formulas available in your version of Excel.

New, simpler answer:

You can use a dynamic array formula to check if each element is strictly less than an element in another array.

=AND((A1:I1) < (B1:J1))

This is nice and simple and basically says, is A1 < B1 and B1 < C1 and so on to the end of the array.

The trick is that we offset the two lists so we're comparing each item against the next value in the original array.

So we're comparing A1:I1 to B1:J1 - so the first array is from the first item to the second-last, and the second array is from the second item to the last.

Original answer:

If the list is allowed to be in >= order (as opposed to strictly >) - i.e. if 1, 2, 3, 3, 4 is also valid.

You can sort the values and compare the sorted values against the original values.

If the original values are in ascending order then the sorted list will be equal to the original list.

Array sorting example

This will sort by columns, compare each value, and then AND() the list to a single result:

=AND(SORT(A1:J1,1,1,TRUE)=A1:J1)

If you are comparing a single column of data then it's slightly simpler:

=AND(SORT(A1:A10)=A1:A10)
Sly_cardinal
  • 271