how do i automatically update % figures daily
 

I have a sales figure sheet showing month to date etc ..on this sheet I also
show the % of month gone .. so Sept's got 26 trading days @ 3.85% per day
... how can I get this figure to automatically update each day ( at the moment
I have to do manually) .. . so that day 2 = 7.7% ... day 3 = 11.55% ...
until last day 100%

Many thanks
John

how do i automatically update % figures daily
 

=NETWORKDAYS(TODAY()-DAY(TODAY())+1,TODAY())/NETWORKDAYS(TODAY()-DAY(TODAY()
)+1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

and format as percentage


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnm" wrote in message
...
I have a sales figure sheet showing month to date etc ..on this sheet I
also
show the % of month gone .. so Sept's got 26 trading days @ 3.85% per
day
.. how can I get this figure to automatically update each day ( at the
moment
I have to do manually) .. . so that day 2 = 7.7% ... day 3 = 11.55% ...
until last day 100%

Many thanks
John

how do i automatically update % figures daily
 

By the way, I calculate 21 working days, and therefore trading days, for
September.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...

=NETWORKDAYS(TODAY()-DAY(TODAY())+1,TODAY())/NETWORKDAYS(TODAY()-DAY(TODAY()
)+1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

and format as percentage


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnm" wrote in message
...
I have a sales figure sheet showing month to date etc ..on this sheet I
also
show the % of month gone .. so Sept's got 26 trading days @ 3.85% per
day
.. how can I get this figure to automatically update each day ( at the
moment
I have to do manually) .. . so that day 2 = 7.7% ... day 3 = 11.55%
....
until last day 100%

Many thanks
John

how do i automatically update % figures daily
 

If "trading days" are Monday thru Saturday, try this:


=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,TODAY()-DAY(TODAY())+1):INDEX(A:A,TODAY())))<1))/SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,TODAY()-DAY(TODAY())+1):INDEX(A:A,DATE(YEAR(TODAY()),MONTH (TODAY())+1,0))))<1))

That formula divides the number of current month-to-date days (excluding
Sundays) by the number of days in the current month (excluding Sundays)

In the case of today (9/11/2006), that is the 9th trading day out of 26
trading days in September: 34.6%

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"johnm" wrote:

I have a sales figure sheet showing month to date etc ..on this sheet I also
show the % of month gone .. so Sept's got 26 trading days @ 3.85% per day
.. how can I get this figure to automatically update each day ( at the moment
I have to do manually) .. . so that day 2 = 7.7% ... day 3 = 11.55% ...
until last day 100%

Many thanks
John

how do i automatically update % figures daily
 

Thanks for the info,

I've got to be honest with you Bob I aint got a clue what to do next ( i'm a
novice at this) if you don't mind I wondered if you could run through it,
...... just so you know the details ... I have cell N3 with a total Month gone
% figure ... I have a column of cells dated 1st to 30th september ..
cells a1 to a26 ( I only use 26 days because we do not trade on sundays) I
need a total % figure (cell N3) to be entered daily automatically ( so each
day increases the month gone % figure )

Your help is apprecited

Many thanks
John

"Bob Phillips" wrote:

=NETWORKDAYS(TODAY()-DAY(TODAY())+1,TODAY())/NETWORKDAYS(TODAY()-DAY(TODAY()
)+1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

and format as percentage


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnm" wrote in message
...
I have a sales figure sheet showing month to date etc ..on this sheet I
also
show the % of month gone .. so Sept's got 26 trading days @ 3.85% per
day
.. how can I get this figure to automatically update each day ( at the
moment
I have to do manually) .. . so that day 2 = 7.7% ... day 3 = 11.55% ...
until last day 100%

Many thanks
John

how do i automatically update % figures daily
 

John,

a few questions.

The trading days will already be entered. What cells are they in?

Is the percentage to be a percentage of trading days passed, or a percentage
of some trade figure (I think it is the former, but just to be sure. If it
is the latter, what cells are the trade amounts in)?

Does today count as a day passed, or yet to pass?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnm" wrote in message
...
Thanks for the info,

I've got to be honest with you Bob I aint got a clue what to do next ( i'm
a
novice at this) if you don't mind I wondered if you could run through
it,
..... just so you know the details ... I have cell N3 with a total Month
gone
% figure ... I have a column of cells dated 1st to 30th september ..
cells a1 to a26 ( I only use 26 days because we do not trade on sundays)
I
need a total % figure (cell N3) to be entered daily automatically ( so
each
day increases the month gone % figure )

Your help is apprecited

Many thanks
John

"Bob Phillips" wrote:


=NETWORKDAYS(TODAY()-DAY(TODAY())+1,TODAY())/NETWORKDAYS(TODAY()-DAY(TODAY()
)+1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

and format as percentage


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnm" wrote in message
...
I have a sales figure sheet showing month to date etc ..on this sheet
I
also
show the % of month gone .. so Sept's got 26 trading days @ 3.85%
per
day
.. how can I get this figure to automatically update each day ( at the
moment
I have to do manually) .. . so that day 2 = 7.7% ... day 3 = 11.55%
....
until last day 100%

Many thanks
John