Formulas

[shirazbakar]

Hi,

I have two question would like to ask. I hope somebody can help me on this matter.

1. Right now I using macro,copy and paste to pickup the daily figure to month to date column. It is others way doing it. I already try using "if" function, but the file become quite big. I get tried to set macro for a new file when I need copy and paste for 30 or 31 every time.

2. What is the best formulae that I can used for sum Year to Date according to month. For example this month is Jan, so the grand total will show Jan only. Next month Feb so my grand total automaticly will sum Jan and Feb budget figure.

thank you ray:

 

[Aladin Akyurek]

...2. What is the best formulae that I can used for sum Year to Date according to month. For example this month is Jan, so the grand total will show Jan only. Next month Feb so my grand total automaticly will sum Jan and Feb budget figure...

Post a sample that shows the lay out of your data.

 

[plettieri]

Hi:

You might look here for some help on the install of Colo utility for displaying your worksheet...


http://www.mrexcel.com/board2/viewtopic.php?t=24049&highlight=colo+utility


plettieri

 

[shirazbakar]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
21	YEAR-TO-DATE
22			ACTUAL				BUDGET				LAST YEAR
23			ROOM	ARR	REVENUE		ROOM	ARR	REVENUE		ROOM	ARR	REVENUE
24			NIGHTS	RM	RM		NIGHTS	RM	RM		NIGHTS	RM	RM
25	FIT										-	-	-
26	CORPORATE										-	-	-
27	NON BUSINESS GROUP										-	-	-
28	TOUR / IT / WHOLESALE										-	-	-
29	CONTRACT										-	-	-
30	OTHERS
31	PACKAGES										-	-	-
32	MEETING										-	-	-
33	FIT DISCOUNT										-	-	-
34	ASSOCIATES RATE										-	-	-
35	TOTAL										-	-	-
MTD-YTD

 

[shirazbakar]

Thank you for reply my massage. This a sample lay out of my worksheet. I need to pickup figure specially on budget according to month. On my worksheet(same worksheet) I have key-in all the year budget. But on this section I need to pickup accumulated figure.If month jan it only pickup jan but if feb this will pickup jan and feb budget figure.

Thank you
shiraz

 

[Aladin Akyurek]

Thank you for reply my massage. This a sample lay out of my worksheet. I need to pickup figure specially on budget according to month. On my worksheet(same worksheet) I have key-in all the year budget. But on this section I need to pickup accumulated figure.If month jan it only pickup jan but if feb this will pickup jan and feb budget figure.

Thank you
shiraz

Please fill in numbers in your exhibit and state which must be picked up for YTD.

 

[shirazbakar]

Bmix 2004.xls
	A	B	C	D	E	F	G	H	I
21	YEAR-TO-DATE
22			ACTUAL				BUDGET
23			ROOM	ARR	REVENUE		ROOM	ARR	REVENUE
24			NIGHTS	RM	RM		NIGHTS	RM	RM
25	FIT							#DIV/0!	590,650
26	CORPORATE						37,955	262.39	9,958,890
27	NON BUSINESS GROUP						6,280	252.13	1,583,350
28	TOUR / IT / WHOLESALE						9,200	212.54	1,955,350
29	CONTRACT						7,230	120.00	867,600
30	OTHERS						9,483	184.38	1,748,453
31	PACKAGES						24,750	247.92	6,136,050
32	MEETING						2,680	279.42	748,840
33	FIT DISCOUNT						2,740	73.00	200,020
34	ASSOCIATES RATE						-	#DIV/0!	-
35	TOTAL						100,318	237.14	23,789,203
36	REVPAR						153.67
37
MTD-YTD

 

[shirazbakar]

This figure will pickup from budget. This figure will accumulate if month is change. refer the budget column

 

[shirazbakar]

Bmix 2004.xls
	R	S	T	U
6		JAN	FEB	MAR
7	ROOM NIGHTS
8	FIT	50	50	600
9	CORPORATE	2700	2950	3500
10	NON BUSINESS GROUP	630	750	1100
11	TOUR / IT / WHOLESALE	1300	850	900
12	CONTRACT	350	400	600
13	OTHERS	1507	1267	1012
14	PACKAGES	1750	2000	2000
15	MEETING	225	155	245
16	FIT DISCOUNT	310	290	310
17	ASSOCIATES RATE	0	0	0
18	TOTAL	8822	8712	10267
19	REVENUE
20	FIT	24,500	24,500	330,000
21	CORPORATE	634,600	699,150	938,000
22	NON BUSINESS GROUP	144,900	174,750	276,100
23	TOUR / IT / WHOLESALE	260,000	172,550	184,500
24	CONTRACT	42,000	48,000	72,000
25	WHOLESALE	312,077	259,266	149,783
26	PACKAGES	390,250	452,000	462,000
27	MEETING	58,500	40,300	63,700
28	FIT DISCOUNT	22,630	21,170	22,630
29	ASSOCIATES RATE	-	-	-
30	TOTAL	1,889,457	1,891,686	2,498,713
MTD-YTD

 

[Aladin Akyurek]

This figure will pickup from budget. This figure will accumulate if month is change. refer the budget column

So, you didn't post the sheet which houses the data from which to compute YTD. Care to post a scaled down version of your data sheet which seems to be as Mthly?