Percentages in Excel

There are no specific functions for calculating percentages.
You have to use the skills you were taught in your maths class at school!
Finding a percentage of a value
	Initial value	120
	% to find	25%
	Percentage value	30	=D8*D9
	Example 1
	A company is about to give its staff a pay rise.
	The wages department need to calculate the increases.
	Staff on different grades get different pay rises.
	Grade	% Rise
	A	10%
	B	15%
	C	20%
	Name	Grade	Old Salary	Increase
	Alan	A	£      10,000	£      1,000	=E23*LOOKUP(D23,$C$18:$C$20,$D$18:$D$20)
	Bob	B	£      20,000	£      3,000	=E24*LOOKUP(D24,$C$18:$C$20,$D$18:$D$20)
	Carol	C	£      30,000	£      6,000	=E25*LOOKUP(D25,$C$18:$C$20,$D$18:$D$20)
	David	B	£      25,000	£      3,750	=E26*LOOKUP(D26,$C$18:$C$20,$D$18:$D$20)
	Elaine	C	£      32,000	£      6,400	=E27*LOOKUP(D27,$C$18:$C$20,$D$18:$D$20)
	Frank	A	£      12,000	£      1,200	=E28*LOOKUP(D28,$C$18:$C$20,$D$18:$D$20)
Finding a percentage increase
	Initial value	120
	% increase	25%
	Increased value	150	=D33*D34+D33
	Example 2
	A company is about to give its staff a pay rise.
	The wages department need to calculate the new salary including the % increase.
	Staff on different grades get different pay rises.
	Grade	% Rise
	A	10%
	B	15%
	C	20%
	Name	Grade	Old Salary	Increase
	Alan	A	£      10,000	£    11,000	=E48*LOOKUP(D48,$C$18:$C$20,$D$18:$D$20)+E48
	Bob	B	£      20,000	£    23,000	=E49*LOOKUP(D49,$C$18:$C$20,$D$18:$D$20)+E49
	Carol	C	£      30,000	£    36,000	=E50*LOOKUP(D50,$C$18:$C$20,$D$18:$D$20)+E50
	David	B	£      25,000	£    28,750	=E51*LOOKUP(D51,$C$18:$C$20,$D$18:$D$20)+E51
	Elaine	C	£      32,000	£    38,400	=E52*LOOKUP(D52,$C$18:$C$20,$D$18:$D$20)+E52
	Frank	A	£      12,000	£    13,200	=E53*LOOKUP(D53,$C$18:$C$20,$D$18:$D$20)+E53
Finding one value as percentage of another
	Value A	120
	Value B	60
	A as % of B	50%	=D59/D58
	You will need to format the result as % by using the % button
	on the toolbar.
	Example 3
	An manager has been asked to submit budget requirements for next year.
	The manger needs to specify what will be required each quarter.
	The manager knows what has been spent by each region in the previous year.
	By analysing the past years spending, the manager hopes to predict
	what will need to be spent in the next year.
	Last years figures
	Region	Q1	Q2	Q3	Q4
	North	9,000	2,000	9,000	7,000
	South	7,000	4,000	9,000	5,000
	East	2,000	8,000	7,000	3,000
	West	8,000	9,000	6,000	5,000	Total
	Total	26,000	23,000	31,000	20,000	100,000
	Last years Quarters as % of last years Total
	Region	Q1	Q2	Q3	Q4
	North	9%	2%	9%	7%	=G74/$H$78
	South	7%	4%	9%	5%	=G75/$H$78
	East	2%	8%	7%	3%	=G76/$H$78
	West	8%	9%	6%	5%	=G77/$H$78
	Total	26%	23%	31%	20%	=G78/$H$78
	Next years budget		150,000
	Next years estimated budget requirements
	Region	Q1	Q2	Q3	Q4
	North	13,500	3,000	13,500	10,500	=G82*$E$88
	South	10,500	6,000	13,500	7,500	=G83*$E$88
	East	3,000	12,000	10,500	4,500	=G84*$E$88
	West	12,000	13,500	9,000	7,500	Total
	Total	39,000	34,500	46,500	30,000	150,000
Finding an original value after an increase has been applied
	Increased value	150
	% increase	25%
	Original value	120	=D100/(100%+D101)
	Example 4
	An employ has to submit an expenses claim for travelling and accommodation.
	The claim needs to show the VAT tax portion of each receipt.
	Unfortunately the receipts held by the employee only show the total amount.
	The employee needs to split this total to show the original value and the VAT amount.
	VAT rate	17.50%
	Receipt	Total	Actual Value	Vat Value
	Petrol	£   10.00	£          8.51	£        1.49	=D113-D113/(100%+$D$110)
	Hotel	£ 235.00	£      200.00	£      35.00
	Petrol	£ 117.50	£      100.00	£      17.50
			=D115/(100%+$D$110)

Related Posts by Categories