I have a column named “Weights” with values like “1.0” or “2.2” that I use for basic item ordering.
x.0 = default item
x.1 = first optional item
x.2 = second optional item
1.0 - Include
2.0 - Include
3.0 - Include
3.1 - Exclude
3.2 - Exclude
4.0 - Include
5.0 - Include
I’m using a Rollup field to add up prices, but it’s including all the optional items in the sum, where I only want the default items included.
My thought was "Create a formula field that outputs the Total Price field, but only if the far-right character of the value of the Weights column was a zero, if it’s anything other than zero, then set the price to “0” instead… Then create a rollup field on that “Total Prices Without Optional Values” column…
Can’t figure out how to get RIGHT() to show me the right-most character of the Weights field so far…
The first thing in the RIGHT function is the field you’re wanting to look at - Weights. The second thing is how many characters from the right you want it to look at - one from the right. So we’re retrieving the rightmost character of the Weights field with (RIGHT(Weights,1)
This is all wrapped in an IF statement: If the rightmost character in weights is = 0, print the Total Price. If the rightmost character is anything but 0, then print nothing.
Is this just about what you wanted?
Hmmm, maybe I’m missing something, but that small difference actually makes it a much more complicated formula. The question becomes how to make a formula that only accepts whole numbers. Looking through the functions, I can’t see anything that would make that very easy.
If I were you, I would convert your Weights field to a text field and just use the text formula I gave you. If you still need the Weights formatted as a number for something else, you can convert it using VALUE(Weights) and print that to a new field for anything you need to do with it.
You can force any number to be a string by adding it to another string, even an empty one. So while using RIGHT(Weights, 1) won’t work on a numerical value, RIGHT("" & Weights, 1) will work. That will let you keep your Weights field numeric rather than changing it to a text field.