Excel: So you expect the calculations with percentages in fields with percentage values

6:55 pm April 6, 201710215

and displaying percentage values is one of the easier tasks in Excel. Nevertheless, there are to observe a lot himself to make as easy as possible the work.

the basics of the Prozentrechnens in Excel

suppose you are seller and get a 7% Commission on all goods you bring successfully to the man.

calculate sale price given their share of one – quite apart from Excel – according to the formula

Commission = price / 100 * Commission rate

for a Commission rate of 7% take time so the achieved selling price of 0.07 (7 / 100).

you want to now create a list of your sales including the calculation of commissions in Excel, then you do for example:

Excel einfache Prozentrechnung
a simple percentage calculation in Excel

the representation of values with % character

now is display the percentage value from the last example with “0.07” Although mathematically in order , but ugly looks. That there is a formatting in Excel specifically for percentages.

this display variant select cells either via a right click on a cell and the option format % or even easier – category – the % button of the start tab .

Prozent-Knopf in Excel
find the percent button in Excel

who works better with the keyboard, use the alternative [Strg – Umschalten – 5] .

the percentage format adds not only to the percent sign, but also affects the displayed value.

Wirkung der Prozentformatierung
the effect of percentage formatting

so the representation looks just much more familiar from. Does not change is the result of the calculation for the actual value of the cell remains at 0.07 and only the display is adjusted to that it will be easier to understand. Remember that the percentage formatting for this thought thats to provide us humans with the gewohnteren numbers. Excel calculates internally different.

you would increase the value now, for example, by typing “9”, Excel changes the actual cell contents on 0,09.

so beat percent or from

you want to calculate a percentage premium, then you need a factor that is so composed:

1 + (percentage / 100)

to make a retail price including 19% value added tax net , you need, for example, by a factor of 1,19.

a net price of 200 euros is multiplying it by this factor to 200 * 1.19 = 238 euros.

vice versa you determine from a gross net dividing this factor. For example, a used car is €8.330 gross, then is my net worth 8330 / 1.19 = 7000 euro.

can use these formulas in Excel and the parts save money is by 100, if you are working with percentage formatting.

the following snippet calculates, for example, the net worth of the gross value entered in A1:

Excel Prozente abschlagen
Excel percent knock off

to locate percentages

you want to know how much percent, a value of another must divide the value by the base value.

a concrete example: A car you lease costs new 20,000 euro. After three years, its residual value is still 7000 euro. To determine now how much percent the residual value to the new value.

to add: residual value / replacement value or in Excel:

Excel Restwert in Prozent
Excel residual percentage

the result of the calculation for field B3 is first 0.35. Again, this is the internal point of view of Excel. After the change in the percentage format see the result “35%” as shown in the screenshot.

know that you in Excel formulas use names instead of cell addresses can?

similar tips