My formula is broken, not sure house to fix

[Sammy700]

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?   The following section is the one that works  D22-D18<d18,{"0"}.    Can anyone help with this formula, not sure what I am doing wrong.

Thank you for your assistance, much appreciated.

[#2]

I don't know what you are trying to do / why it is 'broken'. The formula itself 'works' but not sure why you did a few things:
a) why is each answer formatted as a set of 1 (i.e. {" "} ) you don't need the set around those.
b) why is the 3rd condition =D17 instead of D18? you realize that if D22-D18 = D18 and D18 <> D17 then there is no correct response excel will return #N/A
c) are you sure you want those numbers 0 and 5000000 formatted as TEXT?
so maybe you want:
=IFS(D22-D18>D18,"", D22-D18<D18,0, D22-D18=D18, 5000000)

[#3]

@Sammy700 

The only way to get "5000000" is if D18=D17 and D22=2*D17.

I doubt this is what you intend.

Other observations might be that treating scalar values as arrays {"5000000"}. Also, does the result need to be text?

[#4]

@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?

[#5]

the correct formula may be =IFS (AND(D22-D18>3000000, D22-D18>5000000)

[#6]

@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
  )

[#7]

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?

[#8]

@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]