Find Running Total Value or % at a given Target Date or vice versa

[u473]

From the following table I want to be able to answer 3 questions with
VBA
a. From the cumulated % Column. By what date will I reach 30%
or
b. From the Running Total Column. By what date will I reach 3,000
or
c. From the Date Column. What will be the running total & % by October
1
..
Note : the Date column is in Date Format
Help appreciated
..
Date Cost1 Cost2 Total Run Ttl %
7/27/2010 12 600 612 612 10.7
7/28/2010 1 50 51 663 11.6
7/30/2010 4 200 204 867 15.2
8/25/2010 10 500 510 1,377 24.1
8/26/2010 7 350 357 1,734 30.4
8/27/2010 12 600 612 2,346 41.1
8/28/2010 13 650 663 3,009 52.7
9/30/2010 10 500 510 3,519 61.6
10/1/2010 7 350 357 3,876 67.9
10/2/2010 12 600 612 4,488 78.6
10/3/2010 24 1,200 1,224 5,712 100.0
Grand Total 112 5,600 5,712

 

[Don Guillett Excel MVP]

From the following table I want to be able to answer 3 questions with
VBA
a. From the cumulated % Column. By what date will I reach 30%
or
b. From the Running Total Column. By what date will I reach 3,000
or
c. From the Date Column. What will be the running total & % by October
1
.
Note : the Date column is in Date Format
Help appreciated
.
Date               Cost1  Cost2   Total Run Ttl   %
7/27/2010            12 600        612     612  10.7
7/28/2010              1        50           51    663  11.6
7/30/2010              4        200        204     867  15.2
8/25/2010            10 500        510  1,377   24.1
8/26/2010              7        350        357  1,734   30.4
8/27/2010            12 600        612  2,346   41.1
8/28/2010            13 650        663  3,009   52.7
9/30/2010            10 500        510  3,519   61.6
10/1/2010              7        350        357  3,876   67.9
10/2/2010            12 600        612  4,488   78.6
10/3/2010       24    1,200     1,224   5,712      100.0
Grand Total  112     5,600      5,712

Your table doesn't come across properly with ttc so

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[u473]

Ok, I should have simplified the table. here it is

Date Running Total %
7/27/2010 612 10.7
7/28/2010 663 11.6
7/30/2010 867 15.2
8/25/2010 1,377 24.1
8/26/2010 1,734 30.4
8/27/2010 2,346 41.1
8/28/2010 3,009 52.7
9/30/2010 3,519 61.6
10/1/2010 3,876 67.9
10/2/2010 4,488 78.6
10/3/2010 5,712 100.0
..
Input Boxes & expected answers for the following questions :
1 From the cumulated % Column. By what date will I reach 30% or
less ? Answer : 8/25/2010
..
2. From the Running Total Column. By what date will I reach 3,000 or
Less ? Answer : 8/27/2010
..
3. From the Date Column. What will be the running total & % by
9/01/2010 or earlier ? Answer : 3,009 and 52.7
..
Thank you for your help

 

[Don Guillett Excel MVP]

Ok, I should have simplified the table. here it is

Date               Running Total           %
7/27/2010              612                 10.7
7/28/2010              663                 11.6
7/30/2010              867                 15.2
8/25/2010           1,377                  24..1
8/26/2010           1,734                  30..4
8/27/2010           2,346                  41..1
8/28/2010           3,009                  52..7
9/30/2010           3,519                  61..6
10/1/2010           3,876                  67..9
10/2/2010           4,488                  78..6
10/3/2010           5,712                 100.0
.
Input Boxes & expected answers for the following questions :
1   From the cumulated % Column. By what date will I reach 30% or
less ?                   Answer : 8/25/2010
.
2.   From the Running Total Column. By what date will I reach 3,000 or
Less  ?              Answer : 8/27/2010
.
3.   From the Date Column. What will be the running total & % by
9/01/2010 or earlier ? Answer :  3,009   and  52.7
.
Thank you for your help

A formula solution for question 1
=INDEX(A:A,MATCH(VLOOKUP(30,C2:C22,1),C:C))
or
=SMALL($A$2:$A$17,COUNTIF($C$2:$C$17,"<"&30))
for 3 , a simple vlookup will work.
For macro use a looping for/each stopping when > and go back one