Excel 2007 : Find next 'less than' value, ignoring blanks

**samtoucan** · 12-12-2009, 12:51 AM

Hi

Need to find the next value that is less than 'x', however it needs to ignore blanks, which is what its currently getting stuck on.

The current formula
=IFERROR(ROW(INDEX((INDIRECT("'"&$B216&"'!C"&ROW()+1&":C10000")),SUMPRODUCT(MATCH(1,--((INDIRECT("'"&$B216&"'!C"&ROW()+1&":C10000"))<D216),0)),1)),"NO SALE")

The problem is that it returns cells that have a blank(zero) in them. Is there a way to have it skip blank cells - the required value will never be zero.

Note the blank cell is a tab from a tab separated CSV file.

Cheers

Sam

**JeanRage** · 12-12-2009, 03:34 AM

Hi,

Quite difficult to decypher without your file ...
Try to add

*(A1:A100<>"")

within your Sumproduct() element ...

HTH

**DonkeyOte** · 12-12-2009, 04:03 AM

Sam, it seems as though if a value exists you're returning the row number rather than the value - is that the desired output ?

I ask because you mention

Originally Posted by Sam

Need to find the next value that is less than 'x'

If it is your intention to simply return the row number then as I see it based on your present formulae you could remove the whole ROW/INDEX element and simply add ROW() to your SUMPRODUCT result so as to return the Row number of the MATCH as
found by the SUMPRODUCT

eg

Please Login or Register  to view this content.

Are you able to post a sample file that mimics your real setup (and outline desired results) ?

**samtoucan** · 12-12-2009, 06:03 AM

Please find attached XLSX file.

Originally Posted by D.O

Sam, it seems as though if a value exists you're returning the row number rather than the value - is that the desired output ?

Apologies, you are correct - I need the row number of the value, not the value itself.

The Analysis Sheet on row 203 has this data
CP8 143.670000 143.655633
SO, got to Sheet CP8, look down Col C, and find a value less than or equal to 143.655633.
Thats the job of the formula in cell Analysis!E203, which is the one I posted above.

What is returning is CP8!206, which contains a blank. It should have returned row 207, which has a 142 value.

Any help much appreciated.

Cheers

Sam

**DonkeyOte** · 12-12-2009, 07:43 AM

Your existing approach would become:

Please Login or Register  to view this content.

However it's worth pointing out that each of these SUMPRODUCT is Volatile and each is referencing close on 20000 cells ... that's going to be very (very) bad news for the performance of your file

If I were to make a suggestion... it would be this:

Calculate the position of the "next low value" in each row on the Target sheet where you have a value in B (eg somewhere on CP8 row 204, 205 etc etc...)

You can then use a less expensive formula approach from your Summary sheet - ie along the lines of your existing LOOKUP approach in column C.

eg

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

Given the sheet variable nature of your functions you can't (with native formulae) avoid Volatility however when using Volatile approaches it is imperative that the functions used are not memory hungry - ie avoid Volatile Arrays & Sumproducts like the plague.