Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 life<bg -- 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
As with a speech, you should know your audience.
If your audience is yourself or another technically/mathematically/logic -literate user, then by all means use whatever works. If that means addressing the lowest common denominator, then so be it. Dave -- Brevity is the soul of wit. "Bob Phillips" wrote: 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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Fair point, Roger.
My experience, so far, has been building applications for accountants/auditors who want to know the underlying logic, but blanche at seeing unfamiliar formula constructs. Perhaps it has something to do with them being CPAs as opposed to CFAs/traders/analysts, etc. Dave -- Brevity is the soul of wit. "Roger Govier" wrote: 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 life<bg -- 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Thank you all for an interesting discussion. I understand all the comments prior to this.
As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ...... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Well back to the LCD concept--my boss right now hates SUMPRODUCT--even though
it is, in many cases, the only solution to what he requires. Go figure. Dave -- Brevity is the soul of wit. "Epinn" wrote: Thank you all for an interesting discussion. I understand all the comments prior to this. As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ...... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
no problem Dave,
if ms introduce sumproductif <maybe someone can do a formula conversion technique you know, excel is not an empty facility - it is well equipped. Dont you think? "Dave F" wrote: Well back to the LCD concept--my boss right now hates SUMPRODUCT--even though it is, in many cases, the only solution to what he requires. Go figure. Dave -- Brevity is the soul of wit. "Epinn" wrote: Thank you all for an interesting discussion. I understand all the comments prior to this. As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ...... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Boolean is well know to programmers.
http://www.sjsu.edu/depts/Museum/boole.html Biff "Epinn" wrote in message ... Thank you all for an interesting discussion. I understand all the comments prior to this. As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ....... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Hmmm...... I say only the *well-trained* programmers. I have known people having a job title of "programmer" but have no idea of Boolean.
Unfortunately, not everyone uses Excel is a programmer. Administrative assistants for one use MS Office heavily but they may not be familiar with Boolean. Epinn "Biff" wrote in message ... Boolean is well know to programmers. http://www.sjsu.edu/depts/Museum/boole.html Biff "Epinn" wrote in message ... Thank you all for an interesting discussion. I understand all the comments prior to this. As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ....... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Well, I guess one more question to ask a prospective employer:
"Do you hate SUMPRODUCT as I am a fan of it?" <BG Epinn "Dave F" wrote in message ... Well back to the LCD concept--my boss right now hates SUMPRODUCT--even though it is, in many cases, the only solution to what he requires. Go figure. Dave -- Brevity is the soul of wit. "Epinn" wrote: Thank you all for an interesting discussion. I understand all the comments prior to this. As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ...... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
not everyone uses Excel is a programmer.
That's certainly true. Me, for example! Well actually, I used to program back in the early to mid 80's but I've forgotten almost everything about it. I used to write utilities in C for the lab I worked in. We did xray fluorescence analysis on minerals. We wrote our own spreadsheet and dumped the analysis data into that. I moved on to bigger and better things within the company and by that time (late 80's) started using Lotus for other things. We didn't start using Excel 'til about 92? At that time I was the "office Excel Guru" but believe me when I tell you, back then I didn't know squat compared to what I know now. So, that's me! Biff "Epinn" wrote in message ... Hmmm...... I say only the *well-trained* programmers. I have known people having a job title of "programmer" but have no idea of Boolean. Unfortunately, not everyone uses Excel is a programmer. Administrative assistants for one use MS Office heavily but they may not be familiar with Boolean. Epinn "Biff" wrote in message ... Boolean is well know to programmers. http://www.sjsu.edu/depts/Museum/boole.html Biff "Epinn" wrote in message ... Thank you all for an interesting discussion. I understand all the comments prior to this. As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ....... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
*OFF TOPIC*
Biff, thank you for sharing. That's interesting. I know what programming in the 80's was like. I think you were ahead of the game then - using C already, when most people still programmed in Fortran, Cobol, RPG III etc. They even carried stack of cards around. Although "Internet" was around, it was little known. We have certainly come a looooong way. Talking about Lotus Notes, a friend of mine works in the Govt. and she's still using Lotus Notes. They are about to switch to Excel finally. When I said, "I think at the college, Boolean is not taught," I was referring to the Excel courses offered in the colleges and programming courses may be different. Depending on the qualification of the Excel instructor, he/she may not know Boolean which is not part of the curriculum. When I talked about using Boolean and taking risk, I was joking. I want to make sure everyone understands that. Yes, you are definitely an Excel guru and I have a lot of respect for you even though MS has not labeled you "MVP." I strongly believe that anyone who has a logical/analytical mind or anyone with programming skills/background will do well in Excel. Afterall, writing a formula is like writing a line of code in a program regardless of the computer language. As a matter of fact, I think writing a formula can be more difficult than coding. Why? Technically speaking, one has to incorporate all the conditions, calculations etc. in *one* line of code. One other point, programmers are trained to do detailed testing and they will probably test their Excel formulae with all sorts of data sets. I feel you do that with the complicated formulae. By the way, I have never found even a typo in any of your formulae posted here. I am high on quality and I congratulate you. Thank you for your exemplary contribution and also for being helpful and patient. Epinn "Biff" wrote in message ... not everyone uses Excel is a programmer. That's certainly true. Me, for example! Well actually, I used to program back in the early to mid 80's but I've forgotten almost everything about it. I used to write utilities in C for the lab I worked in. We did xray fluorescence analysis on minerals. We wrote our own spreadsheet and dumped the analysis data into that. I moved on to bigger and better things within the company and by that time (late 80's) started using Lotus for other things. We didn't start using Excel 'til about 92? At that time I was the "office Excel Guru" but believe me when I tell you, back then I didn't know squat compared to what I know now. So, that's me! Biff "Epinn" wrote in message ... Hmmm...... I say only the *well-trained* programmers. I have known people having a job title of "programmer" but have no idea of Boolean. Unfortunately, not everyone uses Excel is a programmer. Administrative assistants for one use MS Office heavily but they may not be familiar with Boolean. Epinn "Biff" wrote in message ... Boolean is well know to programmers. http://www.sjsu.edu/depts/Museum/boole.html Biff "Epinn" wrote in message ... Thank you all for an interesting discussion. I understand all the comments prior to this. As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ....... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Thanks for the kind words!
Biff "Epinn" wrote in message ... *OFF TOPIC* Biff, thank you for sharing. That's interesting. I know what programming in the 80's was like. I think you were ahead of the game then - using C already, when most people still programmed in Fortran, Cobol, RPG III etc. They even carried stack of cards around. Although "Internet" was around, it was little known. We have certainly come a looooong way. Talking about Lotus Notes, a friend of mine works in the Govt. and she's still using Lotus Notes. They are about to switch to Excel finally. When I said, "I think at the college, Boolean is not taught," I was referring to the Excel courses offered in the colleges and programming courses may be different. Depending on the qualification of the Excel instructor, he/she may not know Boolean which is not part of the curriculum. When I talked about using Boolean and taking risk, I was joking. I want to make sure everyone understands that. Yes, you are definitely an Excel guru and I have a lot of respect for you even though MS has not labeled you "MVP." I strongly believe that anyone who has a logical/analytical mind or anyone with programming skills/background will do well in Excel. Afterall, writing a formula is like writing a line of code in a program regardless of the computer language. As a matter of fact, I think writing a formula can be more difficult than coding. Why? Technically speaking, one has to incorporate all the conditions, calculations etc. in *one* line of code. One other point, programmers are trained to do detailed testing and they will probably test their Excel formulae with all sorts of data sets. I feel you do that with the complicated formulae. By the way, I have never found even a typo in any of your formulae posted here. I am high on quality and I congratulate you. Thank you for your exemplary contribution and also for being helpful and patient. Epinn "Biff" wrote in message ... not everyone uses Excel is a programmer. That's certainly true. Me, for example! Well actually, I used to program back in the early to mid 80's but I've forgotten almost everything about it. I used to write utilities in C for the lab I worked in. We did xray fluorescence analysis on minerals. We wrote our own spreadsheet and dumped the analysis data into that. I moved on to bigger and better things within the company and by that time (late 80's) started using Lotus for other things. We didn't start using Excel 'til about 92? At that time I was the "office Excel Guru" but believe me when I tell you, back then I didn't know squat compared to what I know now. So, that's me! Biff "Epinn" wrote in message ... Hmmm...... I say only the *well-trained* programmers. I have known people having a job title of "programmer" but have no idea of Boolean. Unfortunately, not everyone uses Excel is a programmer. Administrative assistants for one use MS Office heavily but they may not be familiar with Boolean. Epinn "Biff" wrote in message ... Boolean is well know to programmers. http://www.sjsu.edu/depts/Museum/boole.html Biff "Epinn" wrote in message ... Thank you all for an interesting discussion. I understand all the comments prior to this. As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ....... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bob P. and Roger G. -- IF(and(...IF(OR( vs. IF(Boolean
Dave,
...... my boss right now hates SUMPRODUCT ...... << Wonder why. Purely something to do with LCD concept? I have always been saying that I am a fan of SUMPRODUCT. I am not so sure any more after some experiments. I have learned that I must be very careful with data type when I deal with SUMPRODUCT as it is very disciplined and less forgiving as other functions like COUNTIF or SUMIF. In case anyone cares about my experiment, check out my posts under this thread. http://groups.google.ca/group/micros...4ad71c9e0ba655 or http://tinyurl.com/yf6a9v Epinn "Dave F" wrote in message ... Well back to the LCD concept--my boss right now hates SUMPRODUCT--even though it is, in many cases, the only solution to what he requires. Go figure. Dave -- Brevity is the soul of wit. "Epinn" wrote: Thank you all for an interesting discussion. I understand all the comments prior to this. As a matter of fact, Dave has spelt out my "all-time" concern. As much as I like SUMPRODUCT (just using it as an example), I have a feeling that some "bosses" may not like it because they are not familiar with it. I am not surprised that I'll be asked to use the lowest common denominator so that everyone who needs to maintain the spreadsheet(s) can understand the formulae and do his/her job. Back to Boolean and IF(AND......(OR, I think at the college, Boolean is not taught. If that is the case, probably Boolean may not be widely accepted/recognized by entry level personnel in the workplace?? Boolean is new to me (well, I am new to Excel anyway) and presents a "challenge" at this point. So, I'll give it some practice and I don't mind taking the "risk." By the way, Boolean is not covered in the Excel Help files if I am not mistaken. Bob, am I considered a risk taker then? <G Another story ...... At this general learning stage, I always try to come up with more than one solution to a specific issue. This is how I learn. Boolean is not as "easy" to decipher but the formula is usually shorter and cleaner. Wonder what others think. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating textbox on a chart | Charts and Charting in Excel | |||
12 Month Average | Excel Worksheet Functions | |||
Help with lookup | Excel Discussion (Misc queries) | |||
Getpivotdata - able to reference to another cell all parameters but not the datafield | Excel Discussion (Misc queries) | |||
Formulas, question for Roger Govier | Excel Worksheet Functions |