• Microsoft Office
  • Microsoft Windows
  • Other Software
  • 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
  • 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
  • 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
  • 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 ( P2 )

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.

Address to locate a cell

Switch to the lookup function in Excel. To find the exact address of a cell, you can use the ADDRESS function. This is convenient when you want an error-free reference to a cell.

The syntax is ADDRESS(row, column, type, style, name), where only the first two arguments are required. Enter the row number for the first argument and the column number for the second argument.

Optional arguments are as follows:

  • Type: The type of reference to return. Use blank or 1 for absolute row, 2 for absolute row and relative column, 3 for relative row and absolute column, or 4 for relative row.
  • Style: Use TRUE for A1 style or FALSE for R1C1 style of cell reference.
  • Name: The name of the worksheet for an external reference. If left blank, Excel assumes the current active worksheet.

To find the address of the cell in row 2, column 3, you use the following formula:

=ADDRESS(2,3)

11 lesser known but useful Excel functions12To find the address of the same cell using absolute row and relative column, you would use the following formula:

=ADDRESS(2,3,2)

11 lesser known but useful Excel functions13To find the address of the same cell in a worksheet named “Sheet2,” use the following formula. Note that the comma represents the empty “type” and “style” arguments.

=ADDRESS(2,3,,,"Sheet2")

11 lesser known but useful Excel functions14PI for the value of Pi

If you need to use the value of pi in your spreadsheet equations, you can retrieve that value using the PI function.

The simple syntax is PI(), with no arguments. You can add more elements to the formula if you want to use the value in a calculation.

To return the value of pi, simply use the formula of the function, including the parentheses:

=PI()

11 lesser known but useful Excel functions15To multiply the value of pi by 10, you would use the following formula:

=PI()*10

11 lesser known but useful Excel functions16ARABIC and ROMAN for number conversion

Another useful mathematical function is converting to and from Arabic and Roman numerals.

The syntax for each is ARABIC(text) and ROMAN(value, form), where the first argument is required for both.

The optional argument for the ROMAN function specifies the type of Roman numerals from Classic to Simplified. Enter 0, TRUE, or omit the argument for Classic. Use 1, 2, or 3 for shorter results. Or, enter 4 or FALSE for Simplified.

To convert the Roman numeral “MMIM” to an Arabic number, use this formula, making sure to enclose the text in double quotation marks:

=ARABIC("MMIM")

11 lesser known but useful Excel functions17To convert 2,999 to a Roman numeral, you would use the following formula:

=ROMAN(2999)

11 lesser known but useful Excel functions18To convert the same number in Simplified form, use one of the following formulas:

=ROMAN(2999,4)
=ROMAN(2999,FALSE)

11 lesser known but useful Excel functions19REPT to repeat text

If you want to add a series of characters, symbols, or text as a placeholder or to create a visual effect, use the REPT text function.

The syntax is REPT(text, number), where both arguments are required. Enter the text argument within double quotation marks and then the number of times to repeat that text.

To repeat “Excel” 10 times in a cell, you would use the following formula:

=REPT("Excel",10)

11 lesser known but useful Excel functions20To repeat the asterisk symbol 20 times, you would use the following formula:

=REPT("*",20)
11 lesser-known but useful Excel functions ( P2 )

11 lesser-known but useful Excel functions ( P2 )

We have covered many Excel functions at How-To Geek, attempting to guide you on using the most popular options. Hopefully, these lesser-known functions are what you need for mathematics, engineering, statistics, lookup, or text data.

2
31 Views
Simplifying Data Analysis: Using VLOOKUP and CHOOSE Functions TogetherPrevSimplifying Data Analysis: Using VLOOKUP and CHOOSE Functions TogetherJuly 15, 2023
7 Things You Didn't Know You Could Do with PowerPoint AnimationsJuly 17, 20237 Things You Didn't Know You Could Do with PowerPoint AnimationsNext

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts
  • Retrieve Data Based on Data Validation
    September 7, 2023
    Excel
  • How to Convert Multiple Rows to Columns and Rows in Excel?
    September 6, 2023
    Excel
  • VLOOKUP for Automatic Discount in Data Validation
    September 6, 2023
    Excel
  • How to Convert Text Dates to Dates in Excel?
    September 5, 2023
    Excel
Categories
  • Blog 80
  • Excel 754
  • Powerpoint 36
  • Software tricks/tips 128
  • Uncategorized 8
  • Word 109
Tags
the ISERROR
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
    Rated 5.00 out of 5
    $15.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
    Rated 5.00 out of 5
    $54.00
  • Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $11.00
Products
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account
    Rated 4.83 out of 5
    $49.00
  • Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $15.00
  • Windows Server 2022 Standard Key Global
    Rated 4.47 out of 5
    $15.00
  • Windows Server 2022 Remote Desktop Services Device Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $45.00

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.