Search TeachExcel.com

Video Tutorials

Tutorials

Featured Tutorial

Dynamically Update Drop-Dowm Menus in Excel

Featured Macro

Delete Empty Rows in Excel

Excel Formula Efficiency 1: SUMPRODUCT function 12 Examples

Video | Similar Helpful Excel Resources

Learn about the basics of SUMPRODUCT function and why some people use double negative and why other use times one. Learn theses important points:
1.When using the SUMPRODUCT function and multiple criteria, see how multiplying by one is more flexible than the double negative
2.When using the SUMPRODUCT function and multiple criteria, see how using double negative will not yield an error when text is in the data range but multiplying by one will yield an error (double negative can handle text)
3.When using the SUMPRODUCT function and multiple criteria, see how using double negative is sometimes faster than multiplying by one

Learn how to speed up slow calculating spreadsheets with formulas that calculate faster. See many methods to speed up your spreadsheet decrease the amount of time it takes for the formulas to calculate. The tricks you see are from a White Paper called Improving Performance in Excel 2007. In this video series you will see tricks for both Excel 2003 and Excel 2007. Many of the large formulas in this series involve multi-conditional multiple criteria calculations that slow down the spreadsheet (worksheet, workbook).

Got a Question? Ask it Here in the Forum.

Similar Helpful Excel Resources

Efficiency Of Array Formula's - Sumproduct? - Excel

View Content

Morning Guys & Gals,

I've a large unwieldy spreadsheet i've concocted with an array of array formula's. Here's one i made earlier:

{=SUM(IF(ISNUMBER(MATCH(LEFT(INDIRECT($W$1),1),{"E"},0)),IF(INDIRECT($Y$1)=$C$3,IF(INDIRECT($Z$1)="Book",IF(INDIRECT($AA$1)="New",INDIRECT($X$1))))))}

What this does is to look up (via those indirect refs) a data sheet, based on criteria in 4 columns.

I suspect that sumproduct might be more efficient, but i'm unsure how to convert the thing :s

Also, will it be any faster?
Thanks!

Please Help Me Writing Vba Function In Excel With Simple Examples... - Excel

View Content

Hi, I need help in writing functions maybe for example to find if named sheet exist with x name...etc if not do this else that...or
to see if col has dates...
My actual question would be to learn how to write this kind of function.

Thanks in advance.
Pedie

Sumproduct Examples - Excel

View Content

Hi All,

I've seen several times on this forum the sumproduct formula being used where times I thought you would use SUMIF.

Could some one please provide some examples of SUMPRODUCT and how it works as the help file is pretty poor and only provides a really basic example.

Any help would be appreciated...

Needing Better Examples Of The Trim Function - Excel

View Content

Hello All,
I have a small but annoying issue. I have a spreadsheet with two columns in it. The A column has finished part numbers in it and the B column has the exact same finished part numbers but I need to remove anywhere from 1 to 4 zero's that are at the front of the finished part numbers.
Is there a way I can use the TRIM function to go through the entire column instead of one cell at a time. I have 5789 lines to go through.
Any assistance would be greatly appreciated. Thank you

Excel Examples - Excel

View Content

i have a task of producing a spreadsheet that asks questions and takes figures then displays them as a report.

e.g how much is your energy bill? the user types a figure, its calculated in a formula, and the results displayed in a report.

I need it set out so it looks professional and user friendly. Has anyone got any examples of well laid out user friendly colourful spreadsheets?

Would appriciate it if some one could help me.

Thanks a great deal

2500 Excel Vba Examples - Excel

View Content

Hello everybody,

Can anybody upload the
2500 Excel VBA Examples, if you have.

Examples Of Do Loops In Excel Vba - Excel

View Content

hi, i would like to know the site or resource for mastering Loops like DO loops and other loops with examples.if any body knows some sites do list here.

Looking For Funny Excel Examples - Excel

View Content

Looking For Excel Dashboard Examples - Excel

View Content

Hi,
I am looking for a good website specially a video tutorial that shows you how to create dashboards or charts. I am looking for similar website like http://www.datapigtechnologies.com/
this is very good website for those of you who need to do some fancy staff with excel. Any help will be appriciated. Thanks.

Efficiency Formula - Excel

View Content

I need to know the Excel (in cell) formula for the following equation:

LOAD x .177
(2*3.14159) x (TORQUE)

Where the LOAD = A1
&
Where the TORQUE = B1

Thanks,
13blues