One of the most common uses for Excel is computing large amounts of complex data. Knowing the best formulas to use allows you to crunch those numbers quickly to get the results you need. The formulas you can use vary from simple computation to condition-based criteria. Excel even provides you with specific error messages when data is entered incorrectly. Let’s get started with some of the most used simple formulas.
Most Commonly Used Formulas in Excel
| Sum=(cell:cell) | This formula allows you to quickly add numbers in a given range. |
| Average=(cell:cell) | This formula allows you to find the mean of a given range. |
| Count=(cell:cell) | This allows you to count the total number of cells in a given range |
Using IF Criteria in Excel Formulas
IF Functions allow you to calculate data based on specific criteria.
IF Functions
|
IF |
Evaluates a cell, returns a
true value or returns a false value |
In cell H3:
=IF(G3>=20%,”Bonus”,”NA”) |
|
Nested IF |
Evaluates multiple criteria, returns a true value or returns a false value | In cell I3:
=IF(G3=10%,”Send Thank You”,IF(G3=15%,”Send Gift”,IF(G3=20%,”Call”,0))) |
|
SUMIF |
Evaluates a range of data,
sums if it matches the criteria. The sum range is the third argument in the formula. |
In cell D7:
=SUMIF(C3:C6,”ABC”,D3:D6) |
|
COUNTIF |
Evaluates a range of data,
counts if it matches the criteria. |
In cell D8:
=COUNTIF(C3:C6,”ABC”) |
|
AVERAGEIF |
Evaluates and averages like
SUMIF. |
In cell D9:
=AVERAGEIF(C3:C6,”ABC”,D3:D6) |
|
SUMIFS |
A SUMIF with multiple
criteria |
In cell D11:
SUMIFS(D3:D6,F3:F6,1200,C3:C6,”ABC”) |
|
COUNTIFS |
A COUNTIF with multiple
criteria |
In cell D12:
=COUNTIFS(C3:C6,”ABC”,D3:D6,30) |
Excel Error Messages
If you enter a formula in Excel that is a circular references or has incomplete data, excel gives you and an error message that identifies the specific problem. You can use that information to correct the problem.
| #DIV/0 | If you receive this message it is because the formula entered is trying to divide by zero. |
| #N/A! | This message indicates the formula or a function within the formula cannot find the referenced data. |
| #VALUE! | This indicates the operand or function used is the wrong type. |
| #NULL! | This error message occurs when there is a space used in a formula that reference multiple ranges. Use a comma to separate range references. |
| #NAME? | If the text in the formula is not recognized you will receive this error message. |
| #REF! | This indicates you used an invalid reference. |
| #NUM! | This error message occurs when the type of operation in a formula has invalid numeric data. |
Here at Learn Excel Now we are committed to helping you do your job better. Please feel free to use these as a quick reference “cheat sheet” when entering formulas in Excel. Determining what formulas work best also depends on your specific needs when using Excel.
If you’re ready to go beyond the basic and start using powerful solutions, you might benefit from this Excel formulas and functions training.
Like these quick and easy tips or have suggestions? Leave your comments below; we’d love to hear from you.
Spread the word and share the knowledge with our social media links!
Kevin – Learn Excel Now.
Susan George says
Awesome training for starters. Really appreciate it
Mike Sullivan says
Thanks. These are simple formulas that I have seen before but when you don’t use them very often it is easy to forget. I will keep them where I can get too them.