Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use button to return random numbers | Excel Worksheet Functions | |||
Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
Dollar weighted rate of return with random cash flows | Excel Discussion (Misc queries) | |||
Search a random array of cells and return a value of "X" | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) |