Fundamental Excel Formulas For Basic office works

Fundamental Excel Formulas For Basic office works

Fundamental Excel Formulas For Basic office works.

Excel has a huge database of formulas to be useful everyday work. Learning them in details will be helpful to the daily functions.

There is basic Excel and Advanced Excel formulas.

We have listed some of the functions and formulas that you can use  these Excel formulas for the daily office or for school projects

 Using Sum() formula in excel.

  1. SUM

The SUM function is the first must-know formula in Excel. It usually aggregates values from a selection of columns or rows from your selected range.

=SUM(number1, [number2], …)

Example:

=SUM(a1:x1) –  start selecting from A1 drag it upto x10.A simple selection that sums the values of a row.

=SUM(A2:A8) – A simple selection that sums the values of a column.

=SUM(A2:A7, A9, A12:A15) – A sophisticated collection that sums values from range A2 to A7, skips A8, adds A9, jumps A10 and A11, then finally adds from A12 to A15.

=SUM(A2:A8)/20 – Shows you can also turn your function into a formula.

Using Average() formula in excel.

AVERAGE

The AVERAGE function should remind you of simple averages of data such as the average number of shareholders in a given shareholding pool.

=AVERAGE(number1, [number2], …)

Example:

=AVERAGE(A1:A10) – Shows a simple average, also similar to (SUM(A1: A10)/9)

Using Count() formula in excel.

  1. COUNT

The COUNT function counts all cells in a given range that contains only numeric values.

=COUNT(value1, [value2], …)

 

Example:

COUNT(A:A) – Counts all values that are numerical in A column. However, it doesn’t use the same formula to count rows.

COUNT(A1:C1) – Now it can count rows.

 

  1. COUNTA

Like the COUNT function, COUNTA counts all cells in a given rage. However, it counts all cells regardless of type. That is, unlike COUNT that relies on only numerics, it also counts dates, times, strings, logical values, errors, empty string, or text.

=COUNTA(value1, [value2], …)

Example:

COUNTA(A:A) – Counts all cells in column A regardless of type. However, like COUNT, you can’t use the same formula to count rows.

 

Using if logical  formula in excel.

  1. IF

The IF function is often used when you want to sort your data according to a given logic. The best part of the IF formula is that you can embed formulas and function in it.

=IF(logical_test, [value_if_true], [value_if_false])

 

Example:

=IF(C2<D3, ‘TRUE,’ ‘FALSE’) – Checks if the value at C3 is less than the value at D3. If the logic is true, let the cell value be TRUE, else, FALSE

=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10, SUM(D1:D10)) – An example of a complex IF logic. First, it sums C1 to C10 and D1 to D10, then it compares the sum. If the sum of C1 to C10 is greater than SUM of D1 to D10, then it makes the value of a cell equal to the sum of C1 to C10. Otherwise, it makes it the SUM of C1 to C10.

Using Trim() formula in excel.

  1. TRIM

The TRIM function makes sure your functions do not return errors due to unruly spaces. It ensures that all empty spaces are eliminated. Unlike other functions that can operate on a range of cells, TRIM only operates on a single cell. Therefore, it comes with the downside of adding duplicated data in your spreadsheet.

=TRIM(text)

Example:

TRIM(A4) – Removes empty spaces in the value in cell A4.

 Using Max() Min() formula in excel.

  1. MAX & MIN

The MAX and MIN functions help in finding the maximum number and the minimum number in a pull of values.

=MIN(number1, [number2], …)

Example:

=MIN(B2:C11) – Finds the minimum number between column B from B2 and column C from C2 to row 11 in both column B and C.

=MAX(number1, [number2], …)

Example:

=MAX(B2:C11) – Similarly, it finds the maximum number between column B from B2 and column C from C2 to row 11 in both column B and C.

 

Using And() Functions in Excel

the AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE.

Example

To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1<10).

The AND function can be used as the logical test inside the IF function to avoid extra nested IFs, and can be combined with the OR function.

=AND (logical1, [logical2], …)

  • logical 1 – The first condition or logical value to evaluate.
  • logical 2 – [optional] The second condition or logical value to evaluate.

Consider

A     B

1      5     5

 

=and(a1=5,b1=5)

The above example returns True

Consider

A     B

1      6     5

The above example returns False

Using OR () Functions in Excel

the OR  function is a logical function used to require to check More than one condition at the same time. OR returns either TRUE or FALSE.

To test if a number in A1 contains a value “Passed” or “Ranked” we can use as OR(a1=”Passed”,a1=”Ranked”)

The OR function can be used as the logical test inside the IF function to avoid extra nested IFs, and can be combined with the AND function.

=OR(logical1, [logical2], …)

  • logical 1 – The first condition or logical value to evaluate.
  • logical 2 – [optional] The second condition or logical value to evaluate.

 

There are still more than these, if you want  to learn Advanced Excel in details Noble institute’s Advanced Excel helps you to learn it in affordable cost.

Please contact us for further details

No Comments

Post a Reply

Hi How May I help you?