So, heres my problem...

I have a set of incremental values which act as thresholds, 100, 200, 300, so on. Another number is independent and correlates to that set of values, say 148.

I want another cell which calculates the difference between 148 and 200, but then recognises that when 148 goes over 200 that the next threshold has been met, so then resets and starts calculating the difference between that new number and the next threshold, 300.

Is it possible to do this all in the same cell?

Thanks in advance.

Re: variable number difference

With the independent number in cell F8, maybe...
  =IF(MOD(F8,100)=0,0,100-MOD(F8,100))

'---
Jim Cone
free & commercial excel programs (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

If your test value is in cell A1, you can use the CEILING.MATH function as follows:

=CEILING.MATH(A1, 100) - A1

which rounds up the value in A1 to the nearest multiple of its second argument (100), and then subtracts A1.

• Edited by Mark18991230 15 hours 28 minutes ago