Suggestions on how to learn Excel formulas? 5

[lcf14]

Last Friday I took a level 2 excel class that supposedly taught advanced formulas. It wasn't very helpful because it only covered a couple of items. I always try to use "help" inside of excel and that usually adds to my confusion. How can I teach myself more complicated formulas than just basic ones (i.e sum, count)? Any recommendations on a book?

Thanks

 

[SkipVought]

Hi,

This is similar advice that I give to people who want to learn VBA coding.

1. Excel HELP

2. Tinker with examples

3. Tek-Tips MS Office star posts.

4. Play with examples

5. Check out the local bookstores. Browse some books and see which one(s) seem to click with you.

6. Tinker some more. Try stuff out.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

As Skip mentions - just try stuff out - you won't break excel

Better than knowing exactly how each formula works is the knowledge of how to nest formulae ie to use the result from one formula as an argument in another - that is where the real power of excel comes. I personally probably only use 20 formulae on a regular basis but I can nest them together to make any number of variations to do what I need to do:

Key formulae for me:

SUM
COUNT
COUNTA
AVERAGE
SUMIF
COUNTIF
VLOOKUP
INDEX
MATCH
SUMPRODUCT
AND
OR
IF
INDIRECT
OFFSET
MAX
MIN
LARGE
SMALL
SUBTOTAL

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

... and continuing on Geoff's suggestion, when building complex expressions, start from the inside and work out.

For instance, You might have a MATCH function embedded in a complex formula. First write and test the MATCH to make sure it works. You can COPY the text of the expression and PASTE it into your more complex one. It might appear in more than one place.

The other thing to gain an understanding of is RELATIVE and ABSOLUTE references. You can ues the F4 key to cycle thru...
absolute row/column,
absolute row/relative column,
relative row/absolute column, &
relative row/column.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

top tip that Skip - start from the inside and work out - absolutely agree

Missed some string manipulation formulae out from my 1st post though:

LEFT
RIGHT
MID
FIND
TEXT
VALUE

Personally, I find the help file pretty helpful with formulae - it is easier if you understand some of the syntax used however - this is probably where the most use would be to you but depends on what syntax is "not helpful"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lcf14]

Thanks, I did spend quite a bit of time yesterday playing with my "sumif" question before emailing the group. Although I did not break excel, I think it started to break my spirit. I know I can automate so many of my tasks here at work if I can learn to use excel formulas better. I'll keep trying...and reading these posts!

 

[xlhelp]

Peter Nonely created an xl function dictionary that is very useful in understanding these functions.

You can download the file at Ron DeBruin's site.

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

Also look at NAMED RANGES. There are 3 ways to name a range...

1. Select the range. Enter a Name in the Name Box. (No Spaces or special characters in Names)

2. Names for Columns or Rows with headings - Select the Table/List including headings. Insert/Name/Create - Create names in... and select Top, Left, Bottom or Right -- usually one (mostly TOP for me) sometimes two (like TOP & LEFT)

3. Name a range in Define Names - Insert/Name/Define - Type the Name in the Names in workbook textbox and the fully quallified range in the RefersTo textbox.

Names can be used in formulas in lieu of range references. VERY POWERFUL and helps in understanding what the expression is doing.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

In terms of the specifics of SUMIF - what is the issue you have with it ? which part of the help file are you struggling with - maybe we can shed some light ? This may help you to better understand the help files in general...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[NoCalAdmin]

http://www.microsoft.com/education/Tutorials.aspx

select the program you want to learn and the level you're at....

 

[lcf14]

I did get responses to my question yesterday that I'm just getting ready to try. First, I needed to check one column and then sum data, so SUMIF worked great. Then I needed to check data in 2 columns and if they were both = to my criteria then sum data. I assumed that I would still use SUMIF along with an "and" statement or something to that effect. I tried "help" and many different variations of formulas, but couldn't make it work. Assuming the responses I got work, then I don't know how I was supposed to come up with those answers. Is it just time and experience? Here was my example

Total OBJ Tax
21875463.89 Bal Tax
12765846.19 Bal Tax
11922258.7 Fix Tax
10424317.12 Bal Non
7688947.39 Fix Tax
6803845.67 Bal Non

Responses were:=SUMPRODUCT((Total)*(OBJ="Bal")*(Tax="Tax"))

Or use an array
=SUM(IF(B1:B6=OBJ,IF(C1:C6=Tax,A1:A6,""),""))
Enter the formula and press ctrl+shift+enter to get:
{=SUM(IF(B1:B6=OBJ,IF(C1:C6=Tax,A1:A6,""),""))}

 

[SkipVought]

Did you enter each of those expressions and try to understand what is happeneing in each?

I believe that the SUMPRODUCT was my post using Named Ranges.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

aaaah yes - multi condition sums/counts. This is where excel help falls over as it doesn't have a formula to do this per se. the SUMPRODUCT method is actually a corruption of its intended use and array formulae can be used for many things - not just multi criteria sums

This is an area where you just have to ask and remember the answer - not sure there is any good way of working out how to do this kind of formula except knowing which sites to come and ask questions on !!

This is pretty much a one off though - most other calculations can be done with formulae where help IS available

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lcf14]

I am trying to work on suggestions given to me yesterday & they are VERY much appreciated! Unfortunately, I get pulled in many directions at work, as I'm sure we all do. I will let you know if I'm successful and which way worked best for me. I will keep these examples & use them for future reference.

This website was the single most valuable piece of information that I received in my full day excel class!!!!
Lorraine

 

[lcf14]

SUMPRODUCT suggestion worked perfectly. Thanks

 

[SkipVought]

FYI on how SUMPRODUCT works in the count/sum area...

faq68-4725

Skip,
[sub]
[red][/red]
[/sub]

 

[lcf14]

ok, I tried SUMPRODUCT, it worked and I understood it. I just tried the array and was able to get it to work but had more trouble with it. I am making a template on one worksheet with all my formulas and will be pasting data into a 2nd worksheet. I thought you used $ to make something absolute so when I copied the formula down cells it wouldn't change the range. That didn't seem to work. I also tried the F4 key that I beleive Skip mentioned but must not have been using it at the appropriate time because it didn't work. Here is a copy of my formula. Any thoughts:

=SUM(IF('AUM Spreadsheet'!$F7:$F1500="bal",IF('AUM Spreadsheet'!$I7:$I1500="tax",'AUM Spreadsheet'!$E7:$E1500,""),""))

 

[SkipVought]

This range reference...
[tt]
$F7:$F1500
[/tt]
says keep COLUMN F ABSOLUTE and the ROWS RELATIVE.

So if I copy ACROSS, COLUMN F does not change

If I copy DOWN, the rows change.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

Enter your formula with ctr+shift+enter as it is an ARRAY FORMULA. It worked for me as...
[tt]
=SUM(IF(OBJ="bal",IF(Tax="tax",Total,0),0))
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[Gavona]

Common functions, named ranges, links to brilliant sites to further explain functions and Tek-Tips. This post has to deserve a star or three!


Gavin