http://www.excelforum.com/excel-formulas-and-functions/679226-find-the-first-value-in-a-row-greater-than-zero.html
- 04-09-2009, 01:35 AM#1Registered User
- Join Date
- 11-16-2008
- Location
- Adelaide,Australia
- Posts
- 12
Find the first value in a row greater than zero
Hi,
I'm trying to find the column number of the first cell in a row that is greater than zero.
There are 31 columns of of data, so if statments aren't working for me.
any suggestions
Example
1 2 3 4 5 6 7 8 9
A 0 0 0 0 0 0 8 1 0 = 7
B 0 0 2 0 0 0 0 0 0 = 3
C 0 0 0 0 0 0 0 0 1 = 9
Thanks
- 04-09-2009, 03:18 AM#2Forum Moderator
- Join Date
- 10-22-2008
- Location
- Suffolk, UK
- MS-Off Ver
- 2002, 2007 & 2010
- Posts
- 21,509
Re: Find the first value in a row greater than zero
You could use an Array
AF1: =MATCH(TRUE,$A1:$AE1>0,0)
confirmed with CTRL + SHIFT + ENTER
copy down as required
EDIT: I spotted from a daddlylonglegs post a way to use INDEX within the MATCH and thereby avoid need for Array...
AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)Last edited by DonkeyOte; 04-09-2009 at 03:35 AM. Reason: typo & dll edit
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges - 06-14-2012, 12:42 PM#3Registered User
- Join Date
- 06-13-2012
- Location
- Hamsterdam
- MS-Off Ver
- Excel 2010
- Posts
- 17
Re: Find the first value in a row greater than zero
This is glorious, I use it all the time, thank you!
Originally Posted by DonkeyOte 
- 08-02-2012, 11:54 AM#4Registered User
- Join Date
- 06-15-2012
- Location
- Sacramento, CA
- MS-Off Ver
- Excel 2010
- Posts
- 7
Re: Find the first value in a row greater than zero
Fantastic! That is very useful!
- 08-06-2013, 01:29 PM#5Registered User
- Join Date
- 08-06-2013
- Location
- poznań
- MS-Off Ver
- Excel 2010
- Posts
- 1
Re: Find the first value in a row greater than zero
Thanks a lot for saving my ***! Originally Posted by DonkeyOte
http://office.microsoft.com/en-us/excel-help/match-function-HP010062414.aspx
MATCH function
This article describes the formula syntax and usage of the MATCH function in Microsoft Office Excel.Description
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula=MATCH(25,A1:A3,0)returns the number 2, because 25 is the second item in the range.Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_numargument of the INDEX function.Syntax
MATCH(lookup_value, lookup_array, [match_type])
The MATCH function syntax has the following arguments:- lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.- lookup_array Required. The range of cells being searched.
- match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matcheslookup_value with values in lookup_array. The default value for this argument is 1.
The following table describes how the function finds values based on the setting of the match_type argument.MATCH_TYPE BEHAVIOR 1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in thelookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. 0 MATCH finds the first value that is exactly equal to lookup_value. The values in thelookup_array argument can be in any order. -1 MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on. NOTES- MATCH returns the position of the matched value within lookup_array, not the value itself. For example,MATCH("b",{"a","b","c"},0) returns 2, which is the relative position of "b" within the array {"a","b","c"}.
- MATCH does not distinguish between uppercase and lowercase letters when matching text values.
- If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
- If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Example
The example may be easier to understand if you copy it to a blank worksheet.1 2 3 4 5 6
7
8
9A B C Product Count Bananas 25 Oranges 38 Apples 40 Pears 41 Formula Description Result =MATCH(39,B2:B5,1) Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned. 2 =MATCH(41,B2:B5,0) The position of the value 41 in the range B2:B5. 4 =MATCH(40,B2:B5,-1) Returns an error because the values in the range B2:B5 are not in descending order. #N/A Did this article help you?
Reply With Quote
Show All
No comments:
Post a Comment