Find first non-zero value in the result from an array formula 
Author Message
 Find first non-zero value in the result from an array formula

I have a table of repeating columns:

Heading1...HeadingN,Heading1...HeadingN,Heading1...HeadingN, etc

I am using a array formula

{=sum(if(row with headings="Heading3",1,0)*Row with values under headings)}

to pull out all the values under say Heading3 which gives me:

{0,0,0,0,0,0,0,0,0,0,0,26,0,0,0,0,0,56,0,0.....}

(the first three values from Heading3 are zero in this example...)

Now this was fine when I wanted to sum all the values under Heading3 in
order to test whether the row needed processing.

But now I find that I need to pull out the first non-zero value from the
values under Heading3.

Is it possible to pull out the required value from the array of values like
the one above???

OR is there another way that I have missed????

Thanks in advance

DG



Thu, 07 Jul 2005 19:59:26 GMT  
 Find first non-zero value in the result from an array formula
Let A2:I3 houses the following sample...

={"Heading1","Heading2","Heading3","Heading1","Heading2","Heading3","Heading
1","Heading2","Heading3";5,6,"",7,4,8,7,6,4}

where "" stands for an empty cell.

If I'm understanding the task as intended, you could use to get the total
for "Heading3" the less expensive...

=SUMIF(A2:I2,"Heading3",A3:I3)

instead of the array formula...

{=SUM(IF(A2:I2="Heading3",A3:I3))}

"[T]o pull out the first non-zero value from the values under Heading3",
use...

=INDEX(A3:I3,MATCH(1,INDEX((A2:I2="Heading3")*(A3:I3<>0),1,0),0))

which is normally entered.


Quote:
> I have a table of repeating columns:

> Heading1...HeadingN,Heading1...HeadingN,Heading1...HeadingN, etc

> I am using a array formula

> {=sum(if(row with headings="Heading3",1,0)*Row with values under
headings)}

> to pull out all the values under say Heading3 which gives me:

> {0,0,0,0,0,0,0,0,0,0,0,26,0,0,0,0,0,56,0,0.....}

> (the first three values from Heading3 are zero in this example...)

> Now this was fine when I wanted to sum all the values under Heading3 in
> order to test whether the row needed processing.

> But now I find that I need to pull out the first non-zero value from the
> values under Heading3.

> Is it possible to pull out the required value from the array of values
like
> the one above???

> OR is there another way that I have missed????

> Thanks in advance

> DG



Thu, 07 Jul 2005 20:43:25 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Finding the first non-zero cell in an array

2. Calculating non-array formulas as array formulas

3. Turn array formula into non-array formula

4. How do I find the first non-zero cell in a cloumn

5. Looking Up First Non-Zero Value

6. Find first non-empty cell in array

7. want to Count and return Zero as well as non zero results

8. Return previous zeros up to first non-zero

9. Formula Results in Zero Value on Line Chart

10. find first value in array


 
Powered by phpBB® Forum Software © phpBB Group