Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • MID + SEARCH to convert cell to rows MID
  • Comments versus Notes Excel User tips
  • Fixed length ID Formulas - combined functions
  • Standard math to calculate Aspect Ratio Basic Math
  • Excel keeps converting all cells to “Date” (or Time) Excel User tips
  • Recover the unrecoverable Excel User tips
  • Week number to Sunday date – Weeknum reverse DATE
  • Get monthly total from table with dates DATE

Category: Standard functions

Standard functions that comes with Excel, plain single functions

New function IFERROR, finally

Posted on December 24, 2017 By ANmar
New function IFERROR, finally

We have already gotten used to use VLOOKUP, MATCH and other functions that generates errors inside an IF

But, we do not need that anymore

We can use the new guy, IFERROR instead, which will show the result if it did not generate error, otherwise will show something else if it resulted an error.

Read More “New function IFERROR, finally” »

IFERROR, VLOOKUP

OFFSET+MATCH

Posted on June 9, 2017 By ANmar
OFFSET+MATCH

One of the power of combining functions in one cell is using the best of these functions

An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP.

Why do we want to do that?

VLOOKUP will search in the left-most column in a table to get a value from a column on the right. But this is not the situation all the time

We needed several times to locate a row by using a column on the right, then get the value from a column left to it.

This is when VLOOKUP can not help us.

Read More “OFFSET+MATCH” »

Formulas - combined functions, Lookup and References, MATCH, OFFSET, Standard functions

Count how many times letter found

Posted on March 3, 2017 By ANmar
Count how many times letter found

Found this in an old file while doing some digital cleaning This will show you how you can easily count how many times a letter (or any string) into a cell. So the main formula doing that is … =LEN(I12)-LEN(SUBSTITUTE(I12,”o”,””)) Of course, this assumes you are looking to find how many the letter “O” is…

Read More “Count how many times letter found” »

Formulas - combined functions, Len, Standard functions, SUBSTITUTE, Texts and Strings

Calculate hours between two cells

Posted on February 27, 2017 By ANmar
Calculate hours between two cells

We needed that several times in the past Then I found it in an old file in my laptop, thought to share it for others to help So, when A1 has the start time, B1 has the end time Time different in one day will be from formula =IF(OR(A1=””,B1=””),””,ABS((HOUR(A1)+MINUTE(A1)/60)-(HOUR(B1)+MINUTE(B1)/60))&” hrs”)   I added to prevent…

Read More “Calculate hours between two cells” »

ABS, Basic Math, Date and Time, Hour, IF, Logical, OR, Standard functions

MATCH fumction, just a simple search

Posted on February 20, 2017 By ANmar
MATCH fumction, just a simple search

To do a search in a column or a row, MATCH is the function to look for It is very simple, you set the value to look for and where to look, then of course there is some another option to set how to do the search. So, we are looking for 87 in column…

Read More “MATCH fumction, just a simple search” »

Lookup and References, MATCH, Standard functions

Multiple lines in cell using functions

Posted on December 2, 2016 By ANmar
Multiple lines in cell using functions

You must already know that when you press Enter after you modify a cell, the cell content will be saved and you jump down to cell below (we can change that behavior by the way, but that is another post).

And you could have already know that if you want to have multiple lines inside a cell, you need to press ALT+ENTER to do that.

But, how can we do that in formula ??

 

I got a request on how to do that in a spreadsheet, so, the client wanted to convert two columns having two parts of address, Column C has Street address and street name, and column D has city, state and Zip code.

post1502-1

Read More “Multiple lines in cell using functions” »

CHAR, CONCATINATE, Formulas - combined functions, Standard functions, SUBSTITUTE, Texts and Strings

Links between two workbooks – dynamically using functions

Posted on September 26, 2016 By ANmar
Links between two workbooks – dynamically using functions

I don’t believe I don’t have this here yet. This is one of my oldest tricks

When we have to do links between two workbooks (One cell in Workbook1 to have value updated from a cell in Workbook2), we usually just do links like this…

Go to workbook1, select a cell, type in ‘=’ then go to Workbook2 and select a cell, then ‘ENTER’

To get something like this

link1

Which will look like this if try1.xlsx is closed

link2

However, if you open try2.xlsx alone before you open try1.xlsx, you get this message

Read More “Links between two workbooks – dynamically using functions” »

INDIRECT, Lookup and References, Standard functions

VLookup

Posted on September 26, 2016 By ANmar
VLookup

I have been asked for a while now to just put a simple VLookup function, here it is, I know this might be stupid to post it here, but we need to fill requests from all types of visitors.

Vlookup is the most famous function in Excel, some people actually refer to Excel as VLookup.
Even though I do not like that fact, especially when we have other powerful functions like INDEX, OFFSET, INDIRECT and others.

So, what is VLOOKUP?

VLookup is going to search for an item in the left column of your table (or selection), as in the screenshot below, then bring me back the value in the same row where it find that item from another column.

 

Post1499

The Green box (Lookup_Value) is what the function will search for, and the vertical green box is where, this is because we selected the range (Table_Array) in Blue as the area of D:G, so VLookup will search in D, this is the V part in VLOOKUP, we should also expect to see HLookup.
Means if item is not found in column D, VLookup will return as #N/A error

Read More “VLookup” »

Lookup and References, Standard functions, VLOOKUP

CONVERT function

Posted on September 26, 2016 By ANmar
CONVERT function

Excel 2010 has a lot of new exciting things, one of them is the ‘Engineering’ category of functions, this was usually a plugin we have to add to get, but in Excel 2010, it is already there.

The function ‘Convert’ does what you expect it, converts a number from one metric to another.

Of course, we cannot remember all metric systems and units, so just press F1, or click help to see this list to know how you can convert from one unit to another in any of the systems inside that function

The one I most interested is

=Convert( 45, "mi", "km")

convert5

More images …

Read More “CONVERT function” »

CONVERT, Engineering, Standard functions

Workdays – Across months

Posted on November 3, 2015 By ANmar
Workdays – Across months

Get total number of days per month
This was a question from one of my friends, This set of formulas will calculate how many days (Networkdays) in each of the months in the list for a given Start and End dates

If Range of dates are across multiple years, user can copy columns C, D, E and F to fill those years

The months for the list are automatically populated starting from Jan of the year of the start date

The main formula in F7 should be:

Read More “Workdays – Across months” »

DATE, Date and Time, Formulas - combined functions, IF, MONTH, NETWORKDAYS, Standard functions, YEAR

Extract current (Active) workbook name

Posted on September 14, 2015 By ANmar
Extract current (Active) workbook name

I need that more often than I thought It is basically extract the full workbook name that we are in, with no folders, and with no extension =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-6) So, here it is….   And if we want to go 1 step further, I often have the version of the tool as part of the filename,…

Read More “Extract current (Active) workbook name” »

CELL, Formulas - combined functions, Information, Lookup and References, MID, SEARCH, Standard functions, Texts and Strings

Posts pagination

Previous 1 … 4 5 6 … 8 Next

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Lesson learned: Do not trust Google Sheets function Google Sheets
  • Fx to cut long column into two columns Formulas - combined functions
  • Validation based on another Validation COUNTA
  • Cells with additional spaces AND
  • LastSunday, LastSaturday Date and Time
  • Fixed length ID Formulas - combined functions
  • 0% interest promotion Basic Math
  • Shapes with dynamic output Excel User tips

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme