how to return random non zero value to a particular cell?

O

Oligo

A
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00872500
0.00000000

assuming the non zero valu(0.008725) might fall in any row. how can i get
that value into cell B1?
 
S

Sheeloo

If you are sure that all others are zero then you can simply have this in B1
=Sum(A1:A100) [change 100 to the last cell in Col A]
 
O

Oligo

Sheeloo said:
If you are sure that all others are zero then you can simply have this in B1
=Sum(A1:A100) [change 100 to the last cell in Col A]

Oligo said:
A
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00872500
0.00000000

assuming the non zero valu(0.008725) might fall in any row. how can i get
that value into cell B1?
Click to expand...
Click to expand...

sorry to bother you again.
what if there are chances that any 2 rows will be of equal values. but i
only one one of the row value to be in B1?
 
S

ShaneDevenshire

Hi,

=MAX(A1:A100)


--
Thanks,
Shane Devenshire


Oligo said:
Sheeloo said:
If you are sure that all others are zero then you can simply have this in B1
=Sum(A1:A100) [change 100 to the last cell in Col A]

Oligo said:
A
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00872500
0.00000000

assuming the non zero valu(0.008725) might fall in any row. how can i get
that value into cell B1?
Click to expand...
Click to expand...

sorry to bother you again.
what if there are chances that any 2 rows will be of equal values. but i
only one one of the row value to be in B1?
Click to expand...
 
S

Sheeloo

If you want the first non-zero value then copy this in B1
=INDIRECT("A"&MATCH(1,(IF(A1:A1000>0,1,"")),0))
and press CTRL-SHIFT-ENTER

Oligo said:
Sheeloo said:
If you are sure that all others are zero then you can simply have this in B1
=Sum(A1:A100) [change 100 to the last cell in Col A]

Oligo said:
A
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00872500
0.00000000

assuming the non zero valu(0.008725) might fall in any row. how can i get
that value into cell B1?
Click to expand...
Click to expand...

sorry to bother you again.
what if there are chances that any 2 rows will be of equal values. but i
only one one of the row value to be in B1?
Click to expand...
 
T

T. Valko

=INDIRECT("A"&MATCH(1,(IF(A1:A1000>0,1,"")),0))

Here's a way to do the same thing without the volatile function. Assuming
there is no text in the range and all numbers are positive:

Array entered:

=INDEX(A1:A1000,MATCH(TRUE,A1:A1000>0,0))

And here's a way to do the same thing without array entering:

=INDEX(A1:A1000,MATCH(TRUE,INDEX(A1:A1000>0,,1),0))

--
Biff
Microsoft Excel MVP


Sheeloo said:
If you want the first non-zero value then copy this in B1
=INDIRECT("A"&MATCH(1,(IF(A1:A1000>0,1,"")),0))
and press CTRL-SHIFT-ENTER

Oligo said:
Sheeloo said:
If you are sure that all others are zero then you can simply have this
in B1
=Sum(A1:A100) [change 100 to the last cell in Col A]

:

A
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00872500
0.00000000

assuming the non zero valu(0.008725) might fall in any row. how can i
get
that value into cell B1?
Click to expand...

sorry to bother you again.
what if there are chances that any 2 rows will be of equal values. but i
only one one of the row value to be in B1?
Click to expand...
Click to expand...
 
Top