Complex Excel question...well to me at least

I'm a sales manager, and one of the requirements of my job is to send my results to my leadership nightly. One of my challenges is getting a formula that will calculate my forcasted results correctly every day of the month which includes providing a daily goal.
here's what I use to calculate the forcast

1) excell reads my MTD data from our company's server which give me my total numbers up to yesterday
2)it divides that number by today's date then mulitiplies is by the days in the month
3) it subracts my current results by my goal and divides it by my days left in the month to give me a daily goal

CHALLENGES
we are closed on thanksgiving and christmas day so I need to subtract on day for Nov and Dec,
I used a VLOOKUP function that would match a month drop down list which looked like this
January 1/31/2010
February 2/28/2010
March 3/31/2010
April 4/30/2010
May 5/31/2010
June 6/30/2010
July 7/31/2010
August 8/31/2010
September 9/31/2010
October 10/31/2010
November 11/30/2010
December 12/31/2009

then I would subtract that result by today() -1(if it was november or december)

well on the 31st of december that would return a negative number for days left which would give me #DIV/0. fixed that with an if statement saying if days days left formula is <= 0 simply subtract my goal by my attainment and that will be the goal for that day.

On the 1st, the server gives numbers for the entire previous month with messes up my daily goal function, I thought about using another IF or maybe an OR statement.

with my limited amount of knowledge I am sure I am making this way too hard, please help me figure this out!!!
dmitchell81
Asked Jan 02, 2010

TIP: If it's not your answer to this question, please click "Leave a Comment" button under the question to communicate with the question owner.

Categories