IF formula

[Jamal]

I am trying to write an IF formula to say “If the value in A1 is divisible by
7 and the result is an integer (that is no decimal reminder like 14, 21, 28)
then divide A1 by 7 otherwise put Xâ€. How do I write this formula please?
Thanks.

 

[Mike H]

Hi,

=IF(AND(A1<>"",MOD(A1,7)=0),A1/7,"x")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Ms-Exl-Learner]

=IF(A1/7-INT(A1/7)=0,A1/7,"X")

Remember to Click Yes, if this post helps!

 

[Ms-Exl-Learner]

Revised Formula:-

=IF(A1="","",IF(A1/7-INT(A1/7)=0,A1/7,"X"))

 

[Joe User]

=IF(AND(A1<>"",MOD(A1,7)=0),A1/7,"x")

That does not work the way I presume you think it does. It works find if A1
is empty (no formula; no constant). But it returns a #VALUE error if A1
contains a null string, for example the result of a formula like
=IF(B1="","",B1).

It would be better to write:

=IF(A1="", "x", IF(MOD(A1,7)=0, A1/7, "x"))

although the following is more to my liking (and perhaps the OP's):

=IF(A1="", "", IF(MOD(A1,7)=0, A1/7, "x"))

PS: The MOD function has defects for some large numbers. A1-7*INT(A1/7) is
safer. But I must admit, I use MOD myself until it bites me.


----- original message -----

 

[Mike H]

Although I accept there are alternative solutions, the formula I posted works
precisely how I intended it to.

I agree it will return a value error in the way you describe but so will
both of your alternatives if A1 formula was for example:-

=IF(B1="","Cell is empty",B1)

So I see no advantage (or in fact disadvantage) in either of the
alternatives proposed.

I'm aware of the problem with MOD and large numbers


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Joe User]

I agree it will return a value error in the way you describe but so will
both of your alternatives

That is incorrect. But perhaps we need to be clear on the meaning -- my
meaning -- of "value error". I am talking about the Excel error #VALUE!.

if A1 formula was for example:-
=IF(B1="","Cell is empty",B1)

That does not result in a #VALUE! error when B1 contains the null string.



That does not result in a #VALUE! error when A1 contains the null string.

So I see no advantage (or in fact disadvantage) in either of the
alternatives proposed.

The advantage is: the latter formula works for the OP even when A1 contains
the null string (i.e. only __appears__ to be empty), whereas your formula
will result in a #VALUE!.


----- original message -----

 

[Mike H]

That does not result in a #VALUE! error when B1 contains the null string.

You truncated my response, what I actually said was:-

I agree it will return a value error in the way you describe but so will
both of your alternatives if A1 formula was for example:-

=IF(B1="","Cell is empty",B1)

which is equally as plausable as the formula in a1 being

=IF(B1="","",B1)


I think we'll call it a draw, the formula I proposed isn't bullet proof and
neither are either of the alternatives you proposed in critique of my
original. All 3 will return a #VALUE! error in different circumstances.

I'll let you have the last word by proposing a bullet proof solution!!


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.