Basic Formula Help using RIGHT()

[Brad_Mills]

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

eg:

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…

[Mitchell_Wells1]

IF(RIGHT(Weights,1)=0,{Total Price},BLANK())

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?

[Brad_Mills]

Thanks!

I tried briefly just using RIGHT(Weights, 1) just to see if that works, and I get an error… So I think maybe I’m trying to do a text operation on a numeric field… Could be I need to use a FLOOR or something

[Mitchell_Wells1]

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.

[Justin_Barrett]

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.

[Enrico_Noriega]

Genius! This worked for me! Thanks!