• Breaking News

    List of Excel Formulas

      SUM Formula

      The SUM function ii the aggregates values from a selection of columns or rows from your selected range.

      =SUM(number1, [number2], …)

      =SUM(B2: G2) – A simple selection that sums the values of a row.

      =SUM(A2: A7, A9, A12: A15) – The 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.


      AVERAGE Formula

      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], …)

      =AVERAGE(B2:B11) – Shows a simple average, also similar to (SUM(B2:B11)/10)

       

      COUNT Formula

       

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

      =COUNT(value1, [value2], …)

      =COUNT(A:A) – Counts all values that are numerical in A column. However, you must adjust the range inside the formula to count rows.

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

       

      IF Formula

      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])

      =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

       

      MAX & MIN Formula

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

      =MIN(number1, [number2], …)

      =MAX(number1, [number2], …)

       

      AND & OR Formula

      These are the two most popular logical functions to check multiple criteria. The difference is how they do this:

       

      AND returns TRUE if all conditions are met, FALSE otherwise.

      OR returns TRUE if any condition is met, FALSE otherwise.

       

      =IF(AND(A2>60, B2>60), "TRUE", "FALSE")

      =IF(OR(A2>60, B2>60), "TRUE", "FALSE")

       

      TEXT

      =TEXT(Value you want to format, "Format code you want to apply")

      =TEXT(TODAY(),"MM/DD/YY")

      Today’s date in MM/DD/YY format, like 03/14/12

      =TEXT(TODAY(),"DDDD")

      Today’s day of the week, like Monday

      =TEXT(0.285,"0.0%")

      Percentage, like 28.5%

       

      No comments

      View My Stats