Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically enter a percent for each month of the year.
In A1 thru L1 i have months: A1=Jan, B1=Feb, C1=Mar etc
In A2 thru L2 will be total for each month. In A19 i have a percentage ex "34%" that varies up and down every day.. In A20 i have "today()" date ex 3-23-08 At the end of the Month i want the percentage in A19 "34%" to automatically log in to the corresponding month ex. today date 3-23-08 to C2 March Is there a formula for this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically enter a percent for each month of the year.
=OFFSET(A2,0,MONTH(A20)-1)/SUM(A2:OFFSET(A2,0,MONTH(A20)-1))
put that into A19 and you should get the March total as a percentage of the totals from January through March (assuming date in A20 is a day in March). That will give a #DIV/0! error if you've not entered any values into row 2, so you may want to modify that to 'hide' the error situation, change formula in A19 to: =IF(ISERROR(OFFSET(A2,0,MONTH(A20)-1)/SUM(A2:OFFSET(A2,0,MONTH(A20)-1))),"",OFFSET(A2,0,MONTH(A20)-1)/SUM(A2:OFFSET(A2,0,MONTH(A20)-1))) If you want the value in A19 to be the percentage of total sales for the entire year, then: =OFFSET(A2,0,MONTH(A20)-1)/SUM(A2:L2) Might be useful as an added computation somewhere on your sheet - you could use a different cell from A20 to just type in a date to see the percentage for a given month when the year's totals have all been entered. "Jman" wrote: In A1 thru L1 i have months: A1=Jan, B1=Feb, C1=Mar etc In A2 thru L2 will be total for each month. In A19 i have a percentage ex "34%" that varies up and down every day.. In A20 i have "today()" date ex 3-23-08 At the end of the Month i want the percentage in A19 "34%" to automatically log in to the corresponding month ex. today date 3-23-08 to C2 March Is there a formula for this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically enter a percent for each month of the year.
Hi:
Sorry there is no formula that allows copying a value from the month to the summary. What you can do is one of the following: Where an on open macro that copies the data if todays date falls in the following month; or Use an event macro to get the data when it is keyed in. Both these approaches use VBA and there are some careful considerations to take into place about then is the data final for the month. Another option is to have a button on the sheet that copies the data to the respective cell. Good luck. Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jman" wrote: In A1 thru L1 i have months: A1=Jan, B1=Feb, C1=Mar etc In A2 thru L2 will be total for each month. In A19 i have a percentage ex "34%" that varies up and down every day.. In A20 i have "today()" date ex 3-23-08 At the end of the Month i want the percentage in A19 "34%" to automatically log in to the corresponding month ex. today date 3-23-08 to C2 March Is there a formula for this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically enter a percent for each month of the year.
Sounds like one of these solutions you mentioned migh do the trick.. .the only problem is i dont know how to create a macro, do love to paste it though :) This option migh work the beset: " Where an on open macro that copies the data if todays date falls in the following month; or Use an event macro to get the data when it is keyed in." "Martin Fishlock" wrote: Hi: Sorry there is no formula that allows copying a value from the month to the summary. What you can do is one of the following: Where an on open macro that copies the data if todays date falls in the following month; or Use an event macro to get the data when it is keyed in. Both these approaches use VBA and there are some careful considerations to take into place about then is the data final for the month. Another option is to have a button on the sheet that copies the data to the respective cell. Good luck. Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jman" wrote: In A1 thru L1 i have months: A1=Jan, B1=Feb, C1=Mar etc In A2 thru L2 will be total for each month. In A19 i have a percentage ex "34%" that varies up and down every day.. In A20 i have "today()" date ex 3-23-08 At the end of the Month i want the percentage in A19 "34%" to automatically log in to the corresponding month ex. today date 3-23-08 to C2 March Is there a formula for this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically enter a percent for each month of the year.
IT"S OK. i found a different way.. i'll get my lazy butt and put in the
percentage manually at the end of each month. Thanks. "Jman" wrote: Sounds like one of these solutions you mentioned migh do the trick.. .the only problem is i dont know how to create a macro, do love to paste it though :) This option migh work the beset: " Where an on open macro that copies the data if todays date falls in the following month; or Use an event macro to get the data when it is keyed in." "Martin Fishlock" wrote: Hi: Sorry there is no formula that allows copying a value from the month to the summary. What you can do is one of the following: Where an on open macro that copies the data if todays date falls in the following month; or Use an event macro to get the data when it is keyed in. Both these approaches use VBA and there are some careful considerations to take into place about then is the data final for the month. Another option is to have a button on the sheet that copies the data to the respective cell. Good luck. Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jman" wrote: In A1 thru L1 i have months: A1=Jan, B1=Feb, C1=Mar etc In A2 thru L2 will be total for each month. In A19 i have a percentage ex "34%" that varies up and down every day.. In A20 i have "today()" date ex 3-23-08 At the end of the Month i want the percentage in A19 "34%" to automatically log in to the corresponding month ex. today date 3-23-08 to C2 March Is there a formula for this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
Enter day, default month and year | Excel Discussion (Misc queries) | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
trying to get day/month/year froamt while user enters year only | New Users to Excel | |||
How do you change the year but not the month automatically? | Excel Discussion (Misc queries) |