IFS CALCULATION NOT TO EXCEED SPECIFIC NUMBER

[Roger_Dalomba]

I am trying to do an IFS statement but don't want the return value to return higher than $10,000.

is this possible.....I've search and searched and can find a solution.

current expression

IFS(
      AND(

                [AGING]>=75,[AGING]<90),([COST]*0.10),
                 ([Aging]>=90),([COST]*0.20))

[MultiTech]

You could do all this in one column, but generally you'll actually be served better to add more columns into the mix.

What if you had another column, that was looking at the field with your calculations, and if that value was over $10k it stopped it there.

Then instead of using the original column (for the value you'll use/display), show this new column - as it's the one that contains the final product.

[Roger_Dalomba]

i'll have to do that if no other solution

[Steve]

MIN(
  LIST(
    IFS(
      ([Aging] >= 90),
        ([cost] * 0.20),
      ([Aging] >= 75),
        ([Cost] * 0.10)
    ),
    10000.00
  )
)

[Roger_Dalomba]

LIST has elements of mismatched types

tried number and price and same result

[Steve · Accepted solution]

Try this instead:

MIN(
  LIST(
    0.00 + IFS(
      ([Aging] >= 90),
        ([cost] * 0.20),
      ([Aging] >= 75),
        ([Cost] * 0.10)
    ),
    10000.00
  )
)

[Roger_Dalomba]

thank you......that worked

[MultiTech]

Clever use of a list there @Steve 

I like to have the actual value stored somewhere in the backend - this is how you can get good KPIs, because you'll have both (what we're using) and (what it really is).  With both you have the ability to see the difference, and if you capture that over time you can really inform your business processes. 

👍