Forum Discussion

Sammy700's avatar
Sammy700
Copper Contributor
Mar 21, 2024

My formula is broken, not sure house to fix

Here is my formula =IFS(D22-D18>D18,{" "},D22-D18<D18,{"0"},D22-D18=D17,{"5000000"}) It is broken at the first part, I need it to come to a number that is between $3M and $5M, how do I get a number...
Formulas and Functions
Sammy700's avatar
Sammy700
Copper Contributor
Mar 21, 2024

m_tarler 

 

 

 

this is what I am attempting to do is take the d22 less d18 if larger then the difference between d22, d18 and d17 if smaller then 0, or if it higher the d17 make it the amount in d17.

 

using=IFS(D22-D18>D18,"",D22-D18<D18,0,D22-D18=D17,500000)

I get the 500,000 but I can't seem to find the formula for the difference between d22-d18>d17,'"

can you help?

 

 

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 21, 2024

Sammy700 

The now information is useful, but I still do not really understand exactly what it is you hope to see.  My formula returned 500,000 but I wouldn't know whether that is good or bad.

= IFS(
      paidLoss-retention > limit, limit,
      paidLoss-retention < retention, 0,
      TRUE, 500000
  )
  • Sammy700's avatar
    Sammy700
    Copper Contributor
    Mar 21, 2024
    I am attempting to make a figure out how to write a formal to determine if the paid loss is below retention, for a zero to enter, if not what is the difference between paid loss, retention and limit, and if the loss is higher then the limit to have the limit entered.

    Does that makes sense?
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Mar 21, 2024

      Sammy700 

      This tries to follow your written criteria as I followed them

      = IFS(
    paidLoss-retention < 0, 0,
    paidLoss-retention > limit, limit,
    TRUE, paidLoss-retention
  )

      If that is correct, an alternative formula might be

      = MEDIAN(0, limit, paidLoss- retention)

      [p.s.  I hope that the use of names makes the formula more meaningful written here, evenif you doen't use them in your workbooks]

       

Resources