[Solved] Constrain maximum percentage to 100%

Discuss the spreadsheet application
Post Reply
Excempt
Posts: 4
Joined: Mon May 24, 2021 2:10 am

[Solved] Constrain maximum percentage to 100%

Post by Excempt »

Hello,

Am new to OpenOffice forum, i did a quick search but couldn't find anything,

i need some help for preventing on going to 101% or above.

i got some basic work and % worked out, but once i exceed the numbers i get percentages of 101% and higher, how do i prevent this?

example ; i got 1,000 units
have ; 1,005 units
missing ; -5 units
percentage ; 101%

what am I missing?

if you look at the 3rd % it hits 101%
any help is appreciated

here is my problem ; https://imgur.com/IIhw82f
percentages.png
Last edited by MrProgrammer on Tue May 25, 2021 4:03 pm, edited 2 times in total.
Reason: Image added to post as link may not be permanent; Tagged ✓ [Solved]
OpenOffice 4.15
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: 101% what?

Post by Zizi64 »

Please upload your real sample file here. There is not any formula to examine on the attached image.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Excempt
Posts: 4
Joined: Mon May 24, 2021 2:10 am

Re: 101% what?

Post by Excempt »

the TOTAL row is =C7*E7 and the same in descending order the missing is =G7-I7 , and the percentages is =I7/G7
and similar in descending order

the percentage cells are formatted in percentages.
OpenOffice 4.15
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: 101% what?

Post by Villeroy »

Please upload your real sample file here. There is not anything to examine on the attached image.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: 101% what?

Post by robleyd »

Is your question intended to be
If the percentage calculated is greater than 100%, I only want to display 100%
?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: 101% what?

Post by Villeroy »

Have a look at the formula bar for the cell values in A1, A2, A3.
Attachments
t105312.ods
Bad number format, correct result
(8.31 KiB) Downloaded 91 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Excempt
Posts: 4
Joined: Mon May 24, 2021 2:10 am

Re: 101% what?

Post by Excempt »

robleyd wrote:Is your question intended to be
If the percentage calculated is greater than 100%, I only want to display 100%
?
yes. i want it to stop at 100% no matter how much excess there is!
OpenOffice 4.15
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: 101% what?

Post by robleyd »

Ah, now we know what your problem really is. Just use IF to determine whether the result of your calculation is greater than 1 (hundred percent) and if so, just display 1 (hundred percent). As you haven't shown your formulae I can't give you a specific example.

Adjust to your needs, assuming the cell where the formula resides is formatted as percentage.:

Code: Select all

=IF(A1/B1 >1;1;A1/B1)
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: 101% what?

Post by Villeroy »

=MIN(1;A1/B1) the smaller value of either 1 or A1/B1
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Excempt
Posts: 4
Joined: Mon May 24, 2021 2:10 am

Re: 101% what?

Post by Excempt »

robleyd wrote:Ah, now we know what your problem really is. Just use IF to determine whether the result of your calculation is greater than 1 (hundred percent) and if so, just display 1 (hundred percent). As you haven't shown your formulae I can't give you a specific example.

Adjust to your needs, assuming the cell where the formula resides is formatted as percentage.:

Code: Select all

=IF(A1/B1 >1;1;A1/B1)

Thank you !!

that worked like a charm :lol: :super:
OpenOffice 4.15
Post Reply