Less than, More than

[Willyan_Hendra]

Hi, could you give me information, for the formula of

If weight Less than 10, use A Price
If weight more than 10, but less than 45, use B Price
If weight more than 45, use then C Price

I have used this formula

ifS(([Weight (kg)] <= 10), A Price, ([Weight (kg)] > 10), B Price, ([Weight (kg)] > 45), C Price.

But it doesnt work on C price.

Please kindly help

[Suvrutt_Gurjar · Accepted solution]

Does following syntax for the expression help?

IFS( [Weight (kg)] > 45, C Price, [Weight (kg)] > 10, B Price, [Weight (kg)] <= 10 , A Price)

[Willyan_Hendra]

I will try it now, hopefully it works

[Willyan_Hendra]

it works, thank you so very much

[JustinGough]

Suvrutt’s reply looks right to me. That said, would you mind a couple of programming style tips?

First, make sure that you phrase the problem correctly. It is difficult to get your code right if you don’t have the problem phrased correctly.

If weight Less than 10, use A Price
If weight more than 10, but less than 45, use B Price
If weight more than 45, use then C Price

These statements don’t say what to do if the weight is 10 or 45. It is easier to find the problem here than it is in a formula. A formula that implements these statements would have at least 2 bugs. I looks like you meant:

If weight Less than or equal to 10, use A Price
If weight more than 10 and less than or equal to 45, use B Price
If weight more than 45, use then C Price

Second, write the formula to closely follow the problem statement. There are many formulas that would work, but the formula used should be easy to understand. Also, indentation helps you find mistakes.

IFS(
[Weight (kg)] <= 10, A Price,
AND([Weight (kg)] > 10, [Weight (kg)] <= 45), B Price
[Weight (kg)] > 45, C Price)

Each line defines a sub range. The order does not matter either.

[Willyan_Hendra]

Thank you so much for your help, this very helpful

[Steve]

JustinGough:

IFS(
[Weight (kg)] <= 10, A Price,
AND([Weight (kg)] > 10, [Weight (kg)] <= 45), B Price
[Weight (kg)] > 45, C Price)

Better yet:

IFS(
[Weight (kg)] <= 10, A Price,
[Weight (kg)] <= 45, B Price,
true, C Price)

Avoids redundant tests.

[Suvrutt_Gurjar]

Nice insights on best practices @JustinGough.

If I may add a somewhat different but relevant point to the IFS() function used in this thread.

The article on IFS() mentions below on return value

Return Value

The result of evaluating the then-do-this expression immediately following the first is-true? expression that evaluates to TRUE.

I believe @Willyan_Hendra’ s original expression was failing because as per characteristics of IFS() argument 2 (([Weight (kg)] > 10) was always evaluating true for even values of argument 3 (([Weight (kg)] > 45), C Price), So argument 3 was never evaluated.

Just thought of adding to the post thread with this peculiarity of IFS() function, if it helps @Willyan_Hendra and any new user of IFS() function. I always refer @Steve’s help articles as these capture these nuances well.

[LeventK]

Suvrutt_Gurjar:

Return Value

The result of evaluating the then-do-this expression immediately following the first is-true? expression that evaluates to TRUE.

Just for that reason, SWITCH () expression is more appropriate and yet powerful to use whenever possible as it evaluates all the option parameters and return the last TRUE param’s eval result or the default param’s eval result if all params eval to FALSE.

[Suvrutt_Gurjar]

Yes, very correct @LeventK. Depending on best judgement and awareness of the requirement, the app creator needs to use the best possible working option that works under all possible conditions.

[JustinGough]

I see your point but the idea was to make the formula map directly to intent. Now the reader has to model what the computer is doing to understand what each line of the IFS ends up selecting for. Line 3 (with 45) only works because of line 2, and line 4 only works because of lines 2 and 3.

I wouldn’t reject any of the solutions in a peer review, but I would point out the differences to a Jr. programmer, who might come back with something better than all of these options.

PS
Thanks for your many AppSheet posts, Steve. You’ve already helped me out several times!