questionexcel
New Member
- Joined
- May 22, 2015
- Messages
- 1
I'm trying to find the date in which my customers reached 70% interest in our product during the 10 day trial period. To do this, I marked "1" for the days in which the customer opened our product and "0" for the days in which the customer did not open our product. Because my data file is quite large, I want to be able to just drag one formula down and get the date.
For example:
Out of the 10 day trial period, Jack used our product for 7/10 days. We want to know the date in which Jack reached 70% interest (rounded up).
<tbody>
</tbody>
The formula that I've made (in red below) returned the value of 8, which tells me that the 70% interest occurred on the 8th day.
=MATCH((ROUNDUP(0.7*(COUNTIF(B2:K2,1)),0)),B7:K7)
Would anyone happen to know how I could change the formula to make the formula return the value of 1/8/15 (the date in which the 70% interest occurred) instead of the number 8?
Also, I'm new with excel so I'm not sure if the logic behind this formula makes sense, so please let me know if there's a better way to approach this.
Thank you in advance for your help!
For example:
Out of the 10 day trial period, Jack used our product for 7/10 days. We want to know the date in which Jack reached 70% interest (rounded up).
Date | 1/1/15 | 1/2/15 | 1/3/15 | 1/4/15 | 1/5/15 | 1/6/15 | 1/7/15 | 1/8/15 | 1/9/15 | 1/10/15 | 70% |
Jack | 1 | 0 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | =MATCH((ROUNDUP(0.7*(COUNTIF(B2:K2,1)),0)),B7:K7) |
Jill | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | |
Amy | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 |
<tbody>
</tbody>
The formula that I've made (in red below) returned the value of 8, which tells me that the 70% interest occurred on the 8th day.
=MATCH((ROUNDUP(0.7*(COUNTIF(B2:K2,1)),0)),B7:K7)
Would anyone happen to know how I could change the formula to make the formula return the value of 1/8/15 (the date in which the 70% interest occurred) instead of the number 8?
Also, I'm new with excel so I'm not sure if the logic behind this formula makes sense, so please let me know if there's a better way to approach this.
Thank you in advance for your help!
Last edited by a moderator: