Please help! Stuck on how to improve this formula

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).

Date 1/1/151/2/151/3/151/4/151/5/151/6/151/7/151/8/151/9/151/10/1570%
Jack1011100111=MATCH((ROUNDUP(0.7*(COUNTIF(B2:K2,1)),0)),B7:K7)
Jill1000111010
Amy1011110001

<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:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top