Counting up row and counting how many days until threshold is reached

[worldCurrencies]

In column A, I have dates from newest date to oldest date. In B i have prices. In C i have the max price. In D I have change between price and peak price in terms of currency. In E i have the change from peak as a percent, but ONLY if the change in peak was -20% or less. F is the amount needed to be recovered(half of what was lost).

I am trying to calculate in column G the amount of days to recover half of what was lost. so for example, in row 20, A(Date) = 1/1/2015, B(Current Price) = $100, C(Peak Price) = $200, D($ Change) = -$100, E(% Change) = -50%. F(Amount to Recover) = $50. G is counting the dates in column A until the prices in Column B = $150 (because current price of 100 plus the amount needed to be recovered of $50). However because the dates are in descending order, the formula would need to count the dates going up the column until it sees the column B price of $150.

 

[etaf]

how about an index match - to find the date of that value and then subtract from the date

=A13-INDEX($A$2:$A$13,MATCH(B13+F13,$B$2:$B$13,0))

you may need to add 1 to include the date in cell
=A13-INDEX($A$2:$A$13,MATCH(B13+F13,$B$2:$B$13,0))+1

assuming i understand all the text description - a sample woul dbe much better here, i can see quite a few errors possible depending on data - ie - will the value looking for always be exact - can the value appear more than once - in which case it will get the oldest value and not the latest

Book3
	A	B	C	D	E	F	G
1	date	price				amount recover	days
2	7/24/22	360
3	7/25/22	330
4	7/26/22	300
5	7/27/22	270
6	7/28/22	240
7	7/29/22	210
8	7/30/22	180
9	7/31/22	150
10	8/1/22	120
11	8/2/22	110
12	8/3/22	105
13	8/4/22	100				50	5
Sheet2

Cell Formulas
Range	Formula
G13	G13	=A13-INDEX($A$2:$A$13,MATCH(B13+F13,$B$2:$B$13,0))+1

Cells with Data Validation
Cell	Allow	Criteria
E2	Custom	=COUNTIF($D$2:$D$5,B2)=0

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[worldCurrencies]

how about an index match - to find the date of that value and then subtract from the date

=A13-INDEX($A$2:$A$13,MATCH(B13+F13,$B$2:$B$13,0))

you may need to add 1 to include the date in cell
=A13-INDEX($A$2:$A$13,MATCH(B13+F13,$B$2:$B$13,0))+1

assuming i understand all the text description - a sample woul dbe much better here, i can see quite a few errors possible depending on data - ie - will the value looking for always be exact - can the value appear more than once - in which case it will get the oldest value and not the latest

Book3
	A	B	C	D	E	F	G
1	date	price				amount recover	days
2	7/24/22	360
3	7/25/22	330
4	7/26/22	300
5	7/27/22	270
6	7/28/22	240
7	7/29/22	210
8	7/30/22	180
9	7/31/22	150
10	8/1/22	120
11	8/2/22	110
12	8/3/22	105
13	8/4/22	100				50	5
Sheet2

Cell Formulas
Range	Formula
G13	G13	=A13-INDEX($A$2:$A$13,MATCH(B13+F13,$B$2:$B$13,0))+1

Cells with Data Validation
Cell	Allow	Criteria
E2	Custom	=COUNTIF($D$2:$D$5,B2)=0

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Hi, thank you for your input. I am about to upload an image to help with this. I tried your formula in column I. I think some problems are: your dates are in ascending order and that it is looking for exact, as you mentioned. I am looking for descending order and whenever that threshold is met of price + recovery. However, I appreciate the response!!! Thank you!!

 

[etaf]

as mentioned images are difficult to use
can you use xl2bb or a share as detailed - in my last post

also provide the example values expected and why - would help a lot here