Hi,

I am looking for a good reference book for Excel formulas & functions. I
have a good general knowledge of Excel itself but want to know more on the
Formula & function side of things.

Thanks in advance

Paul

Paul, have a look here,

http://www.contextures.on.ca/xlbooks.html

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"phocused" wrote in message
...
Hi,

I am looking for a good reference book for Excel formulas & functions. I
have a good general knowledge of Excel itself but want to know more on the
Formula & function side of things.

Thanks in advance

Paul

There is really only one book on formulae worth its salt IMO, and that is
John Walkenbach's Excel 2003 Formulas (sic!).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phocused" wrote in message
...
Hi,

I am looking for a good reference book for Excel formulas & functions. I
have a good general knowledge of Excel itself but want to know more on the
Formula & function side of things.

Thanks in advance

Paul

Bob, Paul b,

Thanks for the help, reccomendations.

I have gone for Bob's suggestion.

Rgds Paul

"Bob Phillips" wrote:

There is really only one book on formulae worth its salt IMO, and that is
John Walkenbach's Excel 2003 Formulas (sic!).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phocused" wrote in message
...
Hi,

I am looking for a good reference book for Excel formulas & functions. I
have a good general knowledge of Excel itself but want to know more on the
Formula & function side of things.

Thanks in advance

Paul

In addition to a reference book, you can also download Peter Noneley's
Excel Function Dictionary:

http://www.xlfdic.com/

phocused wrote:
I am looking for a good reference book for Excel formulas & functions. I
have a good general knowledge of Excel itself but want to know more on the
Formula & function side of things.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra,

That is very helpful, thanks for the link.

Rgds Paul

"Debra Dalgleish" wrote:

In addition to a reference book, you can also download Peter Noneley's
Excel Function Dictionary:

http://www.xlfdic.com/

phocused wrote:
I am looking for a good reference book for Excel formulas & functions. I
have a good general knowledge of Excel itself but want to know more on the
Formula & function side of things.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I'll second the nomination of Walkenbach's books. For 2000/2003 he had two:
one targeted worksheet functions, the other VBA code, but both touch well
into the other's primary area. Well worth the money.

"phocused" wrote:

Bob, Paul b,

Thanks for the help, reccomendations.

I have gone for Bob's suggestion.

Rgds Paul

"Bob Phillips" wrote:

There is really only one book on formulae worth its salt IMO, and that is
John Walkenbach's Excel 2003 Formulas (sic!).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phocused" wrote in message
...
Hi,

I am looking for a good reference book for Excel formulas & functions. I
have a good general knowledge of Excel itself but want to know more on the
Formula & function side of things.

Thanks in advance

Paul

phocused wrote:
"Bob Phillips" wrote:
There is really only one book on formulae worth its salt IMO, and that is
John Walkenbach's Excel 2003 Formulas (sic!).

Thanks for the help, reccomendations.
I have gone for Bob's suggestion.

And please post back with your impression of the book after you have
read it and tried to use it for a while.

I have both of Walkenbach's Excel books. (Well, he might have more
than two. Unfortunately, they are both packed away during remodeling,
so I cannot check the titles.) They are certainly full of useful
information. But I am very disappointed in their organization,
presentation and completeness. After reading the VBA book nearly a
year ago, I never can find anything when I go to look up something that
I "know" is in the book. And as I recall, things were not presented in
a "logical" sequence in the first place.

I have used the Excel Formulas book only sparingly, having learned
Excel on my own very easily based on years of experience with
pioneering spreadsheet products. But it, too, suffers as a reference,
IMHO.

Having said all that, I should add that I am a professional computer
programmer (post grad degree) with many decades of experience in system
programming in another part of the industry. Arguably, perhaps my
expectations are too high. In fact, I am sure they are, considering
the seemingly overwhelming good reviews of Walkenbach's books.

PS: On the other hand, I have yet to find a book that presents Excel
and VBA programming any better. I think the MS industry is severely
lacking in quality texts. At least, I have yet to stumble upon
anything really good.

I don't know about the structure of the books (I have to admit to never
having bought or fully read any of them) so I cannot comment on that, but I
would point out that John's books are not meant for the likes of you and I,
they are aimed straight at the mass-market, looking to generate as many
sales as possible by teaching aspects of Excel to people that have a desire
to learn, but do not necessarily have the sort of background that they can
do it on their own.

I recommended John's book, because I have leafed through many in bookstores,
and that is the only one that I found presented anything other than really
basic information. I agree it falls well short of discussing advanced
techniques such as the one we discussed back on 25th June, and a book on
that topic would be great (I have thousands of pages of notes, but not the
discipline I feel to create such a tome), but unless you are John
Walkenbach, book writing is not a lucrative job, the rewards are nowhere
near commensurate to the effort involved.

BTW, John has written loads of books, far more than the 2 you have. And I am
sure we will be seeing 2007 versions very soon.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
phocused wrote:
"Bob Phillips" wrote:
There is really only one book on formulae worth its salt IMO, and that
is
John Walkenbach's Excel 2003 Formulas (sic!).

Thanks for the help, reccomendations.
I have gone for Bob's suggestion.

And please post back with your impression of the book after you have
read it and tried to use it for a while.

I have both of Walkenbach's Excel books. (Well, he might have more
than two. Unfortunately, they are both packed away during remodeling,
so I cannot check the titles.) They are certainly full of useful
information. But I am very disappointed in their organization,
presentation and completeness. After reading the VBA book nearly a
year ago, I never can find anything when I go to look up something that
I "know" is in the book. And as I recall, things were not presented in
a "logical" sequence in the first place.

I have used the Excel Formulas book only sparingly, having learned
Excel on my own very easily based on years of experience with
pioneering spreadsheet products. But it, too, suffers as a reference,
IMHO.

Having said all that, I should add that I am a professional computer
programmer (post grad degree) with many decades of experience in system
programming in another part of the industry. Arguably, perhaps my
expectations are too high. In fact, I am sure they are, considering
the seemingly overwhelming good reviews of Walkenbach's books.

PS: On the other hand, I have yet to find a book that presents Excel
and VBA programming any better. I think the MS industry is severely
lacking in quality texts. At least, I have yet to stumble upon
anything really good.

I agree it falls well short of discussing advanced techniques such as the
one we discussed back on 25th June

Can you provide a link to that discussion?

a book on that topic would be great (I have thousands
of pages of notes, but not the discipline I feel to create such a tome)

I don't know what "that topic" is but I've also "thought about writing a
book" on "advanced formulas". I wouldn't know where to start! I haven't a
clue about how to write a book! I don't have JW's book, but does it cover
the types of formulas and techniques we routinely provide in these groups? I
have a couple of old books but they're pretty much useless for anything
above the very basics:

"To sum a range of cells use this formula: =SUM(A1:10)"
"To find the maximum value in a range of cells use this formula:
=MAX(A10:A10)"

Really advanced stuff!

Biff

"Bob Phillips" wrote in message
...
I don't know about the structure of the books (I have to admit to never
having bought or fully read any of them) so I cannot comment on that, but I
would point out that John's books are not meant for the likes of you and I,
they are aimed straight at the mass-market, looking to generate as many
sales as possible by teaching aspects of Excel to people that have a desire
to learn, but do not necessarily have the sort of background that they can
do it on their own.

I recommended John's book, because I have leafed through many in
bookstores, and that is the only one that I found presented anything other
than really basic information. I agree it falls well short of discussing
advanced techniques such as the one we discussed back on 25th June, and a
book on that topic would be great (I have thousands of pages of notes, but
not the discipline I feel to create such a tome), but unless you are John
Walkenbach, book writing is not a lucrative job, the rewards are nowhere
near commensurate to the effort involved.

BTW, John has written loads of books, far more than the 2 you have. And I
am sure we will be seeing 2007 versions very soon.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
phocused wrote:
"Bob Phillips" wrote:
There is really only one book on formulae worth its salt IMO, and that
is
John Walkenbach's Excel 2003 Formulas (sic!).

Thanks for the help, reccomendations.
I have gone for Bob's suggestion.

And please post back with your impression of the book after you have
read it and tried to use it for a while.

I have both of Walkenbach's Excel books. (Well, he might have more
than two. Unfortunately, they are both packed away during remodeling,
so I cannot check the titles.) They are certainly full of useful
information. But I am very disappointed in their organization,
presentation and completeness. After reading the VBA book nearly a
year ago, I never can find anything when I go to look up something that
I "know" is in the book. And as I recall, things were not presented in
a "logical" sequence in the first place.

I have used the Excel Formulas book only sparingly, having learned
Excel on my own very easily based on years of experience with
pioneering spreadsheet products. But it, too, suffers as a reference,
IMHO.

Having said all that, I should add that I am a professional computer
programmer (post grad degree) with many decades of experience in system
programming in another part of the industry. Arguably, perhaps my
expectations are too high. In fact, I am sure they are, considering
the seemingly overwhelming good reviews of Walkenbach's books.

PS: On the other hand, I have yet to find a book that presents Excel
and VBA programming any better. I think the MS industry is severely
lacking in quality texts. At least, I have yet to stumble upon
anything really good.

"T. Valko" wrote in message
...
I agree it falls well short of discussing advanced techniques such as the
one we discussed back on 25th June

Can you provide a link to that discussion?


This is the one.

http://tinyurl.com/yn64g2

You won't find it anything new, but it was new to Joe (who by his own
admission is an advanced user) at that time, and the discussion was the
interesting part. At least I thought it was relevant to this thread.


I don't know what "that topic" is but I've also "thought about writing a
book" on "advanced formulas". I wouldn't know where to start! I haven't a
clue about how to write a book! I don't have JW's book, but does it cover
the types of formulas and techniques we routinely provide in these groups?
I have a couple of old books but they're pretty much useless for anything
above the very basics:


That topic was advanced Excel techniques, not just formulae, but mainly
formulae driven.

It's a while since I have looked at John's book, but as I recall, it wasn't
going into the depth of some of the solutions of the type that we offer
here. It was more along the lines of taking themes, and developing them,
showing the formulae available, how to use them etc. There is some extremely
basic stuff there, some more advanced stuff (Part IV is Array Formulae). The
best bit is part III for me, Financial Formulae, but probably because I know
naff all about them. Despite what Joe says, I think it is well formatted, I
think that is one of John's strengths.

I think the market needs a book that starts at the intermediate user, and
gives them tools and techniques to become an expert. No basic stuff, all
that is assumed in the reader. I really don't think that book exists at this
point. And of course, if anyone wants to write it, publishers now want it to
be focussed on Excel 2007.

Can you provide a link to that discussion?
This is the one.
http://tinyurl.com/yn64g2

Ok, I remember reading that thread.

That thread also fits the evolved topic of this thread. Toppers says:

"There is a market for somone who could distill all the knowledge into a
single reference"

I think we agree but it would be a lot of work!

As for the subject of that thread, here's another formula to "do stuff" on
non-contiguous ranges:

Array entered:

=AVERAGE(IF(CHOOSE({1,2,3},B6:B19,B27:B40,H6:H19)= "x",CHOOSE({1,2,3},C6:C19,C27:C40,I6:I19)))

Using this method the ranges must be the same size *and* shape.

Biff

"Bob Phillips" wrote in message
...

"T. Valko" wrote in message
...
I agree it falls well short of discussing advanced techniques such as
the one we discussed back on 25th June

Can you provide a link to that discussion?


This is the one.

http://tinyurl.com/yn64g2

You won't find it anything new, but it was new to Joe (who by his own
admission is an advanced user) at that time, and the discussion was the
interesting part. At least I thought it was relevant to this thread.


I don't know what "that topic" is but I've also "thought about writing a
book" on "advanced formulas". I wouldn't know where to start! I haven't a
clue about how to write a book! I don't have JW's book, but does it cover
the types of formulas and techniques we routinely provide in these
groups? I have a couple of old books but they're pretty much useless for
anything above the very basics:


That topic was advanced Excel techniques, not just formulae, but mainly
formulae driven.

It's a while since I have looked at John's book, but as I recall, it
wasn't going into the depth of some of the solutions of the type that we
offer here. It was more along the lines of taking themes, and developing
them, showing the formulae available, how to use them etc. There is some
extremely basic stuff there, some more advanced stuff (Part IV is Array
Formulae). The best bit is part III for me, Financial Formulae, but
probably because I know naff all about them. Despite what Joe says, I
think it is well formatted, I think that is one of John's strengths.

I think the market needs a book that starts at the intermediate user, and
gives them tools and techniques to become an expert. No basic stuff, all
that is assumed in the reader. I really don't think that book exists at
this point. And of course, if anyone wants to write it, publishers now
want it to be focussed on Excel 2007.

Using this method the ranges must be the same size *and* shape.

But then again, you could always transpose if needed:

=AVERAGE(IF(CHOOSE({1,2},A1:A5,TRANSPOSE(D1:H1))=" x",CHOOSE({1,2},B1:B5,TRANSPOSE(D2:H2))))

Biff

"T. Valko" wrote in message
...
Can you provide a link to that discussion?
This is the one.
http://tinyurl.com/yn64g2

Ok, I remember reading that thread.

That thread also fits the evolved topic of this thread. Toppers says:

"There is a market for somone who could distill all the knowledge into a
single reference"

I think we agree but it would be a lot of work!

As for the subject of that thread, here's another formula to "do stuff" on
non-contiguous ranges:

Array entered:

=AVERAGE(IF(CHOOSE({1,2,3},B6:B19,B27:B40,H6:H19)= "x",CHOOSE({1,2,3},C6:C19,C27:C40,I6:I19)))

Using this method the ranges must be the same size *and* shape.

Biff

"Bob Phillips" wrote in message
...

"T. Valko" wrote in message
...
I agree it falls well short of discussing advanced techniques such as
the one we discussed back on 25th June

Can you provide a link to that discussion?


This is the one.

http://tinyurl.com/yn64g2

You won't find it anything new, but it was new to Joe (who by his own
admission is an advanced user) at that time, and the discussion was the
interesting part. At least I thought it was relevant to this thread.


I don't know what "that topic" is but I've also "thought about writing a
book" on "advanced formulas". I wouldn't know where to start! I haven't
a clue about how to write a book! I don't have JW's book, but does it
cover the types of formulas and techniques we routinely provide in these
groups? I have a couple of old books but they're pretty much useless for
anything above the very basics:


That topic was advanced Excel techniques, not just formulae, but mainly
formulae driven.

It's a while since I have looked at John's book, but as I recall, it
wasn't going into the depth of some of the solutions of the type that we
offer here. It was more along the lines of taking themes, and developing
them, showing the formulae available, how to use them etc. There is some
extremely basic stuff there, some more advanced stuff (Part IV is Array
Formulae). The best bit is part III for me, Financial Formulae, but
probably because I know naff all about them. Despite what Joe says, I
think it is well formatted, I think that is one of John's strengths.

I think the market needs a book that starts at the intermediate user, and
gives them tools and techniques to become an expert. No basic stuff, all
that is assumed in the reader. I really don't think that book exists at
this point. And of course, if anyone wants to write it, publishers now
want it to be focussed on Excel 2007.

Hi,

Someone asked me to post back and let people know my thoughts on the book.
I received the book last Friday and have been using it since, working
through the examples and reading the tips and tricks.

First imprssions are that it will be a helpful addition to my collection but
like most of these books, it falls short in a number of areas.

I often find that the examples are to simplistic and dont really cover 'real
world' problems. They appear to be designed to meet the requirement of the
beginner were I was looking for a bit more. Dont get me wrong, i am by no
means an expert, far from it, but I can muddle through reasonable well.

For example, the sumproduct examples in the book are limited to a couple of
descriptions and yet on this forum it seems that sumproduct is used in a vast
number of the answers.
Take the formula below which was written by bob if i remember rightly. Its
real world and its doing a number of things and yet there is nothing in the
book like this. Its all there seperately, but there is nothing on how to put
it together and make it work.

=IF($G19<"",SUMPRODUCT((ROW(INDIRECT($F19&":"&$G1 9))=AC$16)*(ROW(INDIRECT($F19&":"&$G19))<DATE(YEA R(AC$16),MONTH(AC$16)+1,1))),SUMPRODUCT((ROW(INDIR ECT($F19&":"&$F$1))=AC$16)*(ROW(INDIRECT($F19&":" &$F$1))<DATE(YEAR(AC$16),MONTH(AC$16)+1,1))))

Having said that, the detail is all in the book and the fun is as much about
the journey as the end. So I will be making good use of the book but I will
still be looking for a book that goes that bit further.

Rgds Paul

" wrote:

phocused wrote:
"Bob Phillips" wrote:
There is really only one book on formulae worth its salt IMO, and that is
John Walkenbach's Excel 2003 Formulas (sic!).

Thanks for the help, reccomendations.
I have gone for Bob's suggestion.

And please post back with your impression of the book after you have
read it and tried to use it for a while.

I have both of Walkenbach's Excel books. (Well, he might have more
than two. Unfortunately, they are both packed away during remodeling,
so I cannot check the titles.) They are certainly full of useful
information. But I am very disappointed in their organization,
presentation and completeness. After reading the VBA book nearly a
year ago, I never can find anything when I go to look up something that
I "know" is in the book. And as I recall, things were not presented in
a "logical" sequence in the first place.

I have used the Excel Formulas book only sparingly, having learned
Excel on my own very easily based on years of experience with
pioneering spreadsheet products. But it, too, suffers as a reference,
IMHO.

Having said all that, I should add that I am a professional computer
programmer (post grad degree) with many decades of experience in system
programming in another part of the industry. Arguably, perhaps my
expectations are too high. In fact, I am sure they are, considering
the seemingly overwhelming good reviews of Walkenbach's books.

PS: On the other hand, I have yet to find a book that presents Excel
and VBA programming any better. I think the MS industry is severely
lacking in quality texts. At least, I have yet to stumble upon
anything really good.

Paul,

Thanks for letting us know.

I think you are absolutely right, but as I said earlier, I don't think that
the book that you want exists (yet!). If you want a good description on
SUMPRODUCT see =SUMPRODUCT(--(A1:A20-DAY(A1:A20)+1=DATE(2006,10,1))) (I know
it's good, I wrote it <vbg).

I think that one the problems is that these esoteric solutions are logic
based, not formula based, that is they arise from understanding formulae and
seeing how they can be stretched, they don't necessarily arise from the
formula, the logic can be applied to many functions. As such, it probably
doesn't fit in describing how particular functions work. But ... maybe a
chapter on building complex formulae could cover it, but again it is no
trivial subject (I do a two day course on the same subject).

Anyway, I hope that the book helps, and the page above. Keep visiting,
that's the best way to learn.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phocused" wrote in message
...
Hi,

Someone asked me to post back and let people know my thoughts on the book.
I received the book last Friday and have been using it since, working
through the examples and reading the tips and tricks.

First imprssions are that it will be a helpful addition to my collection
but
like most of these books, it falls short in a number of areas.

I often find that the examples are to simplistic and dont really cover
'real
world' problems. They appear to be designed to meet the requirement of
the
beginner were I was looking for a bit more. Dont get me wrong, i am by no
means an expert, far from it, but I can muddle through reasonable well.

For example, the sumproduct examples in the book are limited to a couple
of
descriptions and yet on this forum it seems that sumproduct is used in a
vast
number of the answers.
Take the formula below which was written by bob if i remember rightly. Its
real world and its doing a number of things and yet there is nothing in
the
book like this. Its all there seperately, but there is nothing on how to
put
it together and make it work.

=IF($G19<"",SUMPRODUCT((ROW(INDIRECT($F19&":"&$G1 9))=AC$16)*(ROW(INDIRECT($F19&":"&$G19))<DATE(YEA R(AC$16),MONTH(AC$16)+1,1))),SUMPRODUCT((ROW(INDIR ECT($F19&":"&$F$1))=AC$16)*(ROW(INDIRECT($F19&":" &$F$1))<DATE(YEAR(AC$16),MONTH(AC$16)+1,1))))

Having said that, the detail is all in the book and the fun is as much
about
the journey as the end. So I will be making good use of the book but I
will
still be looking for a book that goes that bit further.

Rgds Paul

" wrote:

phocused wrote:
"Bob Phillips" wrote:
There is really only one book on formulae worth its salt IMO, and
that is
John Walkenbach's Excel 2003 Formulas (sic!).

Thanks for the help, reccomendations.
I have gone for Bob's suggestion.

And please post back with your impression of the book after you have
read it and tried to use it for a while.

I have both of Walkenbach's Excel books. (Well, he might have more
than two. Unfortunately, they are both packed away during remodeling,
so I cannot check the titles.) They are certainly full of useful
information. But I am very disappointed in their organization,
presentation and completeness. After reading the VBA book nearly a
year ago, I never can find anything when I go to look up something that
I "know" is in the book. And as I recall, things were not presented in
a "logical" sequence in the first place.

I have used the Excel Formulas book only sparingly, having learned
Excel on my own very easily based on years of experience with
pioneering spreadsheet products. But it, too, suffers as a reference,
IMHO.

Having said all that, I should add that I am a professional computer
programmer (post grad degree) with many decades of experience in system
programming in another part of the industry. Arguably, perhaps my
expectations are too high. In fact, I am sure they are, considering
the seemingly overwhelming good reviews of Walkenbach's books.

PS: On the other hand, I have yet to find a book that presents Excel
and VBA programming any better. I think the MS industry is severely
lacking in quality texts. At least, I have yet to stumble upon
anything really good.