Quote:
Originally Posted by john_charves
I have data for water indicator, what is the formula for make 001-999 counted as 2 not -998 (assume max digit is three)?
|

Do you have in some cell a formula like:
, where A2 has value 1 and B2 has value 999 (and cell A2 is simply a number formatted with leading zeroes, with real value being 1 - because in case it were text "001", you'd get -999 instead of -998)? Or do you have something entirely different?
When it is a formula described above, then:
Question1: Why B2=999 as border value? Why not 1000? With this rule, there will never be result <-997 when A2=1 - is this OK?
Question2: When B2<999, must be result always be the difference calculated, i.e. 1-998 =-997, or 2-998=-996 (i.e. with A=2 the result will never be <-996), or 2-995=-993? Will 3-998=-995 be right answer? And does this mean, with A2=3, the result can never be less than -995?
Question3: What must be result, when A2=3, and B2=999? The result can't be 4!