# Formula to see if a series of numbers is increasing in value?

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?

• By "What I would like is a formula to assess how continuous is the growth." are you looking to check if a line is monotonically increasing (or not)? Commented Dec 19, 2022 at 14:03

``````=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.

• Is there any reason to do IFS(B6<A6, FALSE, C6<B6, FALSE, ...) instead of AND(B6>=A6, C6>=B6, ...)? Commented Dec 19, 2022 at 14:41
• Nope that would work as well, no difference to the CPU load or memory load. personal preference based on what you are used to. Commented Dec 19, 2022 at 21:29
• What is A6, B6, C6, D6, E6, F6, G6, H6, I6 and J6? Are aou saying that OP should put all his thousands of cells into this formula? Commented Dec 20, 2022 at 5:56
• He already has the data in cells, if you put that formula in one cell, you can then copy it to all the other cells, and excel will change the references to the correct lines. It takes a matter of seconds. Commented Dec 20, 2022 at 11:56

You can use array formulae to accomplish this:

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.

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)`.

• Would like to add that the formulas do not necessarily need to consume additional rows, they could as well placed to the right of the columns, p.e.
– r2d3
Commented Dec 18, 2022 at 12:18
• The array solution is superior in all aspects over the first one, I think it'll be better if you put it first. Commented Dec 18, 2022 at 13:46
• The question also has a google-spreadsheets tag, so it may be worth adding the Google Sheets ArrayFormula as well. Commented Dec 18, 2022 at 16:33
• Great answer Tom, the reason I did not go with the array solution, was due to the CPU usage, and array functions update on any change to the sheet, so any time a number is changed all thousands' of arrary with be re-triggered and have to recalculate. That is why I avoid using array calculations if at all possible, a larger formula will always give better performance then arrays. Commented Dec 19, 2022 at 11:13

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.

• Nice answer! After posting mine I realised I accidentally reached the same solution as you from a different starting point. Commented Dec 20, 2022 at 4:47

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

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.

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.

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)
``````