Automatically enter a percent for each month of the year.

[Jman]

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?

 

[JLatham]

=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.

 

[Martin Fishlock]

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]

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."

 

[Jman]

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.