SOLVED

Formula that will have a result of 5000 or less OR a result of 0

Occasional Contributor

Hello,

I know how to get the first result of 5000.  What I'm trying to figure out is if the two numbers I am subtracting goes below a certain threshold to make the result 0.

Let's say the top number is 18000 and the variable number is <14500 then I want the solution to be 0.  However, if the answer is between 13000 and 14499 then I want the actual number to be the solution.

Is this possible to formulate?

Thank you in advance for your time.

Chad

 

6 Replies
Sorry I meant to say if the variable number is between 13000 and 14499 then I want the answer to be exactly what it is. ex. 18000-14500=0, ex. 18000-12999=5000, ex. 18000-14000=4000. Is this possible to formulate? Thank you
best response confirmed by Sergei Baklan (MVP)

@keao46 

Have you changed the problem?  Your example seem to conform more to

image.png

In that case, a simpler formula would do

= IF(variable#<bound, top - variable#, 0)
Hi,
I used the LET fx that you posted and I had to tweak it a bit but it looks like it is returning what I want it to do.
=LET(diff,L3-H22, IF(diff<(L3-O3+0.01), 0, diff))
L3= 18379
O3= 14703.20
It is doing exactly what I wanted it to do. I even modified it so that if O3 or L3 changes over time then the formula will still work.

Thanks again sir
So sorry. I would like the maximum return to be 5000 as well
Is that possible?
18000-14999=5000 max