Calculate Goal each day

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'

Killa'

If I understand you correctly and assuming that you have A1:D1 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

[email protected]
[email protected] with @tiscali.co.uk


<[email protected]> wrote in message
news:[email protected]...
> 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'
>

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 wrote:
> Killa'
>
> If I understand you correctly and assuming that you have A1:D1 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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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'
> >

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

[email protected]
[email protected] with @tiscali.co.uk


<[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> Killa'
>>
>> If I understand you correctly and assuming that you have A1:D1 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
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> > 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'
>> >
>

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 wrote:
> 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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >> Killa'
> >>
> >> If I understand you correctly and assuming that you have A1:D1 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
> >>
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >>
> >> <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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'
> >> >
> >

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

[email protected] wrote:
> 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 wrote:
> > 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
> >
> > [email protected]
> > [email protected] with @tiscali.co.uk
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 wrote:
> > >> Killa'
> > >>
> > >> If I understand you correctly and assuming that you have A1:D1 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
> > >>
> > >> [email protected]
> > >> [email protected] with @tiscali.co.uk
> > >>
> > >>
> > >> <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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'
> > >> >
> > >

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

[email protected] wrote:
> 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 wrote:
> > 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
> >
> > [email protected]
> > [email protected] with @tiscali.co.uk
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 wrote:
> > >> Killa'
> > >>
> > >> If I understand you correctly and assuming that you have A1:D1 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
> > >>
> > >> [email protected]
> > >> [email protected] with @tiscali.co.uk
> > >>
> > >>
> > >> <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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@???????????????> wrote in message
news:[email protected]...


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

[email protected]
[email protected] with @tiscali.co.uk