nick205
|
| posted on 4/2/14 at 07:13 PM |
|
|
Excel help please
Hoping someone can help with the following in Excel.....
Column A = sequential dates into the future
Column B = decreasing number values driven by a variable driven formula
When the number value drops to <0 I want to the corresponding date value to be displayed in a cell in another sheet.
|
|
|
|
|
geoff shep
|
| posted on 4/2/14 at 08:14 PM |
|
|
In the cell, on the other sheet, where you want the date to display, you need a formula like this:
=IF(B1<0,A1,0)
Where B1 is the cell with the number which might be less than 0 and A1 is the cell with the date you want to copy. Make sure you format the cells as
date if you want them to display correctly.
|
|
|
nick205
|
| posted on 4/2/14 at 10:49 PM |
|
|
Hi Geoff,
Not got it in front of me at the minute, but think I follow the method.
My problem is, the numerical values in column B change depending on other variables and the <0 point will move up and down in the column. The
column is around 3000 cells top to bottom so far and growing.
Also, once the value drops below 0 it keeps going in negative numbers, but it's the first date at which it drops below 0 that I need to
extract.
I guess i need a way of applying the method to the whole column, but no idea how to do it.
[Edited on 4/2/14 by nick205]
|
|
|
garyo
|
| posted on 5/2/14 at 08:38 AM |
|
|
Set up a column C that uses the formula Geoff suggests. Then you'll have a list of dates where x < 0
=IF(B1<0,A1,"Not less"
Then on your other sheet, set the forumula to =MIN(C0:C3000) and that will give you the first date from column C.
[Edited because I just realised Geoff's default value of 0 is going to be less than the date values and confuse the 'MIN'
function)
[Edited on 5/2/14 by garyo]
|
|
|
nick205
|
| posted on 5/2/14 at 10:39 AM |
|
|
Thanks - will have a play with that.
|
|
|
cerbera
|
| posted on 5/2/14 at 11:42 AM |
|
|
Copy
=IF(INDIRECT("Sheet2!"&(ADDRESS(COUNTA(Sheet2!B:B),2)))<0,(OFFSET(INDIRECT("Sheet2!"&(ADDRESS(COUNTA(Sheet2!B:B),2)
)),0,-1)),"No Minus Values" )
into the formula bar of a cell on whatever sheet you want the output to be.
Change all refs to Sheet2, in the formula, to the sheet name where your data is stored.
Change all refs of B:B, in the formula, to the column where your numbers are stored / inputed.
Make sure you format the output cell to show date.
HTH
Just re-read your second post and this may not do what you want, as this formula always looks at the last value in the column B and if its <0 will
output the date.
Another go which is similar to the above solutions
Copy into cell C1
=IF(B1<0,(OFFSET(B1,0,-1)),"" )
And then drag down the 3000+ lines that you already have. You can then hide this column if it spoils your spreadsheet.
On the cell where you want your result copy this into the formula bar
=MIN(Sheet2!C:C)
[Edited on 5/2/14 by cerbera]
|
|
|
Minicooper
|
| posted on 7/2/14 at 10:23 PM |
|
|
Use an array formula, no need for any other formula, enter this formula with Ctrl+Shift+Enter. Obviously you need to change this formula to suit the
sheet it is on
=INDEX(A:A,MATCH(TRUE,B:B<0,0))
Cheers
David
[Edited on 7/2/14 by Minicooper]
|
|
|
Minicooper
|
| posted on 11/2/14 at 10:21 AM |
|
|
"Obviously you need to change this formula to suit the sheet it is on"
That should read
"Obviously you need to change this formula to suit the sheet the formula is referring to"
David
|
|
|
