Find date from target %

chullan88

New Member
Joined
Dec 4, 2016
Messages
19
Hi,

I have a set of values for dates and corresponding %s

1-Jan-18 8%
1-Feb-18 6%
1-Mar-18 8%

I need a single cell formula to find the particular date at which a target cumulative % is achieved

For Eg, the target cumulative % of 20% will be achieved on 22-Feb-18
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,239
For Eg, the target cumulative % of 20% will be achieved on 22-Feb-18
I do not understand how cumulative can exceed 20% before March (unless there is already a cumulative value of approx 8% at 31-Dec-17)
The method is correct, but my table exceeds 20% on 26 March :)

I would create a table with a cumulative value for EVERY day and look up the value in that table with a simple formula in the cell
- dates must be sequential earlier to later for this formula to work

=INDEX(A:A,MATCH(0.2,D:D,1)+1)


Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
1
DatesMonth's
Target
Daily
Target
Cum
2
01-Jan-19​
8%​
0.26%​
0.26%​
26/03/2019​
=INDEX(A:A,MATCH(0.2,D:D,1)+1)
3
02-Jan-19​
8%​
0.26%​
0.52%​
4
03-Jan-19​
8%​
0.26%​
0.78%​
5
04-Jan-19​
8%​
0.26%​
1.04%​
6
05-Jan-19​
8%​
0.26%​
1.30%​
7
... etc
8
9
30-Jan-19​
8%​
0.26%​
7.80%​
10
31-Jan-19​
8%​
0.26%​
8.06%​
11
01-Feb-19​
6%​
0.19%​
8.25%​
12
02-Feb-19​
6%​
0.19%​
8.44%​
13
... etc
14
15
27-Feb-19​
6%​
0.19%​
13.19%​
16
28-Feb-19​
6%​
0.19%​
13.38%​
17
01-Mar-19​
8%​
0.26%​
13.64%​
18
02-Mar-19​
8%​
0.26%​
13.90%​
19
... etc
20
21
24-Mar-19​
8%​
0.26%​
19.62%​
22
25-Mar-19​
8%​
0.26%​
19.88%​
23
26-Mar-19​
8%​
0.26%​
20.14%​
24
27-Mar-19​
8%​
0.26%​
20.40%​
25
28-Mar-19​
8%​
0.26%​
20.66%​
26
29-Mar-19​
8%​
0.26%​
20.92%​
27
30-Mar-19​
8%​
0.26%​
21.18%​
28
31-Mar-19​
8%​
0.26%​
21.44%​
Sheet: Sheet2
 

Forum statistics

Threads
1,077,784
Messages
5,336,327
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top