how to deduct a % from sum

  • Thread starter Thread starter SS
  • Start date Start date
S

SS

How would I change this formula so that I can deduct 5% of the calculation?
The 5% figure would be put in a seperate cell so that the % could be
altered, meaning 5% would be in say cell A2, and I might require to change
the 5% to say 7%.

The % figure relates to an inflation level to be deducted from a lump sum of
money.

=D9+D10-D11

My attempt...

=D9+D10-D11-(A2%)
 
In your case, depending on how you stored the percentage:

=(D9+D10-D11)*(1-A2)


But take a look at my standard reply about percentages in excel:

=============================================================
About percentages in Excel

Niek Otten, July 26 2006

In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100%
as 1. That makes it easy to calculate with; just multiply a number with a
percentage and you get what you need. No need to divide/multiply by 100. In
fact, if you see a calculation with percentages which has the number 100
somewhere in the formula; be very careful, it might be wrong or at least use
percentages in a way they weren't meant to be used in Excel.
The conversion to a fraction happens automatically if you enter the % sign:
if you enter "15%" (without the quotes) the value will be 0.15 and it will
be displayed as 15%. If you then enter 12 in the same cell, two things can
happen: It will be the number 12 or 12%. What happens in your case depends
on a setting:
Tools>Options, Edit tab, "Enable automatic percent entry" (only Excel2000
and newer).
All built-in functions of Excel and all the functions in Analysis Toolpak
use this representation of percentages: be careful when supplying parameters
to these functions; never use whole numbers (like 8), always use fractions
(like 0.08 or, even better, 8%).

Frequently Asked Questions:

Q:
I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
A:
Formula in C1: =B1/A1, Format as %

Q:
I have A1 and B1. How do I show the difference as a percentage in C1?
A:
As a percentage of A1: =(B1-A1)/A1, Format as %
As a percentage of B1: =(B1-A1)/B1, Format as %

Q:
In A1 I have he end price.
In B1 I have the VAT percentage, entered like 15%
What is the formula to find the base price, excluding VAT?
A:
=A1/(1+A2)

=============================================================
 
Thanks now working ok.

Niek Otten said:
In your case, depending on how you stored the percentage:

=(D9+D10-D11)*(1-A2)


But take a look at my standard reply about percentages in excel:

=============================================================
About percentages in Excel

Niek Otten, July 26 2006

In Excel, percentages are stored as fractions; 15% is stored as 0.15, 100%
as 1. That makes it easy to calculate with; just multiply a number with a
percentage and you get what you need. No need to divide/multiply by 100.
In fact, if you see a calculation with percentages which has the number
100 somewhere in the formula; be very careful, it might be wrong or at
least use percentages in a way they weren't meant to be used in Excel.
The conversion to a fraction happens automatically if you enter the %
sign: if you enter "15%" (without the quotes) the value will be 0.15 and
it will be displayed as 15%. If you then enter 12 in the same cell, two
things can happen: It will be the number 12 or 12%. What happens in your
case depends on a setting:
Tools>Options, Edit tab, "Enable automatic percent entry" (only Excel2000
and newer).
All built-in functions of Excel and all the functions in Analysis Toolpak
use this representation of percentages: be careful when supplying
parameters to these functions; never use whole numbers (like 8), always
use fractions (like 0.08 or, even better, 8%).

Frequently Asked Questions:

Q:
I have A1 and B1. How do I get C1 to show B1 as a percentage of A1?
A:
Formula in C1: =B1/A1, Format as %

Q:
I have A1 and B1. How do I show the difference as a percentage in C1?
A:
As a percentage of A1: =(B1-A1)/A1, Format as %
As a percentage of B1: =(B1-A1)/B1, Format as %

Q:
In A1 I have he end price.
In B1 I have the VAT percentage, entered like 15%
What is the formula to find the base price, excluding VAT?
A:
=A1/(1+A2)

=============================================================

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Click to expand...
 
Back
Top