MTD running total formula

B

brianwa

I am building a spreadsheet that tracks sales on a daily basis.
What I want to do is have a column that shows where we should be in
sales on a particular day. Normally that would be simple but my sheet
has everyday of the month in it and I want to exclude Sat, Sun and
Holidays from the formula.

The sheet is set up as follows with the sales target for the month in
cell D1 and the amount of shipping days in D2.
A B C D
Sales Daily Target
8 Thu 1/1/04
9 Fri 1/2/04
10 Sat 1/3/04
11 Sun 1/4/04

Thanks in advance,
BW
 
D

Debra Dalgleish

With the month start date in a cell on the worksheet (A1 in this
example), and the holidays in a named range (Holidays), enter the
following formula in cell C8, and copy down:

=$D$1/$D$2*NETWORKDAYS($A$1,B8,Holidays)

NETWORKDAYS is a function in the Analysis Toolpak. If it's not
installed, you'll see #NAME? in the cell with the formula.
 
B

brianwa

Thanks,

I must be suffering from holiday cheer. I never thought to adjust the
end date on the networkdays formula =-(

Much appreciated.
BW
 
Top