Excel: So you expect the calculations with percentages in fields with percentage values
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:
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 .
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.
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:
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:
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.