Never ending If Function

[BPharm]

Hi,

I want to make the following formula to never end

=IF(B6*100<B17;B6;IF(((B6-1)*100)<B17;(B6-1);IF(((B6-2)*100)<B17;(B6-2);IF(((B6-3)*100)<B17;(B6-3)... etc

So it is continually will minus 1 from B6 until it finds a true result

Thank you in advance for your help!

Patrick

[FJCC]

You don't want to do this with an IF(). It is too late here for me to think about this all the way through but I would start with this function

Code: Select all

=CEILING(B6-B17/100;1)

I came to that starting with my understanding that you want to find the smallest integer x such that
(B6 - x) * 100 < B17
which leads to looking for the first integer larger than
B6 - B17/100
I am not sure that my formula will give you the answer you want when (B6 - x) * 100 = B17 for some x. You have to decide what you want in that case. The formula will also throw an error when B6 * 100 is already less than B17, say B6 = 6 and B17 = 625. That would have to be handled.