2

In LibreOffice Calc, I have data in one column. I need to calculate the percentage of a whole column of data without filling the percentage column one by one, without writing the formula one by one when the percentage is the same, like in this case 15%. And I want to use the formula %, not multiplying by 0.15 or 0.10. Maybe this is easier than we think.

For instance, I have:

10    15  15% of 10     answer
20    15  15% of 20     answer
50    15  15% of 50     answer
76    15  15% of 76     answer
90    10  10% of 90     answer
100   15  15% of 100    answer

I hope you understand my English; it is not my first language.

2
  • Your English seems okay, but the example is not. Please edit the question to give a complete example, including input and desired output.
    – Jim K
    Commented Nov 8, 2023 at 15:44
  • Are you saying you want it to literally say "15% of 10" as text, or simply to calculate that answer?
    – Jim K
    Commented Nov 21, 2023 at 1:14

3 Answers 3

2

I'll take a stab at it. I suppose you want to have 15% of your data so that your table becomes this:

Line/Row A B
1 My data 15% of my data
2 10 1.5
3 20 3
4 50 7
5 76 11.4

If that's the case, go to the cell "B2" and add the formula: =(A2*0.15) You can then drag down the corner of the cell "B2" and excel (or calc) will adapt the formula automatically.

EDIT: Jim K. also suggested a better approach which would allow you to change the "fixed" percentage if needed be.

Line/Row A B
1 My data 0.15
2 10 1.5
3 20 3
4 50 7
5 76 11.4

The formula in cell "B2" would be: =(A2*$B$1) You could then change the formatting of the cell as he suggested and you'd see percentages. As for other methods, drag the corner of "B2" down to copy the formula.

If instead you want your percentages to change according to a value you had already set in a column, replace 0.15 by that cell. Example:

Line/Row A B C
1 My data My percentages My result
2 10 15 1.5
3 20 15 3
4 50 50 25
5 76 100 76

In which case, the formula would need to be in the cell "C1" and should read: =(A2*B2/100) Then select the corner of "C1" and drag it down.

1
  • How do you do that table here in Stackexchange, you are a genious.
    – Feuergeist
    Commented Nov 20, 2023 at 19:25
0

Maybe you want to fill down =A1*B$1/100, with cells B2 to B4 empty. The $ means that row 1 in the second part of the formula is fixed — the reference won't adjust as the formula is filled.

In that case, it would be simpler to change the value in column B to an actual percentage, 0.15, which you can optionally change to display as 15% with cell number formatting.

A B C
10 0.15 =A1*B$1
20 =A2*B$1

EDIT:

Here is a solution that makes it so you only have to enter the percentage when it changes. The complex LOOKUP parameters are a common trick to find the last non-empty cell.

A B C
10 15% =A1*LOOKUP(2;1/(B1:B$1<>"");B1:B$1)
20 3
50 7.5
76 11.4
90 10% 9
100 15% drag formula down from C1 to fill this column
=A6*LOOKUP(2;1/(B$1:B6<>"");B$1:B6)
4
  • The problem is that I need to learn how to use % function. Can you give me an example using it in something similar to this case?
    – Feuergeist
    Commented Nov 12, 2023 at 18:50
  • It is not clear what you are asking about. kbftech and I have tried to guess, but you have not provided any more information.
    – Jim K
    Commented Nov 13, 2023 at 17:01
  • I understand what you are saying. In librecalc you have a button "%", I want to use that function.
    – Feuergeist
    Commented Nov 20, 2023 at 19:29
  • I'm not sure why you are struggling with 15% because 0.15 is the same value. In Calc, you can write the number either way, and you can format the percentage in Format > Cells. Regardless, I've added another answer to match the updated question.
    – Jim K
    Commented Nov 21, 2023 at 1:11
0

To convert a whole number (x) to a percentage (y), divide by 100.
If x = 15, then y = 15 ÷ 100 = 15% (or represented differently 0.15)

Here are some screenshots of how I did it in LibreOffice Calc

Image showing formula -
Image showing formula

Image showing answers -
Image showing answers

Image showing answers after clicking on % symbol -
Image showing answers with percentages

The % button changes the way a cell is displayed, but does not alter the contents of the cell itself.
When % button is clicked, you are instructing LibreOffice to change how the number is interpreted.
1 becomes 100% (because 100% of 1 = 1; 1 = 100/100) and 15 becomes 1500.00% (because 1500% of 1 = 15; 15 = 1500/100).

See image below for example -
Image showing how percentages of numbers are shown

Visit LibreOffice help page here for more examples

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .