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
[Solved] Limiting the result of formula to maximum number
-
theinspector
- Posts: 3
- Joined: Wed Mar 25, 2015 11:38 am
[Solved] Limiting the result of formula to maximum number
Last edited by Hagar Delest on Thu Mar 26, 2015 4:10 am, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
Open Office 4.1 - Windows Vista
Re: Limiting the result of formula to maximum number
Use MIN:
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])
Code: Select all
=MIN(your_formula;45)=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
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
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
Re: Limiting the result of formula to maximum number
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)
=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
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
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