countdown function

[markan667]

hi guys i have a question regarding a function i am not familliar with and hope you can help me with it...

the thing i'm trying to achieve is to make excell count for me the remaining months in this example (in this case till march 2012)


here is a screenshot





what i would like is to have it so that everytime a new month is added the countdown reduces the valeue by one. In this case when i add the "10-11" the countdown sholud go from 6 to 5

 

[number kruncher]

I would first need to set the date based on your title of each bow.

You could use something like this to turn your title in the B column into a date in say the F column..

=DATE(MID(B187,FIND("-",B187)+1,2)+2000,LEFT(B187,FIND("-",B187)-1),1)

Then use this date to compare to finish date..

=(DATE(2012,3,31)-F187)/30

Then round to desired result.

HTH

 

[markan667]

hmmmm i'm not sure to understand that formula (havent been using excel for a while now and to be honest even while i was using it i really had no need for anything that was more complexe than summing charts and simple formulas).
anyway i would like to understand it so i'll try to explain a little better what i'm trying to do.

the thing is i need a certain ammont of money (7000) saved by 20.03.2012 ( box I 166) also i am listing the ammounts i earn by date (collumn D) and summ it up monthly to the ammounts made (green boxes) and ammounts saved (pink boxes)

the thing i would like to have is that after every month passed the months remaining collumn (L 174)automaticly gets reduced by one (so far i do it manually) so that the formula monthly needed makes sense. the B and C collums are by the way irrellevant as the are only dates and days.

 

[number kruncher]

Why not just try, the end date minus todays date, eg..

=(DATE(2012,3,20)-TODAY())/30

Round up or down to suit.

HTH

 

[markan667]

well that would work also but still i don't know how to make the countdown work (total ammount needed/months left=monthly ammount needed)


but anyway thanks a lot for the help, i appreciate it

 

[number kruncher]

I think your nearly there. Formula should be something like..

=(7000-Saved)/months remaining

Will give desired result.

eg..

=(7000-5510)/6 = 248.33

or more accurately

=(7000-5510)/((DATE(2012,3,20)-TODAY())/30) = 259.88

Substitute 7000 and 5510 from cells above to keep dynamic

HTH

 

[markan667]

I think your nearly there. Formula should be something like..

=(7000-Saved)/months remaining

Will give desired result.

eg..

=(7000-5510)/6 = 248.33

or more accurately

=(7000-5510)/((DATE(2012,3,20)-TODAY())/30) = 259.88

Substitute 7000 and 5510 from cells above to keep dynamic

HTH

once again thanks a lot for the help!! the bolded formula is actually the one i use in the monthly needed cell the only value i can't get to update itself automaticly in this case is the number of months remaining in this case 6 months at the moment.

the second formula is actually something that would give the desired result,

so can excel actualy automaticly use the actual date

=(7000-5510)/((DATE(2012,3,20)-TODAY())/30) = 259.88

where the bolded TODAY gets replaced by the date?

 

[markan667]

and for the last time in this topic, i didn't understand it at first but now i made it, i added to the months remaining cell the formula: =(L179-L180)/30 while L179 is 20.03.2012 and L180 =TODAY(), all that is formated so that it rounds up to 0 decimals and it actually works :D

thanks a lot again for the help

 

[number kruncher]

You are welcome. Thanks for feedback.