Find First Formula In a Range that Evaluates to a Number

Topic

New Reply

I am using Windows 8.1 and Excel 2013. All updated.

I have a range of formulas that sometimes evaluate to a number and sometimes to a blank.

I would like to find the value showing in the first cell in that range where the formula evaluates to a number rather than something else.

I have attached a contrived example.

Reply | Quote

Replies

David,

Here’s a UDF (User Defined Function) that will do the trick if you don’t mind having code:

Code:

Option Explicit

Public Function lFindFirstGT(lFValue As Long, rngSRange As Range) As Long

   Dim rngCell As Range
   
   Application.Volatile   'Note: You can comment this out if you are controlling recalcs.

   For Each rngCell In rngSRange
      If rngCell.Value  "" Then
        If rngCell.Value > lFValue Then
          'Comment out one of the following depending on what your want returned
          lFindFirstGT = rngCell.Value   'Returns Value in cell
'          lFindFirstGT = rngCell.Row     'Returns Row number of cell
          Exit For
        End If
      End If
   Next rngCell
   
End Function 'lFindFirstGT

46544-Dtufte

Test File: 46545-Excel-VBA-FFResult

Note: Application.Volatile is NOT in the sample file!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I am OK with the UDF (I will not be able to check it on the real thing until the morning).

But I am sharing the solution to this problem with folks who rely on formulas in their cells.

If anyone can do this with formulas and functions in a cell, let me know.

Reply | Quote

Boo,

If RG’s code doesn’t meet your needs, here is a non vba method using an array formula that will return the first value >3.

In cell C1 enter the formula =INDEX(B2:B7,MATCH(TRUE,B2:B7>3,0),1) then make it an array formula with CTRL-SHIFT-ENTER then copy down.

46546-boo2

On caveat: the formula cannot handle blanks so if you do not mind zeros, you can change you formulas in column B from

=IF(A2>0,A2,””) to =IF(A2>0,A2,0)

HTH,
Maud

Reply | Quote

RetiredGeek: yes, the UDF seems to work fine with the actual worksheet.

Maudibe: yes, your array formula works too with the actual worksheet.

I’m a dork: I wrote the example from memory using columns, and the problem worksheet had rows … it took me a couple minutes to figure out that the array formula wouldn’t work because of user error … an easy fix once I spotted it. But this points to the problem with using an array formula with novices: there’s a big drop off in their ability to troubleshoot when going from (standard) formulas to array formulas.

Reply | Quote

Hi Maud

..you can make your array formula handle blanks:
=INDEX(B2:B7,MATCH(TRUE,(B2:B7)*1>3,0),1)

zeddy

Reply | Quote

Good call. I use that same trick in statistics packages sometimes.

Reply | Quote

Hi boobounder

So, if you want to use a non-array formula, see attached example file.

In your example file, instead of using
=IF(A2>0,A2,””) or =IF(A2>0,A2,0)
we could use..
=N(A2)

In my example file, I went further with
=–(N(A2)>$C$1)

This uses cell [C1] to hold your check value, e.g. [C1] = 3 will return the first number > 3 etc etc.
The ‘minus-minus’ — converts TRUE/FALSE results to a 1 or 0.
(You can then sum this formula range to count how many entries are > 3 etc etc)

From Excel 2007 onwards, Excel’s sparse storage methods are now very efficient, and allow for the use of whole columns in formulas. The advantage of using whole column references is that you don’t have to adjust your formulas when additional rows of data are added or deleted. You can’t use this in Excel2003 though.
(Also, formatting an entire column does not increase the used range.)

As an example of using entire columns, look how simple this formula is:
=AVERAGE(A:A) will average all data in column [A]

You can also have
=AVERAGE(1:1) will average all data in row one.

So, my nonarray formula to find the first number greater than a specified value is
=INDEX(A:A,MATCH(1,C:C,0))

You can also use entire rows in a similar way, so I included an example of this in the attached example file.

zeddy

Reply | Quote

I think
=INDEX(A:A,MATCH(1,C:C,0))
..is easier to follow (but top marks to rory for another solution)

..and with simple modifications to the file in post#9, (using column rather than [C] ), you can get the nth value in that range which exceeds a chosen value.

For example, to get the 2nd value that is higher than a chosen value, the formula would be:
=INDEX(A:A,MATCH(2,B:B,0))

I can post another file if you need it.

zeddy

Reply | Quote

Maudibe: yes, it is OK to use the 0 rather than the blank.

**************************************

Again, I personally am OK with an array formula.

But if I am doing this for a lower level class, I need to start them out with (standard) formulas in cells. Then I say “but there’s a better way” and introduce an array formula or a UDF.

There’s a more general problem here: how do we search/find over what we see rather than what we wrote? I actually run into this quite a lot with students and stakeholders that I’m helping out … but the situation in this example has some extra bells and whistles that make it tougher and stumped me.

So this one is still open if someone wants to try.

Reply | Quote

If you add an additional INDEX to Maudibe’s approach, this doesn’t require array-entry:

=INDEX(B3:B7,MATCH(1,INDEX(N(B3:B7+0>3),),0))

Reply | Quote

Rory,

The ),) looks awful suspicious but it WORKS!

Code:

=INDEX(B3:B7,MATCH(1,INDEX(N(B3:B7+0>3[COLOR="#0000FF"][SIZE=3]),)[/SIZE][/COLOR],0)) 

Thanks for an innovative solution! :thewave: :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You folks are going to want to “kill” me.

My initial example needs to be more complex (Rory and Zeddy’s stuff is definitely on track for what I need, but running that brought up other shortcomings in my thinking).

I have learned a ton from this thread already. But it’s also pointing out that my initial question is not as well posed as I thought it was.

I have attached a better example. Should we continue here, or start a new thread?

Reply | Quote

Hi boo

Using Rory’s ‘INDEX within INDEX’ inspiration, you can use this formula in cell [E1]
=IF(SUM(C2:C11)=0,3,INDEX(D2 : D11,MATCH(1,INDEX(N(D2 : D11>-1),),0)))
..and copy this formula to cells [H1], [K1], [N1], [Q1], [T1], [W1] etc etc

I also simplified the formulas in Gathered Data block [D2 : D11] to
=IF(C2=””,-2,IF(LEN(OFFSET($A$1,C2,0))=0,-1,OFFSET($A$1,C2,0)))
..and copied this block to your other Gathered Data columns.

see attached file

zeddy

Reply | Quote

Yes. This works with the original full worksheet. Thanks for the help!

EOM.

Reply | Quote

Bingo. Going to test this on the full data set shortly.

Reply | Quote

Hi boo

Personally, I prefer to use row-references rather than offset-references.
I find it easier to check the source data (from column [A] ) if we refer to the direct row number rather than an offset.

We can still detect the difference between a zero data entry and a blank in column [A].
See adjusted version attached.

zeddy

Reply | Quote

..and, I confess, I still have no idea what you are using this for.

zeddy

Reply | Quote

Not really sure myself.

I have students that bring me stuff from their jobs. We use them as examples in class. This is from some utility construction companies HR management spreadsheet.

Most of them I can fix off the top of my head, and honestly I thought this one would take me about 10 minutes.

In a lot of cases, these are legacy workbooks that have been worked on by many people through the years. The students are trying to make their own jobs easier by using what they learn in class to clean up past nonsense (figure out how to do it right rather than twice, so to speak). But they can’t always start from scratch, or modify the workbook too much, because other people will freak out.

And, a lot of times, after they figure out how this works, then I’ll tell them to replicate it with an array formula, or VBA. But most of them can’t start with that.

So a tough one like this is pretty awesome. We will spend some time on what you called the “index within index” insight. Of course, I am always finding new uses for =n(); I just can’t seem to remember enough that this function is in there. And I still haven’t figured out the significance of the ),) that RetiredGeek noticed. Maybe this afternoon I’ll get back to that.

FWIW: there’s a different commercial real estate company nearby owned by a family who are all spreadsheet nerds (all better than me). They design huge formulas (thousands of characters) and put them into individual cells that they keep locked down. They’ve had better luck when they hire new people in having them troubleshoot those big formulas rather than VBA or array formulas that are a tenth the size.

Reply | Quote

If your students bring you stuff from their jobs, get some of them to work in a car dealership.

When I read your first post about ‘finding the first entry greater than a chosen value’, my first thought was you were treating the first patient with a blood-alcohol-level higher than the legal limit.
..or making an example of the first motorist that exceeded the town speed-limit

zeddy

Reply | Quote

..and finally, I’m a fan of using extra columns, rather than single huge formulas.

If you want to analyse a huge formula, then, in the formula bar, highlight a section of the formula and press [F9] to show the result of that part of the formula. This can be very useful in tracking down which part of a long formula is causing a problem.

zeddy

Reply | Quote

Hi

..the ),) is just missing out the MATCH type_type option (1=less than, 0=exact match, -1=greater than), so it defaults to zero, i.e. exact match.

The =N() function is the sister function to =T()
(the latter returns the text in a cell, or blank if the referenced cell contains a numeric value)

zeddy

Reply | Quote