*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 said:
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
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
Boolean is well know to programmers.
http://www.sjsu.edu/depts/Museum/boole.html
Biff
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
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(A2>30,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((A2>30)*((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
