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

 

[KenWright]

Literally dozens of examples of SUMPRODUCT / SUMIF / COUNTIF etc tha\t may help in the following thread:-

thread68-1291533

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[lcf14]

Thanks Ken! I haven't had time to really try to understand the whole thread(68-1291533) but it looks like it contains alot of great information.

With everyone's help I have been able to make a big dent in the project that I'm working on and hopefully am beginning to better understand excel formulas.

Thanks Again
Lorraine

 

[KenWright]

It's usefull mainly for examples of syntax and showing you how you would incorporate references / ranges / conditional counts etc into the various formulas.

Also, I'm a 110% in agreement with Skip, although sometimes I'll work from the outside in

I tend to build a formula with hardwired variables in it, and then gradually replace the variables with other functions one by one, until you end up with a formula with nothing hard-wired in it if possible.

One neat function is Tools / Formula Auditing / Evaluate Formula, which allows you to step through each piece of a formula and see what is/isnt working, which is very useful when you have nested formulas.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Welshbird]

I do the same Ken, but with bits of the complex formula in different columns, then I can check each bit and the total.

So, eg, for an IF statment with a nested VLOOKUP, I'll evaluate the VLOOKUP in one column (and therefore get to check it) and then write the IF referencing the other column.

Once I'm happy, I can build the complex formula and check that I get the same result.

(The more I learn and the more experience I gain, the more I do this, as I still find it so helpful)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[lcf14]

I'm stuck again! With all of your help I feel like I should be able to figure it out but so far I can't. This formula works:

=MEDIAN(IF(disc="y",IF(obj="equ",total,""),""))

but now I need to add one more criteria (tax="tax").

Thanks in advance for your help.
Lorraine (aka Lo)

 

[Gavona]

I think you are looking for this:
=MEDIAN(IF(disc="y",IF(obj="equ",total,If(tax="tax",total,""),""))

But this is easier to follow if there are indeed only two possible results: Median(total) or Median("")

=median(if(and(disc="y",obj="equ",tax="tax"),total,"")


Gavin