2 + 46% = ?

June 16, 2007 5:50 AM

I need some really REALLY basic spreadsheet help. I've been given the task of creating a spreadsheet that will work out for us several things - how many orders we need in total and per week, how much revenue this will bring, etc. I've got most of it worked out, but I can't for the life of me how to work out percentages. Yes, I did Google this before asking. :D

I have a number, say 765. I need a formula to work out what various percentages of this are - 80% of 765 = nnnn, 45% of 765 = nnnn etc. I also need a formula that will add a percentage to this number - 765 + 19% = nnnn for example.

I'd prefer it if this was in the format of "multiply number by X then divide by Y then clap your hands 3 times", than something more esoteric. Mainly because I can get my head round that, and also I can prove it's working to my boss on a calculator (he is even more clueless at mathematics than I am!). I have a very dim recollection of multiply by 100 then divide by the percentage you want, but I can't get that to work.

I'm using OpenOffice Calc 2.0.4 to create the spreadsheet.

posted by Solomon to education (8 comments total)

X "per cent".

"cent" means 100, so X percent means "x / 100".

765 * (80 / 100) == 80% of 765.

An easier way to write "(80 / 100)" is "0.80".
posted by cmiller at 5:55 AM on June 16, 2007

One of the most important things that math students should learn is that the ideas actually mean something. Instead, woefully many of them approach it as pushing some symbols around and using some rules to transform them into other symbols.

"X per cent of N things" really means something, and if you think about it, it should become obvious: "For N things, for every hundred of them, take X".

If you were writing something that said you wanted 1 per three for 12 things, you'd understand that for every 3 things, you'd keep one. So, 1 perthree of 12 is 4, right?
posted by cmiller at 6:01 AM on June 16, 2007

And adding to cmiller, 765 + 19% is the same as 119% of 765 is the same as (119/100) * 765.
posted by commander_cool at 6:11 AM on June 16, 2007

How you'd actually set it up: one row, sort of a title row, would have your percentages. If you tell Excel to format it as a percentage, you type in "0.80" and it will show "80%".

I'll assume, for simplicity, that your base values are in the A column, and that they start in row 3: A3 is your first actual value. Row 2 would be filled with column headings of percent values: 80%, 60%, and so on. So B2 has 80%, B3 has 60%, B4 has 40%.

        A        B        C        D        

1    Units   Percentages
2               .80      .60      .40      
3    value1   =(B2*A3)  =(C2*A3) =(D2*A3)
4    value2   =(B2*A4)  =(C2*A4) =(D2*A4)
Note that you just have to type the formula in cell B3, and then copy it to the other cells. Excel should adjust the values. I think that formula is slightly wrong, in that the first argument (B2, C2, D2) should be "absolute", not "relative". There's a way to specify absolute addressing when copying formulas, but I don't remember it offhand, and I don't have a spreadsheet installed to look.

So, you'll need to adjust that a little bit, but it will at least give you the idea.

Note that for the second part of the question, adding a percentage.... you can use the same formula. Just make the percentage greater than 1. For 120%, type 1.20; for 133%, type 1.33.
posted by Malor at 7:10 AM on June 16, 2007

Frankly, if you are having trouble with something this basic I would hire someone to put together the spread sheet for you. This is not meant as an insult. Some people just never caught on to basic math skills or perhaps it is spreadsheet skills. Fine. However, without them you are just asking for trouble with this kind of thing.
posted by caddis at 8:02 AM on June 16, 2007

Malor: "...I think that formula is slightly wrong, in that the first argument (B2, C2, D2) should be "absolute", not "relative". There's a way to specify absolute addressing when copying formulas, but I don't remember it offhand, and I don't have a spreadsheet installed to look.

..."

Malor has it, but just to followup on his point, absolute references in Excel* are $B$2 (i.e. a $ in front of whatever you want to be absolute.) In case the difference isn't clear to you- if you move things around, you want relative references to move. Not true for relative references. In Malor's sample, I think you would want relative columns and absolute rows. (e.g. B$2)

* I googled, and OpenOffice uses the same format.
posted by JMOZ at 10:43 AM on June 16, 2007

It may not be clear from Malor's response that 'adding' a percentage means multiplying by a number greater than one. For example, 765 +19% is 765 * 1.19.

It makes sense if you think about it: 100% of a number is the number itself. 19% of a number is the number * 0.19. Start with the number (100%, = multiply by one) and add the extra (19%, = multiply by 0.19), so you get 119% overall (multiply by 1.19).
posted by PercussivePaul at 12:32 PM on June 16, 2007

Ok, with JMOZ's extra info, you'd want to modify the formula to be ($A3 * B$2), in the upper left corner, and then copy that formula to all the other cells. Everything should come out right.
posted by Malor at 4:45 PM on June 16, 2007