Forum Discussion
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? 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.
7 Replies
- the correct formula may be =IFS (AND(D22-D18>3000000, D22-D18>5000000)
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?
- 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)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?
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 )
