Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
percentage calculation
If my figure in Column A2 is $53,000 for annual sales and my column A$ is
$75,000 for the budget amount, how to calculate the percentage of the sales amount to the budget? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
percentage calculation
Hi,
I assume that the 75000 are in cell A3, so in cell A4 enter =A2/A3 format as % or if you don't want to format as % use =A2/A3*100 "rpi929" wrote: If my figure in Column A2 is $53,000 for annual sales and my column A$ is $75,000 for the budget amount, how to calculate the percentage of the sales amount to the budget? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
percentage calculation
================================================== ===========
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 werent 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: ToolsOptions, 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 "rpi929" wrote in message ... If my figure in Column A2 is $53,000 for annual sales and my column A$ is $75,000 for the budget amount, how to calculate the percentage of the sales amount to the budget? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
percentage calculation
"rpi929" wrote:
If my figure in Column A2 is $53,000 for annual sales and my column A$ is $75,000 for the budget amount, how to calculate the percentage of the sales amount to the budget? I assume 75000 is in A4. Then sales as a percentage of budget (forecast) is: =A2/A4 formatted as Percentage (click Format Cells Number). If you want to calculate percentage increase of sales over budget (forecast): =A2/A4 - 1 formatted, again, as Percentage. In both case, if the budget (forecast) might be zero, it would be prudent to write; =if(A4=0, 1, A2/A4) =if(A4=0, 1, A2/A4 - 1) It should be noted that the choice of 1 (100%) when A4=0 is arbitrary. Caveat emptor: post back for more help if either A2 or A4 can be negative. (Don't scoff at negative sales. I've seen it happen when returns from a previous time period exceeds sales in the current time period.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage calculation | Excel Discussion (Misc queries) | |||
percentage calculation | Excel Discussion (Misc queries) | |||
'of' percentage with calculation | Excel Discussion (Misc queries) | |||
Percentage calculation | Excel Discussion (Misc queries) | |||
Percentage Calculation | Excel Worksheet Functions |