Detect if a value goes down in a row of numbers, but not if there is a zero

[Surreybloke]

I have a formula in place already that detects if a value goes down in each of the relevant cells, but the problem is that not all rows have the same number of values and so will have blanks/zeros instead. I don't need to know if there is a zero only where the value is greater than zero, but is lower than the value in the previous cell on that row.

Does anyone know of a way around this problem?

=IFERROR(IFS(J3<I3,FALSE,K3<J3,FALSE,L3<K3,FALSE),TRUE)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Some sample data and expected results would probably help clarify your requirement.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Surreybloke]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Some sample data and expected results would probably help clarify your requirement.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi,

I haven't been on the site for a while so I didn't know about this extra stuff. In this case, I'm on my work laptop, so the version of Excel is different to what I'm on at home. This is what is on my work laptop: Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20124) 64-bit

In terms of data, it's a series of values, such as 4,000, 5,000, 2,000, 0 - in this case the zero indicates that the contract length does not extend beyond the three cells where there is a value. I'll have a look at your link for being able to add the data I'm using to.

 

[Peter_SSs]

but the problem is that not all rows have the same number of values

If there are any blanks in the row, are those blanks always after any non-blank values or could a row have blanks in the middle like 4, 5, blank, 3?

 

[Surreybloke]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Some sample data and expected results would probably help clarify your requirement.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I don't think I'm going to be able to install that add-in on my work laptop due to security issues - I would likely get in trouble for doing so even though I'm sure it's completely safe as a user of this site for many years now, on and off.

 

[Surreybloke]

If there are any blanks in the row, are those blanks always after any non-blank values or could a row have blanks in the middle like 4, 5, blank, 3?

There would only be blanks/zeros after, there wouldn't be anymore actual values.

 

[Peter_SSs]

Thanks for updating your details.

I don't think I'm going to be able to install that add-in

Failing that you can copy/paste directly from excel into your post and tell us what the ranges and formulas are.

Does this do what you want?

24 03 26.xlsm
	H	I	J	K	L
3	TRUE	3	5	8
4	FALSE	8	5	2	1
5	TRUE	2	3	4	5
6	TRUE	2	3	4
7	TRUE	2	5	12	12
8	FALSE	2	5	12	11
Going Up

Cell Formulas
Range	Formula
H3:H8	H3	=MIN((J3:L3<>"")*(J3:L3-I3:K3))>=0

 

[Surreybloke]

Thanks for updating your details.


Failing that you can copy/paste directly from excel into your post and tell us what the ranges and formulas are.

Does this do what you want?

24 03 26.xlsm
	H	I	J	K	L
3	TRUE	3	5	8
4	FALSE	8	5	2	1
5	TRUE	2	3	4	5
6	TRUE	2	3	4
7	TRUE	2	5	12	12
8	FALSE	2	5	12	11
Going Up

Cell Formulas
Range	Formula
H3:H8	H3	=MIN((J3:L3<>"")*(J3:L3-I3:K3))>=0

That is perfect - I've just tested it and it works perfectly. Thank you very much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.