today as a function of the year to date

[Guest]

I need to create a formula that will automatically calculate the number of
tickets sold per month that takes into account the current date. The number
of tickets sold since July 1, 2007 is in a cell which is continuoslu updated.
As of today, October 9th, we have sold 5251 tickets which will result in
around 1595 tickets per month. But this sure has me stumped, any ideas?

 

[Pete_UK]

Assume the number of tickets sold is in A1, and in D1 you have the
reference date (1st July 2007) in Excel date format. This formula will
give you the number sold per day:

=A1/(TODAY()-D1)

If you take it that an average month is 30 days, then multiply by 30
to get the monthly rate.

Hope this helps.

Pete

 

[David Biddulph]

=A1*(365.2425/12)/(TODAY()-DATE(2007,7,1))

The 365.2425/12 term is what I have taken as the average number of days in a
month. You may wish to define a month differently.

 

[Guest]

Thanks Pete and David, Davids works best but I ended up using a cell location
instead of the date(2007,7,1) for last years year end. I tip my hat to you
both.

 

[Rick Rothstein \(MVP - VB\)]

I need to create a formula that will automatically calculate the number of

tickets sold per month that takes into account the current date. The
number
of tickets sold since July 1, 2007 is in a cell which is continuoslu
updated.
As of today, October 9th, we have sold 5251 tickets which will result in
around 1595 tickets per month. But this sure has me stumped, any ideas?

Here is another possibility... it calculates the actually number of months
passed prior to the current month and adds to that the fraction of the
current month that has passed to date (day of month divided by total days in
month) and divides that into A3 (where I am assuming your total number of
tickets sold is kept).

=A3/(DATEDIF(DATE(2007,7,1),TODAY(),"m")+DAY(TODAY())/DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)))

Rick