Calculate Goal each day

[bryan.sigur]

Ok, First I'm an Excel novice! I have a spreadsheet that has
'Production Amount', 'Weekly Goal', and 'What It Will Take Tomorrow'
columns. I also have two cells that displays the day of the week (ex.
Wednesday formula: =TODAY()) and the Month (ex. 6/21/06 Fornula:
=CHOOSE(WEEKDAY(H1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")).
I want to be able to open that spreadsheet everyday and input the
production amount completed for that employee and have the spreadsheet
calculate what it will take on each of the following days of the week
to reach their goal. Right now i have it calculating entire goal
remaning in the 'What It Will Take Tomorrow' column. I would like
that column to consider the remainder of the days left in the week and
divide the goal by that number of days. Thanks for any help!

Killa'

 

[Sandy Mann]

Killa'

If I understand you correctly and assuming that you have A11 with your
headers, "Date, Production Amount, Weekly Goal and What It Will Take
Tomorrow" respectively. I further assume that as you quote every day of
the week Sunday to Saturday, you work Sunday to Saturday.

In A2 your formula: =TODAY()
In A3 enter: =IF(A2="","",IF(WEEKDAY(A2,1)<7,A2+1,"")) and copy it down to
A8 then custom format A2:A8 as "dddd mmm d yyyy" (without the quotes and
make sure that the column is wide enough to display a date like: "Saturday
September 30 2006"

In D2 enter: =ROUND((C2-B2)/(COUNT(A2:A8)-1),0)&" Per day"

If you only work Monday to Friday then in A3 use:

=IF(A2="","",IF(WEEKDAY(A2,3)<4,A2+1,"")) and copy it down


--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[bryan.sigur]

Ok, looks like you understood me right on point. I actually have it
setup like this:
- Day of the week (Wednesday) in G1
- Month (6/21/2006) in H1
- 'Persons Name'= A3
- 'Production'= C3
- 'Monthly Goal'= E3
- 'What It Will Take Tomorow'= G3

Note: A1:F1 are merged for a Title (Weekly Goal Report)

I think i may be able to work with what you gave me, but if the way i
have it setup throws your suggestion off, let me know. Thanks!

 

[Sandy Mann]

If you want to have the daily amount in G3 without listing the days of the
week as I did then make the formula in G3:

=ROUND((E3-C3)/(7-WEEKDAY(G1,2)),0)&" Per day"

For a working week ending on Sunday and:

=ROUND((E3-C3)/(5-WEEKDAY(G1,2)),0)&" Per day"

for a Monday - Friday working week
--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[bryan.sigur]

Yes, that's exactly what i want!!! You are the bomb! Is it possible
to display the 'per day' number with a $? Also, how would i add all of
the total 'per day' amounts to get a grand total 'per day'? I had a
cell that was adding those cells (=sum(G4:G8)), but that is no longer
working. I know you want to send me a bill at this point! Thanks for
your help!!!

 

[sandy.mann]

Bryan,

Try a custom format of:

$#,##0.00 "Per Day"
for individual rows and:

Total $#,##0.00
for the total cell

The cells should then SUM correctly because you will have only numbers
in the cells - the rest is just formatting

HTH

Sandy

 

[sandy.mann]

Bryan,

Try a custom format of:

$#,##0.00 "Per Day"
for individual rows and:

Total $#,##0.00
for the total cell

The cells should then SUM correctly because you will have only numbers
in the cells - the rest is just formatting

HTH

Sandy

 

[Sandy Mann]

Full address in a Public Newsgroup not just once but twice! Boy! am I going
to be in trouble at work tomorrow!

--

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk