[Solved] Math Clamp Function?

[BomboBombom]

Hello I've been trying to make sure the code in my cells isn't several rows long as it gets quite hard to read and mod.
Thus instead of having to use IF statements to repeat the value of a calculation I've been trying to find a clamp function. But from what I can tell, there is no such thing.

The function I'm trying to find would allow me to input a number and then I should be able to pick the lowest allowed and highest allowed number. Or, just limit the number to 0.

Example:
=Clamp(5,0,4) returns 4
=Clamp(-341,0,4) returns 0
=Clamp(3,0,4) returns 3

codish example:

Code: Select all

clamp(in,min,max) {
 if (in > max return max)
 if (in < min return min)
 return in
}

Is there such a function or a similar one or must I use macros? The only macros I've seen had to be ran manually each time I wanted to calculate changes.

Have a great one

[Zizi64]

Is there such a function or a similar one or must I use macros? The only macros I've seen had to be ran manually each time I wanted to calculate changes.

The macro is a good solution for this task, but not needed run the macro manually. Just write the macro as a Cell Function:

Code: Select all

REM  *****  BASIC  *****

Function myClamp(inValue as double, minValue as double, maxValue as double) as double

	myClamp = inValue
	If inValue < minValue Then myClamp =  minValue
	If inValue > maxValue Then myClamp =  maxValue

end function 

Store the macro into MyMacros - Standard - Module 1.
Then you can use this function for all of the spreadsheet files on your PC.

Store the macro into the Standard directory of the spreadsheet document (into a Module), when you want to send the document. The macro will work when the recipient open the file.

[Zizi64]

Here is an example with the embedded macro. You need enable the macro running az Tool - Options - Open/LibreOffice - Security Macro security - choose the option: "Medium".

Otherwise it is not too difficult to handle this case with a regular formula (there is an example for it in the sample file too):

MyClamp.ods

(10.96 KiB) Downloaded 113 times

Note: There is not error handler in my macro. For example: what about, when the passed MINVALUE is greater then the MAXVALUE??

[BomboBombom]

Thank you one hundred million times Zizi64, you are awesome.