Basic Math W Excel

Basic Math Functions in Excel

Calculating Remaining Quantity

  • To calculate remaining quantity:

    • Formula: =Inventory - Quantity Sold

    • Example: If Inventory = 56, Quantity Sold = 23, Remaining Quantity = =56 - 23, which results in 33.

    • Drag the fill handle to apply the formula to subsequent cells.

Calculating Percentage Sold

  • To calculate the percentage sold:

    • Formula: =(Quantity Sold / Inventory) * 100

    • Example: If 23 items sold from an inventory of 56, percentage sold = = (23 / 56) * 100, results in 41.07%.

    • Alternatively, divide and format the result as a percentage in Excel.

    • Use the percentage button to adjust decimal places.

Rounding Functions

Rounding Up and Down

  • To round a number in Excel:

    • Round Function: =ROUND(number, num_digits)

      • Usage example for rounding to nearest whole number with 0 decimal points.

    • Round Up Function: =ROUNDUP(number, num_digits)

      • Example: =ROUNDUP(1234.56, 0) results in 1235.

    • Round Down Function: =ROUNDDOWN(number, num_digits)

      • Example: =ROUNDDOWN(1234.56, 0) results in 1234.

    • Lock values using $ for consistent referencing when dragging formulas.

    • Adjust num_digits for rounding to different counts or multiples (negative values for rounding to tens, hundreds, etc.).

MROUND, CEILING, and FLOOR

  • MROUND Function: =MROUND(number, multiple):

    • Rounds to the nearest specified multiple (e.g., 2 for multiples of 2).

  • CEILING Function: =CEILING(number, significance):

    • Rounds up to the nearest specified multiple (e.g., rounds 1234 to 1236 for multiple of 2).

  • FLOOR Function: =FLOOR(number, significance):

    • Rounds down to the nearest specified multiple (e.g., rounds 1234 down to 1230 for multiple of 5).

Practical Uses

  • Utilize these functions for effective data analysis and inventory management in business.

  • Can be used to compute new columns for KPIs and metrics.