Percentages based on description criteria

Panoos64 · Sep 10, 2017

Hi all, I would like to create a vba code so that to prepare the percentages in col. C, E, G, I, K, based on categories descriptions criteria, as my below P & L. extract. In sched. 1 are the original data and in sched. 2 is the expected result. Due to much more rows, i present an extract. Just i need a support to begin and then i will adapt it to whole spreadsheet. I would be appreciated any support. Many thanks in advance.

Sched.1

	A	B	C	D	E	F	G	H	I	J	K
4	JULY / 2017
5	Account Description	THIS MONTH P & L	%	THIS YEAR P & L	%	L. Y. MONTHLY P & L	%	L.Y. ACCUM. P & L	%	VARIANCES	%
9	Beverage income	265.125		1.200.122		250.487		1.100.236		99.886
10	Other income	12.854		60.452		12.489		58.478		1.974
11	TOTAL INCOME	1.177.824		5.915.825		1.163.559		5.682.022		233.803
12
15	Printing / stationery	5.245		20.145		6.785		30.147		-10.002
16	Fuel & gaz	7.865		35.137		9.745		42.745		-7.608
17	Telephones / postages	6.748		32.745		8.456		41.857		-9.112
18	OPERATING EXPENSES	230.158		1.151.172		240.569		1.174.330		-23.158

Sched.2

	A	B	C	D	E	F	G	H	I	J	K
4	JULY / 2017
5	Account Description	THIS MONTH P & L	%	THIS YEAR P & L	%	L. Y. MONTHLY P & L	%	L.Y. ACCUM. P & L	%	VARIANCES	%
9	Beverage income	265.125	22,51%	1.200.122	20,29%	250.487	21,53%	1.100.236	19,36%	99.886	42,72%
10	Other income	12.854	1,09%	60.452	1,02%	12.489	1,07%	58.478	1,03%	1.974	0,84%
11	TOTAL INCOME	1.177.824	100,00%	5.915.825	100,00%	1.163.559	100,00%	5.682.022	100,00%	233.803	100,00%
12
15	Printing / stationery	5.245	1,90%	20.145	1,48%	6.785	2,38%	30.147	2,19%	-10.002	64,49%
16	Fuel & gaz	7.865	2,85%	35.137	2,58%	9.745	3,42%	42.745	3,10%	-7.608	49,06%
17	Telephones / postages	6.748	2,45%	32.745	2,40%	8.456	2,97%	41.857	3,04%	-9.112	58,75%
18	OPERATING EXPENSES	230.158	83,52%	1.151.172	84,45%	240.569	84,37%	1.174.330	85,18%	-23.158	149,32%

Panoos64 · Sep 12, 2017

The above you wrote is based to " TOTAL EXPENSES" which it doesn't appear on my first example. You should checked my second spreadsheets. There are more rows above and so the "TOTALS" are not balance, but i have to create the vba based to the above data. Thanks

oldbrewer · Sep 12, 2017

ok - I fully understand now. It is important that you tell us which rows are subtotals eg it might be rows 2 to 10 are totalled in row 11, rows 12 to 30 are totalled in row 31. Then the macro will be really easy. If I have time later I will make a simplified model.

Panoos64 · Sep 12, 2017

Just 2 totaled rows exists on my P & L. One for "TOTAL INCOME" and the other "TOTAL EXPENSES" Simply i need to divide any income a/c / "TOTAL INCOME" and any expenses a/c / "TOTAL EXPENSES"

The point is that, the totaled rows ARE NOT STABLE and so the code should run through col. "A" and where find row's description "TOTAL INCOME" should divided the a/cs which are up (above). The same procedure should do with "TOTAL EXPENSES" Note that between totaled rows exist also blank rows. However i appreciated so much your attempt to solve my issue.
Many thanks

oldbrewer · Sep 12, 2017

sales1	5000	41.66667
sales2	7000	58.33333
total sales	12000

cost1	1500	25

cost2	2000	33.33333	this macro has inserted the 5 percentages

			For j = 1 To 2
cost3	2500	41.66667	If Cells(j, 2) <> "" Then Cells(j, 3) = 100 * Cells(j, 2) / Cells(3, 2)
			Next j
total costs	6000		For k = 4 To 11
			If Cells(k, 2) <> "" Then Cells(k, 3) = 100 * Cells(k, 2) / Cells(15, 2)
			Next k
profit	6000		End Sub

oldbrewer · Sep 12, 2017

very easy to add the 100% 's total sales and costs

Panoos64 · Sep 13, 2017

Thank you oldb, somehow it works but just for first category "total sales" and if i enter the 100% by manual method and write off the "next k". Also It doesn't run to prepare the % for numbers up to "total cost". It pause to this point, if Cells(k, 2) <> "" Then.
In addition the code should run for col. E, G, I, AND K. I feel very bad that you try for my project to solve it. Do not worry if is so complicate. I will use manual method.

Thanks once again

oldbrewer · Sep 13, 2017

no problem - I was demonstrating the principle - my example had 2 sales lines and 3 cost lines, with some blank rows. Have you any experience of macros - it is fairly easy to now apply the code to your situation.

Panoos64 · Sep 13, 2017

I have a little bit experience and i will study your code so that to adapt it on my data and i will let you know the soonest. Many thanks for your advice and your support. Best wishes!

oldbrewer · Sep 13, 2017

Jul-17		6	tot inc		13	op exp		2	Acc Descr
Account Description	THIS MONTH P & L	%	THIS YEAR P & L	%	L. Y. MONTHLY P & L	%	L.Y. ACCUM. P & L	%	VARIANCES	%
Beverage income	265.13	95.4	1200.1	95.2	250.487	95.3	1100.24	95.0	99.9

Other income	12.854	4.6	60.452	4.8	12.489	4.7	58.478	5.0	2.0
TOTAL INCOME	277.98		1260.6		262.976		1158.72		101.9

Printing / stationery	5.245	26.4	20.145	22.9	6.785	27.2	30.147	26.3	-10.0

Fuel & gaz	7.865	39.6	35.137	39.9	9.745	39.0	42.745	37.3	-7.6
Telephones / postages	6.748	34.0	32.745	37.2	8.456	33.8	41.857	36.5	-9.1

OPERATING EXPENSES	19.858		88.027		24.986		114.749		-26.722




									macro
although this is a self contained sheet with only 2 rows
									Sub Macro4()
extra rows can be added and the % columns will update automatically					'
									' Macro4 Macro
the numbers in row 1 are detecting where the data starts and					' Macro recorded 13/09/2017 by bob
which row TOTAL INCOME and OPERATING EXPENSES are on					'

the macro uses these as reference points							'
									For k = Cells(1, 9) + 1 To Cells(1, 3) - 1
the macro has put in every % value								For z = 3 To 9 Step 2
									If Cells(k, z - 1) <> "" Then Cells(k, z) = 100 * Cells(k, z - 1) / Cells(Cells(1, 3), z - 1)
not sure about the variance column								Next z
variance to what									Next k
and can % be applied to variances								For k = Cells(1, 3) + 1 To Cells(1, 6) - 1
									For z = 3 To 9 Step 2
									If Cells(k, z - 1) <> "" Then Cells(k, z) = 100 * Cells(k, z - 1) / Cells(Cells(1, 6), z - 1)
									Next z
									Next k
									End Sub

oldbrewer · Sep 13, 2017

I am not finished yet - (smiles)

Percentages based on description criteria

Panoos64

Well-known Member

Panoos64

Well-known Member

Excel Facts

oldbrewer

Well-known Member

Panoos64

Well-known Member

oldbrewer

Well-known Member

oldbrewer

Well-known Member

Panoos64

Well-known Member

oldbrewer

Well-known Member

Panoos64

Well-known Member

oldbrewer

Well-known Member

oldbrewer

Well-known Member

Similar threads

Forum statistics

Share this page

Percentages based on description criteria

Well-known Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock