Set up Constraint in Xlsx

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Set up Constraint in Xlsx

Marcin Wyrwalski
Hello.

 I would like to ask, if there is a possibility to set up constraint to not allow user to write more than three decimall places in cell? I preparing and sending file to user, and user is supposed to return file with some data. I would like to set up constraints on required field, to allow only good values.
Has anybody have any ide it this can be done?

Have a Nice day
marcin Wyrwalski

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Set up Constraint in Xlsx

Rob Sargent
I guess I don't understand what is not "good" about extra digits after
the decimal point?  You can round to whatever precision you choose.
What if the user is using some tool to generate and insert the data and
it divides 1 by 3 and gets 0.3333333333.  You're going to make the user
trim that manually to 0.333.  That would not make me happy.

On 5/21/20 7:30 AM, Marcin Wyrwalski wrote:

> Hello.
>
>   I would like to ask, if there is a possibility to set up constraint to not allow user to write more than three decimall places in cell? I preparing and sending file to user, and user is supposed to return file with some data. I would like to set up constraints on required field, to allow only good values.
> Has anybody have any ide it this can be done?
>
> Have a Nice day
> marcin Wyrwalski
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Set up Constraint in Xlsx

Andreas Reichel
Rob,

On Thu, 2020-05-21 at 19:39 -0600, Rob Sargent wrote:
> I guess I don't understand what is not "good" about extra digits after
>
> the decimal point?

Very simple, but frequent use case: Loading data from a Spreadsheet
file into a database.
In this case, the read value is a FLOAT which needs to be forced into
DECIMAL(?,?). And this can be a real nightmare, because most of the End
Users just copy/paste the content from other data sources into the
Spreadsheet without thinking about it.

Also, certain data, like currency amounts or interest rates permit only
a fixed number of digits.

Spreadsheets actually do lack a BigDecimal data type.

Cheers


Reply | Threaded
Open this post in threaded view
|

Re: Set up Constraint in Xlsx

Andreas Reichel
In reply to this post by Marcin Wyrwalski
Marcin,

On Thu, 2020-05-21 at 13:30 +0000, Marcin Wyrwalski wrote:
> Hello.
>
>  I would like to ask, if there is a possibility to set up constraint to not allow user to write more than three decimall places in cell? I preparing and sending file to user, and user is supposed to return file with some data.

The closest thing I found look like that:
https://exceljet.net/formula/data-validation-number-multiple-100
(Validity defined by a formula.)

You will likely compare ROUND(x, 2) vs ROUND(x/100) and test for Zero
or something like that.
Cheers
Andreas