• Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

11 lesser-known but useful Excel functions ( P1 )

0 Comments

11 lesser-known but useful Excel functions: Microsoft Excel provides hundreds of functions, so there are definitely a few that you may not be aware of. These unique functions have specific purposes that you’ll be excited to learn and use.

FLOOR and CEILING for rounding

You can use the mathematical functions FLOOR and CEILING to round towards or away from zero to a specified multiple. Use FLOOR to round down and CEILING to round up.

The syntax for each is FLOOR(value, multiple) and CEILING(value, multiple) where both arguments are required.

To round 4.4 down to the nearest multiple of 2, you would use the following formula:

=FLOOR(4.4,2)

11 lesser known but useful Excel functions3

To round 5.6 up to the nearest multiple of 2, you would use the following formula:

=CEILING(5.6,2)

11 lesser known but useful Excel functions3

MODE.SNGL to find repeated values

Originally just a simple MODE function, Microsoft created an improved version of this statistical function for better accuracy. Use MODE.SNGL to find frequently repeated numbers in a range or array of cells.

The syntax is MODE.SNGL(array1, array2, …) where only the first argument is required. You can use numbers, names, arrays, or cell references that contain numbers. Use optional arguments for additional ranges of cells.

Here, we find the most frequently repeated number appearing in cells A1 to A5.

=MODE.SNGL(A1:A5)

11 lesser known but useful Excel functions4

To find a recurring number in A1 to A5 and C1 to C5, you would use the following formula:

=MODE.SNGL(A1:A5, C1:C5)

11 lesser known but useful Excel functions5CONVERT to switch from one measurement to another

For useful technical functionality, you can use CONVERT to change a value from one measurement system to another.

The syntax is CONVERT(value, from, to) where you’ll need all three arguments. For the from and to arguments, you’ll use abbreviations. Check Microsoft’s Support website for the abbreviations you need for weight and mass, distance, time, pressure, force, energy, power, magnetism, temperature, volume, area, information, and speed.

To convert the value in cell A1 from degrees Celsius to degrees Fahrenheit, you would use the following formula:

=CONVERT(A1,"C","F")

11 lesser known but useful Excel functions6To convert the value in cell B1 from centimeters to inches, you would use the following formula:

=CONVERT(B1,"cm","in")

11 lesser known but useful Excel functions7DELTA to check for equal or unequal values

Another useful technical function is DELTA. With it, you’ll use the delta Kronecker function to check if two values are equal or not. Unlike the EXACT function, the result is 1 (true) or 0 (false).

The syntax is DELTA(value1, value2) where only the first argument is required and can be a number or cell reference. If you leave the second argument blank, Excel assumes it to be zero.

To check the values in cell A1 and B1, you would enter the following formula:

=DELTA(A1,B1)

11 lesser known but useful Excel functions8To check the values 2 and -2, you would use the following formula:

=DELTA(2,-2)

11 lesser known but useful Excel functions9GESTEP to check for greater than or equal to threshold

Another useful technical function you may find handy is GESTEP, which allows you to check if a value is greater than or equal to a step (threshold). The result is 1 (true) or 0 (false).

The syntax is GESTEP(value, step) where only the first argument is required and can be a number or cell reference. If you leave the second argument blank, Excel uses zero.

To check the value in cell A1 against a step of 4, you would use the following formula:

=GESTEP(A1,4)

11 lesser known but useful Excel functions10To check the value 10 against a step of 12, you would use the following formula:

=GESTEP(10,12)
11 lesser known but useful Excel functions

11 lesser known but useful Excel functions

Rate this post
19
243 Views
How to Find Bold Cells using VBA in Microsoft Excel 2010PrevHow to Find Bold Cells using VBA in Microsoft Excel 2010July 14, 2023
Simplifying Data Analysis: Using VLOOKUP and CHOOSE Functions TogetherJuly 15, 2023Simplifying Data Analysis: Using VLOOKUP and CHOOSE Functions TogetherNext

Leave a Reply Cancel reply

You must be logged in to post a comment.

Buy Windows 11 Professional MS Products CD Key
Buy Office 2021 Professional Plus Key Global For 5 PC
Top rated products
  • Kaspersky Internet Security 2021 1 year 5 devices key Global Kaspersky Internet Security 2021 1 year 5 devices key Global
    Rated 5.00 out of 5
    $45.00
  • Windows Server 2022 Datacenter Key Global Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $7.00
  • Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year
    Rated 5.00 out of 5
    $164.50
  • Avast Ultimate Suite 2021 1 Year 10 Devices Global Avast Ultimate Suite 2021 1 Year 10 Devices Global
    Rated 5.00 out of 5
    $54.00
  • Windows 11 Pro Key Global Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $6.00
Products
  • Microsoft Visio 2019 professional Key Global Bind to your Microsoft Account Microsoft Visio 2019 professional Key Global Bind to your Microsoft Account
    Rated 4.96 out of 5
    $12.00
  • Windows Server 2019 Datacenter Key Global Windows Server 2019 Datacenter Key Global
    Rated 4.75 out of 5
    $7.00
  • Kaspersky Small Office Security 20 PCs + 20 Mobiles + 2 Servers 1 Year Kaspersky Small Office Security 20 PCs + 20 Mobiles + 2 Servers 1 Year $284.73
  • Avast Premium Security 2021 10 Devices 2 Years Global Avast Premium Security 2021 10 Devices 2 Years Global
    Rated 5.00 out of 5
    $41.00
  • Buy Windows Server 2016 Essentials Key Global Buy Windows Server 2016 Essentials Key Global
    Rated 4.75 out of 5
    $10.00
  • Kaspersky Standard - 1 Device, 1 Year (Global License) Kaspersky Standard - 1 Device, 1 Year (Global License) $21.00
  • Avast Premium Security 2021 10 Devices 1 Year Global Avast Premium Security 2021 10 Devices 1 Year Global
    Rated 5.00 out of 5
    $28.00
  • microsoft office home and business 2019 pcmac key Microsoft Office Home And Business 2019 (MAC) key bind to your Microsoft account
    Rated 4.95 out of 5
    $30.00
  • Buy Windows 11 Professional MS Products CD Key Buy Windows 11 Professional MS Products CD Key
    Rated 5.00 out of 5
    $6.00
  • Windows Server 2022 Standard Key Global Windows Server 2022 Standard Key Global
    Rated 4.10 out of 5
    $7.00
Product categories
  • Anti Virus
  • Microsoft Office
  • Microsoft Project
  • Microsoft Visio
  • Microsoft Visual
  • Microsoft Windows
  • Other Software
  • Uncategorized

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.