## Forum Discussion

# My formula is broken, not sure house to fix

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)

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?

- PeterBartholomew1Mar 21, 2024Silver Contributor
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 )`

- Sammy700Mar 21, 2024Copper ContributorI 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?- PeterBartholomew1Mar 21, 2024Silver Contributor
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*]