## Wednesday, September 11, 2013

### Using Formula in Excel to Count the # of Values less than/more than zero

http://www.excelforum.com/excel-formulas-and-functions/679226-find-the-first-value-in-a-row-greater-than-zero.html

# Find the first value in a row greater than zero

1. ## 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
2. ## 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)
3. ## Re: Find the first value in a row greater than zero

Originally Posted by DonkeyOte
You could use an Array
AF1: =MATCH(TRUE,INDEX(\$A1:\$AE1>0,0),0)
This is glorious, I use it all the time, thank you!
4. ## Re: Find the first value in a row greater than zero

Fantastic! That is very useful!
5. ## Re: Find the first value in a row greater than zero

Originally Posted by DonkeyOte
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)
Thanks a lot for saving my ***!

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_TYPEBEHAVIOR
1 or omittedMATCH 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.
0MATCH finds the first value that is exactly equal to lookup_value. The values in thelookup_array argument can be in any order.
-1MATCH 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.
ABC
ProductCount
Bananas25
Oranges38
Apples40
Pears41