little change to MOD function

[Ady4um]

Hello.

Straight to my situation.

A1: positive integer
A2: positive integer
A3: MOD(A1/A2,1)
A4: 1*(A3=0)
A5: A3+A4

The above MOD function in A3 returns values between:

0 < = (A1/A2) < 1

In A5 I manage to have:

0 < (A1/A2) < = 1

Which is what I want.

That result in A5 is almost the same as the MOD function in A3, except that when A3 results in 0 (zero), A5 results in 1 (one).

I don't really need A4 nor A3, so I could combine them as:

A6: MOD(A1/A2,1)+(MOD(A1/A2,1)=0)

There should be a simpler (or alternative) way to get the same result, but I couldn't find it. Ideas?

Thank you in advance.

 

[Ady4um]

@VoG,

My apologies for the other (now closed) topic. I couldn't find my own topic (this one I'm writing to), even using the forum's search function. In addition, it seems I am somehow "logging off", but I am NOT clicking on anything.

Anyway, my apologies. Now to the issue. Any suggestion for an alternative function (or combining with ceiling, floor, int...) so to achieve what I want?

Thank you in advance.

 

[Ady4um]

So, no suggestion at all? Isn't there ANY other way to obtain this result?

 

[shg]

One way:

=IFERROR(1/(1/MOD(A1/A2,1)), 1)

 

[Ady4um]

That is a valid alternative, and I thank you for posting.

I don't think it is a "simpler" one though .

The formula I presented in the OP is also an "IF", used with boolean, so replacing that with an "IFERROR"...

I really can't believe that there is no simple alternative. I don't expect it to be a one-function method, but all the alternatives I am thinking about are failing me in some point of the results.

All the functions I know of, that are somehow related to MOD (INT, QUOTIENT, CEILING, FLOOR, ROUND*) are "defaulting" to the "zero" side of the formula, and I find hard to believe that there is no potential application for this type of results that would have triggered some simple alternative already.

I'll keep looking into it. If anyone have any idea (which I guess is more about math than about spreadsheets ), I'll be happy to hear about it.

 

[shg]

I'm not sure what your criteria is. It's faster.

 

[Ady4um]

A_ I wouldn't know how to calculate which method / formula is faster of those 2.

B_ The criteria is that I should use the more basic function I can.

(Side note: The formula I presented is only part of a more complex one, but the other parts are not relevant to the goal of simplifying this part of the formula. If this was the only part, I would be somehow OK with it.)

The formula needs to be as much compatible with older versions as it can (I don't have any specific version to aim to; just to make it as much compatible as I can), without special additions. I though about many other functions (like the ones I already mentioned in previous posts), but all of them fall to the "zero side".

Using the obvious "IF" alternative:

IF(MOD(A1/A2,1)=0,1,MOD(A1/A2,1))

is "just the same" (or maybe it is "faster" ? ).

Using:

1-MOD(A1/A2,1)

would give me the correct result when the MOD result is zero, but will "fail me" on any other result.

This is to show you just 2 of many combinations I am thinking about, but with no final success of simplifying the formula.

Any tips, clues, or recommendations (about the fastest and simplest alternative within the criteria mentioned), or any other suggestion, are very welcome and appreciated.

Thank you in advance.