how to return random non zero value to a particular cell?
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? |
how to return random non zero value to a particular cell?
=SUM(A1:A12)
-- David Biddulph "Oligo" wrote in message ... 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? |
how to return random non zero value to a particular cell?
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" wrote: 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? |
how to return random non zero value to a particular cell?
"Sheeloo" wrote: 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" wrote: 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? 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? |
how to return random non zero value to a particular cell?
Hi,
=MAX(A1:A100) -- Thanks, Shane Devenshire "Oligo" wrote: "Sheeloo" wrote: 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" wrote: 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? 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? |
how to return random non zero value to a particular cell?
If you want the first non-zero value then copy this in B1
=INDIRECT("A"&MATCH(1,(IF(A1:A10000,1,"")),0)) and press CTRL-SHIFT-ENTER "Oligo" wrote: "Sheeloo" wrote: 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" wrote: 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? 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? |
how to return random non zero value to a particular cell?
=INDIRECT("A"&MATCH(1,(IF(A1:A10000,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:A10000,0)) And here's a way to do the same thing without array entering: =INDEX(A1:A1000,MATCH(TRUE,INDEX(A1:A10000,,1),0) ) -- Biff Microsoft Excel MVP "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... If you want the first non-zero value then copy this in B1 =INDIRECT("A"&MATCH(1,(IF(A1:A10000,1,"")),0)) and press CTRL-SHIFT-ENTER "Oligo" wrote: "Sheeloo" wrote: 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" wrote: 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? 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? |
All times are GMT +1. The time now is 06:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com