Formula for calculating percentage

Discuss the spreadsheet application
Post Reply
Jaxon's Dad
Posts: 2
Joined: Fri Feb 19, 2010 7:06 pm
Location: Lansing, MI

Formula for calculating percentage

Post by Jaxon's Dad »

So glad there is a community for OpenOffice! Hopefully someone here has the answer.

I'm working on a budget spreadsheet in Calc made up of spending categories. What I'm trying to do is determine a formula for the percentage of monthly income spent in each category. For example, the percentage of charitable giving on a net monthly income of X. I did some searching on the web and found the following formula:

=[Field]%/[Monthly Income]

This seems to do the trick except that the percentage value follows a decimal. For example 25% looks like 0.25. Not a huge deal, but I'd really prefer to have the percentages look less confusing. I would also like to have the formula calculate to the nearest tenth of a percent in case a given category's bottom line works ot to be 25.6% of income, for example. Any help is most appreciated.

Thanx in advance,

JD
There are three things I can't stand: 1)Intolerance; and 2)People that can't count.
OpenOffice 3.1 on Windows Vista
jiml9
Posts: 21
Joined: Mon Feb 01, 2010 8:26 pm

Re: Formula for calculating percentage

Post by jiml9 »

Don't have access to my OOo 3.2 install at this time, but on 2.3, it works like this;

Right click on the cell containing the decimal. Click on Format Cells, then Numbers, and set it to percent.
LibO 3.4.5 on WinXP, Kubuntu 11.10
User avatar
TheGurkha
Volunteer
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: Formula for calculating percentage

Post by TheGurkha »

Highlight the cell(s) you want to format as percentage, right-click in one of them and then seelct Format Cells.
On the Numbers tab, in the Category list select the Percent option. Then use the options to specify the number of decimal places and the leading zeroes.

If this has answered your question please go to your first post and use the Edit button, and add [Solved] to the start of the title. You can use the green tick icon at the same time if you like.
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
Jaxon's Dad
Posts: 2
Joined: Fri Feb 19, 2010 7:06 pm
Location: Lansing, MI

Re: Formula for calculating percentage

Post by Jaxon's Dad »

Well, that solved the decimal placement issue, but the numbers aren't adding up. This makes me suspect that the formula itself has a problem with it. For instance, if monthly income is $2000.00 and charitable giving is $250.00, the percentage of income should be 12.5%. However, the formula (=field %/2500.00) renders the amount as 0%. Suggestions?
There are three things I can't stand: 1)Intolerance; and 2)People that can't count.
OpenOffice 3.1 on Windows Vista
MichelleL
Posts: 3
Joined: Fri Feb 19, 2010 10:57 pm

Re: Formula for calculating percentage

Post by MichelleL »

I just tried it and this is the formula that worked for me.

Say you have $2,000 in A1, and $250 in B1. And you want the percentage in cell C1.

[sorry, editing this because I had the formula switched around]

In C1, type =B1/A1

Then you'll get .13, right?

Well, highlight C1, then change the cell's formatting to % by going to the top menu and navigating to Format > Cells > Percent. Once you've chosen percent, then choose how many decimal places you want (default is two, but you might just want one decimal place).

That's what will change your .13 to 12.5%

Hope this helps!
OOo 3.1, Windows Vista (I know, it sucks!)
Post Reply