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

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

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

On Oct 4, 2:32*pm, u473 wrote:
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."

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

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

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

On Oct 4, 4:14*pm, u473 wrote:
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