[Solved] Limiting the result of formula to maximum number

Discuss the spreadsheet application
Post Reply
theinspector
Posts: 3
Joined: Wed Mar 25, 2015 11:38 am

[Solved] Limiting the result of formula to maximum number

Post by theinspector »

I would like to multiply the value in a number cells by one number and divide it by another with the result rounded to an even number. Is it possible to place a "floor" on the results using the one formula to achieve both results.
At the moment I'm using the formula =ROUND(A1*124/113) to achieve the first part of the calculation. The answers I get range from 0-55. Is there anything I can add to this formula to limit the answers to a maximum of say, 45, or do I have to start another column using the formula, FLOOR(a1;45)

Thanks

Open Office 4.1.1 Windows Vista
Last edited by Hagar Delest on Thu Mar 26, 2015 4:10 am, edited 1 time in total.
Reason: tagged [Solved].
Open Office 4.1 - Windows Vista
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Limiting the result of formula to maximum number

Post by gerard24 »

Use MIN:

Code: Select all

=MIN(your_formula;45)
Inversely you can use MAX for limiting to a minimun number and MEDIAN for both limitation (low and high):
=MAX(your_formula;10) (Result is 10 or more)
=MEDIAN(your_formula;10;20) (Result is always incuded in the range [10,20])
LibreOffice 6.4.5 on Windows 10
theinspector
Posts: 3
Joined: Wed Mar 25, 2015 11:38 am

Re: Limiting the result of formula to maximum number

Post by theinspector »

Thanks Gerard,
Your suggestion does what I want as far as limiting the results go but it also stops the rounding process that I need. I guess what I'm after, if feasible, is a combination of your formula and my original formula. e.g., something like =MIN(ROUND(A1*124/113;45), so that I get, the multiplication, division, rounding and a maximum result of 45 from the one formula.
As I've said, this may not be achievable with the one formula.

Theinspector
Open Office 4.1 - Windows Vista
User avatar
keme
Volunteer
Posts: 3799
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Limiting the result of formula to maximum number

Post by keme »

Easily achievable with one formula using those two functions. In fact, you are almost there. You only need a closing parenthesis to delimit the round function.
=MIN(ROUND(A1*124/113);45)
Apache OO 4.1.16 and LibreOffice 25.8, mostly on Ms Windows 10 and 11.
theinspector
Posts: 3
Joined: Wed Mar 25, 2015 11:38 am

Re: Limiting the result of formula to maximum number

Post by theinspector »

EXCELLENT, SOLVED

Thanks to both of you - through my inexperience I didn't interpret what Gerard was saying correctly. Had I done so I wouldn't have needed to re-post.
Thanks again,
theinspector
Open Office 4.1 - Windows Vista
Post Reply