If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Hi Bob and Roger (in alphabetical order),
I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did. Column A = age Column B = eye colour I have this formula: =IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE) But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT. So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following: =IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE) So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge." Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right? Please feel free to comment and I don't need any guarantee on your answers. G Epinn |
#2
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Hi Epinn
Well done or slightly corrupting the words of Pygmalion "I think he's got it" bg Yes, "*" is the equivalent of AND and "+" is the equivalent of OR. As far as I am aware, there is no difference between using either solution. Personally, I prefer the latter as there is just a single IF statement. -- Regards Roger Govier "Epinn" wrote in message ... Hi Bob and Roger (in alphabetical order), I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did. Column A = age Column B = eye colour I have this formula: =IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE) But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT. So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following: =IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE) So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge." Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right? Please feel free to comment and I don't need any guarantee on your answers. G Epinn |
#3
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Well even though I wasn't asked, here's my two cents...
I like the first IF statement, not the second because the + and * operators have very defined mathematical meanings. Better to eliminate ambiguity than to create more. My experience is that relatively few users of XL have training in logic, especially symbolic logic, and, therefore, using logic symbols which can be misconstrued as mathematical operators is best avoided. I prefer the IF THEN ELSE IF THEN ELSE construction to syntactical ambiguity, as it were... Dave -- Brevity is the soul of wit. "Roger Govier" wrote: Hi Epinn Well done or slightly corrupting the words of Pygmalion "I think he's got it" bg Yes, "*" is the equivalent of AND and "+" is the equivalent of OR. As far as I am aware, there is no difference between using either solution. Personally, I prefer the latter as there is just a single IF statement. -- Regards Roger Govier "Epinn" wrote in message ... Hi Bob and Roger (in alphabetical order), I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did. Column A = age Column B = eye colour I have this formula: =IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE) But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT. So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following: =IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE) So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge." Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right? Please feel free to comment and I don't need any guarantee on your answers. G Epinn |
#4
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
=AND(A230,OR(B2={"blue","brown"}))
don't forget to put a check mark against it BG -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Hi Bob and Roger (in alphabetical order), I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did. Column A = age Column B = eye colour I have this formula: =IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE) But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT. So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following: =IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE) So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge." Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right? Please feel free to comment and I don't need any guarantee on your answers. G Epinn |
#5
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
nice acrobat Epinn, I just wonder guys if you made test on a formula built
with a standard IF (1st) structuring with a sumproduct criterias inside the true or false results. Maybe u can do such test....for us. "Epinn" wrote: Hi Bob and Roger (in alphabetical order), I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did. Column A = age Column B = eye colour I have this formula: =IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE) But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT. So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following: =IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE) So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge." Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right? Please feel free to comment and I don't need any guarantee on your answers. G Epinn |
#6
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
try 7 ifs with multi sumproduct with multi and + or ....
"driller" wrote: nice acrobat Epinn, I just wonder guys if you made test on a formula built with a standard IF (1st) structuring with a sumproduct criterias inside the true or false results. Maybe u can do such test....for us. "Epinn" wrote: Hi Bob and Roger (in alphabetical order), I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did. Column A = age Column B = eye colour I have this formula: =IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE) But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT. So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following: =IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE) So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge." Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right? Please feel free to comment and I don't need any guarantee on your answers. G Epinn |
#7
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
It is a long story ...... A lot happened before this post and only Bob and Roger understand. Hence, their names are part of the subject line. Comments are welcome from all.
Dave, your points are very well taken. Before I understood Boolean, I wasn't comfortable with the "implicity" as described by you. Yes, I agree that it may be more logical to use the IF/AND/OR syntax. Thank you for sharing. I really like Bob's formula which should satisfy "simplicity" and "explicity." Roger, thank you for your kind words. Epinn "Epinn" wrote in message ... Hi Bob and Roger (in alphabetical order), I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did. Column A = age Column B = eye colour I have this formula: =IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE) But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT. So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following: =IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE) So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge." Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right? Please feel free to comment and I don't need any guarantee on your answers. G Epinn |
#8
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Of course, if you're the only one using the spreadsheet you should use
whatever formula you want as long as it returns the correct result! My experience has been, however, that many people are either unable or unwilling to take the time to figure out what an unfamiliar formula does. -- Brevity is the soul of wit. "Epinn" wrote: It is a long story ...... A lot happened before this post and only Bob and Roger understand. Hence, their names are part of the subject line. Comments are welcome from all. Dave, your points are very well taken. Before I understood Boolean, I wasn't comfortable with the "implicity" as described by you. Yes, I agree that it may be more logical to use the IF/AND/OR syntax. Thank you for sharing. I really like Bob's formula which should satisfy "simplicity" and "explicity." Roger, thank you for your kind words. Epinn "Epinn" wrote in message ... Hi Bob and Roger (in alphabetical order), I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did. Column A = age Column B = eye colour I have this formula: =IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE) But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT. So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following: =IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE) So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge." Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right? Please feel free to comment and I don't need any guarantee on your answers. G Epinn |
#9
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
So we should build spreadsheets for the lowest common denominator?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... Of course, if you're the only one using the spreadsheet you should use whatever formula you want as long as it returns the correct result! My experience has been, however, that many people are either unable or unwilling to take the time to figure out what an unfamiliar formula does. -- Brevity is the soul of wit. |
#10
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Hi Dave
Most of the time I am building applications for clients who care not one jot what the formulae are (they don't even see them), and would certainly not be interested in understanding them - that's why they ask me to build the application as opposed to doing it for themselves anyway. I do agree, that the IF THEN ELSE construct may be clearer for most people to follow the logic, and when correcting people's postings, or offering suggestions, most of the time I do use IF THEN construct. Sometimes I do offer that there is an alternative which requires fewer IF's. My response to Epinn was merely an expression of my personal favourite construct of the two he proposed. But variety is the spice of lifebg -- Regards Roger Govier "Dave F" wrote in message ... Of course, if you're the only one using the spreadsheet you should use whatever formula you want as long as it returns the correct result! My experience has been, however, that many people are either unable or unwilling to take the time to figure out what an unfamiliar formula does. -- Brevity is the soul of wit. "Epinn" wrote: It is a long story ...... A lot happened before this post and only Bob and Roger understand. Hence, their names are part of the subject line. Comments are welcome from all. Dave, your points are very well taken. Before I understood Boolean, I wasn't comfortable with the "implicity" as described by you. Yes, I agree that it may be more logical to use the IF/AND/OR syntax. Thank you for sharing. I really like Bob's formula which should satisfy "simplicity" and "explicity." Roger, thank you for your kind words. Epinn "Epinn" wrote in message ... Hi Bob and Roger (in alphabetical order), I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did. Column A = age Column B = eye colour I have this formula: =IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE) But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT. So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following: =IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE) So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge." Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right? Please feel free to comment and I don't need any guarantee on your answers. G Epinn |
Thread Tools | |
Display Modes | |
|
|