Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I automatically update stock prices? | Excel Discussion (Misc queries) | |||
Link two cells to automatically update | Excel Discussion (Misc queries) | |||
automatically update a number in cell when template opens | Excel Discussion (Misc queries) | |||
How do i update hyperlink in excel spread sheet automatically. | Excel Discussion (Misc queries) | |||
automatically update chart plotting current month and previous 6 | Charts and Charting in Excel |