|
IF formula
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. |
IF formula
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. "Jamal" wrote: 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. |
IF formula
=IF(A1/7-INT(A1/7)=0,A1/7,"X")
Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Jamal" wrote: 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. |
IF formula
Revised Formula:-
=IF(A1="","",IF(A1/7-INT(A1/7)=0,A1/7,"X")) -------------------- (Ms-Exl-Learner) -------------------- "Ms-Exl-Learner" wrote: =IF(A1/7-INT(A1/7)=0,A1/7,"X") Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Jamal" wrote: 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. |
IF formula
"Mike H" wrote:
=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" wrote in message ... 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. "Jamal" wrote: 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. |
IF formula
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" wrote: "Mike H" wrote: =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" wrote in message ... 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. "Jamal" wrote: 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. . |
IF formula
"Mike H" wrote:
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. I also wrote: =IF(A1="", "x", IF(MOD(A1,7)=0, A1/7, "x")) 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" wrote in message ... 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" wrote: "Mike H" wrote: =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" wrote in message ... 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. "Jamal" wrote: 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. . |
IF formula
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. "Joe User" wrote: "Mike H" wrote: 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. I also wrote: =IF(A1="", "x", IF(MOD(A1,7)=0, A1/7, "x")) 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" wrote in message ... 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" wrote: "Mike H" wrote: =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" wrote in message ... 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. "Jamal" wrote: 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. . . |
| All times are GMT +1. The time now is 12:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com