| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookups
I have a row of data with either zeros or numbers in 24 cells. I would like
to create a formula in an adjacent cell which scans the 24 cells and records the first nonzero number it finds. I've thought of using a nested if-then but that seems to cumbersome. Would appreciate your feedback...thx. Daveo |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
=INDEX(A1:X1,MATCH(TRUE,A1:X1<0,0))
entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Daveo" wrote in message ... I have a row of data with either zeros or numbers in 24 cells. I would like to create a formula in an adjacent cell which scans the 24 cells and records the first nonzero number it finds. I've thought of using a nested if-then but that seems to cumbersome. Would appreciate your feedback...thx. Daveo |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
Peo,
I tried your formula and I got back #VALUE!...any other suggestions? "Peo Sjoblom" wrote: =INDEX(A1:X1,MATCH(TRUE,A1:X1<0,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Daveo" wrote in message ... I have a row of data with either zeros or numbers in 24 cells. I would like to create a formula in an adjacent cell which scans the 24 cells and records the first nonzero number it finds. I've thought of using a nested if-then but that seems to cumbersome. Would appreciate your feedback...thx. Daveo |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
After you typed the below in, did you hit CTRL+SHIFT+ENTER or just hit enter
to exit the cell? "Daveo" wrote: Peo, I tried your formula and I got back #VALUE!...any other suggestions? "Peo Sjoblom" wrote: =INDEX(A1:X1,MATCH(TRUE,A1:X1<0,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Daveo" wrote in message ... I have a row of data with either zeros or numbers in 24 cells. I would like to create a formula in an adjacent cell which scans the 24 cells and records the first nonzero number it finds. I've thought of using a nested if-then but that seems to cumbersome. Would appreciate your feedback...thx. Daveo |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
It works believe me, so either you didn't apply it correctly or your data is
not what it is supposed to be. Post the exact formula you tried, copied from your workbook -- Regards, Peo Sjoblom "Daveo" wrote in message ... Peo, I tried your formula and I got back #VALUE!...any other suggestions? "Peo Sjoblom" wrote: =INDEX(A1:X1,MATCH(TRUE,A1:X1<0,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Daveo" wrote in message ... I have a row of data with either zeros or numbers in 24 cells. I would like to create a formula in an adjacent cell which scans the 24 cells and records the first nonzero number it finds. I've thought of using a nested if-then but that seems to cumbersome. Would appreciate your feedback...thx. Daveo |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
That could be the problem...will try again...thanks...
"Sean Timmons" wrote: After you typed the below in, did you hit CTRL+SHIFT+ENTER or just hit enter to exit the cell? "Daveo" wrote: Peo, I tried your formula and I got back #VALUE!...any other suggestions? "Peo Sjoblom" wrote: =INDEX(A1:X1,MATCH(TRUE,A1:X1<0,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Daveo" wrote in message ... I have a row of data with either zeros or numbers in 24 cells. I would like to create a formula in an adjacent cell which scans the 24 cells and records the first nonzero number it finds. I've thought of using a nested if-then but that seems to cumbersome. Would appreciate your feedback...thx. Daveo |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
That worked...thanks!
"Sean Timmons" wrote: After you typed the below in, did you hit CTRL+SHIFT+ENTER or just hit enter to exit the cell? "Daveo" wrote: Peo, I tried your formula and I got back #VALUE!...any other suggestions? "Peo Sjoblom" wrote: =INDEX(A1:X1,MATCH(TRUE,A1:X1<0,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Daveo" wrote in message ... I have a row of data with either zeros or numbers in 24 cells. I would like to create a formula in an adjacent cell which scans the 24 cells and records the first nonzero number it finds. I've thought of using a nested if-then but that seems to cumbersome. Would appreciate your feedback...thx. Daveo |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Lookups
Peo...you were correct...had to do with me not entering the ctrl, shift &
enter but I went back and tried again and it works perfectly...thanks! "Peo Sjoblom" wrote: It works believe me, so either you didn't apply it correctly or your data is not what it is supposed to be. Post the exact formula you tried, copied from your workbook -- Regards, Peo Sjoblom "Daveo" wrote in message ... Peo, I tried your formula and I got back #VALUE!...any other suggestions? "Peo Sjoblom" wrote: =INDEX(A1:X1,MATCH(TRUE,A1:X1<0,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Daveo" wrote in message ... I have a row of data with either zeros or numbers in 24 cells. I would like to create a formula in an adjacent cell which scans the 24 cells and records the first nonzero number it finds. I've thought of using a nested if-then but that seems to cumbersome. Would appreciate your feedback...thx. Daveo |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookups | Excel Discussion (Misc queries) | |||
| Lookups | Excel Discussion (Misc queries) | |||
| Lookups... | Excel Worksheet Functions | |||
| Maybe I need help with Lookups?? | Excel Worksheet Functions | |||
| LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
Linear Mode
