| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
last negative cell
Hi.
I have a column that contain numbers (either + or -). I want to find the last cell that contains a -ve value and return the corresponding date from the adjoining column e.g Col A Col B 1-Apr-2010 -1000 2-Apr-2010 -1000 3-Apr-2010 +500 4-Apr-2010 -1000 5-Apr-2010 +700 In this illustration, i did like to have the value 4-Apr-2010 returned Can anyone please help Regards Nikhil |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
last negative cell
Try this array formula (Entered with CTRL + SHFT + ENTER)
=INDEX(A:A,MATCH(MAX(B2:B65536<0),B2:B65536)+1) Regards Peter "Nikhil" wrote: Hi. I have a column that contain numbers (either + or -). I want to find the last cell that contains a -ve value and return the corresponding date from the adjoining column e.g Col A Col B 1-Apr-2010 -1000 2-Apr-2010 -1000 3-Apr-2010 +500 4-Apr-2010 -1000 5-Apr-2010 +700 In this illustration, i did like to have the value 4-Apr-2010 returned Can anyone please help Regards Nikhil |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
last negative cell
=LOOKUP(2,1/(B1:B100<0),A1:A100)
"Nikhil" wrote: Hi. I have a column that contain numbers (either + or -). I want to find the last cell that contains a -ve value and return the corresponding date from the adjoining column e.g Col A Col B 1-Apr-2010 -1000 2-Apr-2010 -1000 3-Apr-2010 +500 4-Apr-2010 -1000 5-Apr-2010 +700 In this illustration, i did like to have the value 4-Apr-2010 returned Can anyone please help Regards Nikhil |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| fill cell with colour if preceding cell is a negative value | Excel Discussion (Misc queries) | |||
| want to equal a cell number in a different cell with a negative si | Excel Worksheet Functions | |||
| Having the contains of a cell always be negative | Excel Discussion (Misc queries) | |||
| How to find each cell with non-negative value? | Excel Discussion (Misc queries) | |||
| zero on negative cell | Excel Discussion (Misc queries) | |||
Linear Mode
