Learning when and where to use a formula...

[dcook22]

Hi all,

Like so many others, I find the members of this forum to be immensely helpful and I'm grateful for that. I am also interested in really learning how to use excel effectively and efficiently. While it's great to ask for help and have a member suggest a formula or even the setup of an entire sheet, I was wondering if there is some resource where i can find out when and where to best use a formula. For example, I have read about summing formulas and lookups and indexing, but my problem is when I'm designing a spreadsheet, I have trouble knowing which formula to use and when. I find myself building "clunky" formulas and constantly saying to myself "there's got to be a simpler way to do this".

Any suggestions other than practice, practice, practice?

I'm willing to do that but if I can cut the learning curve I'd be very happy.

Thanks in advance.

Dan

 

[Joe4]

There are a number of books and tutorials out there that you might might useful. I did a simple Google search on "excel formulas tutorial", and it returned a lot of promising hits.

The MrExcel store has a few books which might be helpful also. They have a few books, like Excel for Engineers and Guerilla Data Analysis. And there are many other good books out there that specialize in different industries, if you work in some specialized field. Sites like Amazon.com usually have buyer reviews, which I find helpful when determining which book to buy.

I also find that the built-in Excel help does a pretty darn good job with formulas. They explain them in detail, and often give useful examples. And the Excel function key (icon with the big F and little x) will help you write most formulas. If nothing else, this is a good place to "browse" all the functions available. They have them grouped by category. You can then use the built-in help to research more about them.

Another handy thing is to spend some time on this board and check out questions that pique your interest and follow along and see what answers are provided (you can "subscribe" to any thread that you like, so that you will get an e-mail notification when there is a reply to it). Even us experts do that to learn new tips and techniques!

And of course, practice, practice, practice and we are always here to lend a helping hand! I have asked a bunch of questions myself that begin with "this works, but is there a better way".​

 

[dcook22]

Hi Joe,

Thanks for the reply. Excellent advice, and I'll follow it. I like the "this works, but is there a better way?" approach. I'll use that.


You wrote:

I also find that the built-in Excel help does a pretty darn good job with formulas. They explain them in detail, and often give useful examples. And the Excel function key (icon with the big F and little x) will help you write most formulas. If nothing else, this is a good place to "browse" all the functions available. They have them grouped by category. You can then use the built-in help to research more about them.​

That's kind of the problem. Looking up a formula or function is great, but I don't know which function I should be looking up. I think of the manager who says to his trainees "OK, if you don't know something, ask me." Well, if they don't know it, how do they know to ask? They don't know that they don't know. I don't know which formula need so i don't know which to look up. I could spend hours randomly searching.

That said, if it's the best way to learn, I'll certainly do it.

Also, I have purchased two books. Both by John Walkenbach.

The Excel 2007 Bible and Excel 2007 Formulas. Both have been helpful.

Thanks agiain.

 

[Joe4]

Looking up a formula or function is great, but I don't know which function I should be looking up. I think of the manager who says to his trainees "OK, if you don't know something, ask me." Well, if they don't know it, how do they know to ask? They don't know that they don't know. I don't know which formula need so i don't know which to look up. I could spend hours randomly searching.

That said, if it's the best way to learn, I'll certainly do it.

I understand what you are saying, you don't know what you don't know. The purpose of that particular advice is to just familiarize yourself with some of the different functions out there. I do this from time to time, and see something that looks interesting, so I check it out, and often times end up thinking, "Wow, I didn't know there was a function that does that!".

An example of that was way back in my early days of Excel, if I had to make a number positive, I would use a function like:
=IF(A1<0,A1*-1,A1)
which basically says if the number in A1 is negative, multiply it by -1 to make it positive.

Then one day I stumbled upon the Absolute Value function (ABS). So you could replace what is up above with:
=ABS(A1)
Sometimes, just being aware of what is available is half the battle.

Like I said, it won't solve of your problems, but can be a useful tool in helping with some of them.

By the way, I have heard excellent things about John Walkenbach's books. So sounds like you made some good purchases there.

 

[Smitty]

If you PM me your e-mail address I'll send you a workbook that has examples of all of Excel's native formulas and scenarios for using them.

 

[dcook22]

If you PM me your e-mail address I'll send you a workbook that has examples of all of Excel's native formulas and scenarios for using them.

PM being sent now.

Thanks.