Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Sorting with functions only CHOOSE
  • Skills Grid Conditional Formatting
  • Hyperlink usage and HyperlinkOf UDF CELL
  • Week number to Month number DATE
  • Count Unique COUNTIF
  • Excel @ before function names in formulas Excel User tips
  • DayName Date and Time
  • Change Calculator – calculate change in multiple bills/coins Formulas - combined functions

Category: Standard functions

Standard functions that comes with Excel, plain single functions

Week number to Month number

Posted on May 11, 2015 By ANmar No Comments on Week number to Month number
Week number to Month number

Now we need to convert week number to month number

This is exactly the same as week number to date (reverse weeknum)

So, when the week number is in A5, then formula below will get you the month number

Read More “Week number to Month number” »

DATE, Date and Time, Formulas - combined functions, IF, Logical, MONTH, TODAY, WEEKDAY, YEAR

Why I ran from =INDIRECT() function

Posted on April 6, 2015 By ANmar No Comments on Why I ran from =INDIRECT() function
Why I ran from =INDIRECT() function

I think if there is a single function that would be a huge threat to VBA and macros from user perspective, it would be INDIRECT()

Yes, it wasn’t long enough before I got introduced to OFFSET() back in 1998, and was using it a lot and in my classes, yet once I found INDIRECT, things changed

Along with ADDRESS, ROW and COLUMN and some text tricks like SEARCH, LEFT, LEN, RIGHT, etc, you can do a lot.

You see INDIRECT does a simple job

Converts a text that represent a reference, into reference

So…

If you do

=INDIRECT("G4")

It gives you the value in G4

Notice that “G4” has the double-quotations, means

=INDIRECT("G"&SUM(S1:S4))

Read More “Why I ran from =INDIRECT() function” »

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

Get column name (columnname as A,B,C, etc) as input inside cell

Posted on April 6, 2015 By ANmar
Get column name (columnname as A,B,C, etc) as input inside cell

Excel already has the formula ROW()
=Row()
Which as we may already know will give us the number of the row we are in, if you add no parameters

4-6-2015 9-25-15 AM

However, the COLUMN() is kind of tricky

Because of the fact that cell addresses (or references) are based on the old style of R1C1

=Column()

Read More “Get column name (columnname as A,B,C, etc) as input inside cell” »

ADDRESS, CHAR, COLUMN, Formulas - combined functions, LEFT, Lookup and References, ROW, SEARCH, Standard functions, Texts and Strings

Week number to Sunday date – Weeknum reverse

Posted on June 17, 2014 By ANmar No Comments on Week number to Sunday date – Weeknum reverse
Week number to Sunday date – Weeknum reverse

This was a request from one of my work managers
She wanted to convert a week number she got in a column into thier dates

Excel already have the WEEKNUM that converts a date into its week number, but the reverse is what needed here

So after my mouth was drooling while she was asking her question, I started a head, and below is the result formula

You just need to place the week number in cell B3, then paste this formula anywhere:

Read More “Week number to Sunday date – Weeknum reverse” »

DATE, Date and Time, Formulas - combined functions, Standard functions, TODAY, WEEKDAY, WEEKNUM, YEAR

Fx to cut long column into two columns

Posted on March 19, 2012 By ANmar No Comments on Fx to cut long column into two columns
Fx to cut long column into two columns

Convert one long column into two
This set of formulas will get the items after row 45 (for example) from column A into the column B starting from B1 to have two 45-rows next to each other instead of one 90-rows column

This was the answer for this question
Yahoo! Answers question http://answers.yahoo.com/question/index?qid=20081225182043AAc7ZEB

Show two columns in one page as four columns using functions only. This will help you to show the next x number of rows for a certain column to the next of the first one to reduce the number of pages to print.

Read More “Fx to cut long column into two columns” »

Formulas - combined functions, INT, Lookup and References, OFFSET, ROW, Standard functions

iframe in Excel (XLiFrame)

Posted on February 19, 2012 By ANmar No Comments on iframe in Excel (XLiFrame)
iframe in Excel (XLiFrame)

This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too.

Formulas used to bring actual data is:

=IF(OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN())="","",OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN()))

While formula used to bring header labels:

Read More “iframe in Excel (XLiFrame)” »

ADDRESS, COLUMN, Formulas - combined functions, IF, INDIRECT, LEFT, Lookup and References, OFFSET, Standard functions, Texts and Strings

State Abbr with Index+Match+Validation

Posted on February 14, 2012 By ANmar No Comments on State Abbr with Index+Match+Validation
State Abbr with Index+Match+Validation

Small file that will show how to do INDEX + MATCH formulas to get the abbreviation of the state based on its name or the name of the state based on its abbreviation

Main formula to do that is:

=INDEX(Abbr_States,MATCH(B4,States,0),1)

Read More “State Abbr with Index+Match+Validation” »

Data Validation, Formulas - combined functions, INDEX, Lookup and References, MATCH, Standard functions

Zipcode-State search

Posted on February 12, 2012 By ANmar No Comments on Zipcode-State search
Zipcode-State search

Enter a zip code, and Excel will tell you in which state it is
It also has a list of all zip codes for all states and a technique (INDEX and MATCH functions) to get the state from a given zip code.
Simple file to help learning INDEX and MATCH

The formula used is:
 =INDEX(Data!$D:$D,MATCH(TEXT(Search!C4,"00000"),Data!$C:$C,0),1)

Read More “Zipcode-State search” »

Formulas - combined functions, INDEX, Lookup and References, MATCH, Standard functions, TEXT, Texts and Strings

Extract Address into table

Posted on February 10, 2012 By ANmar No Comments on Extract Address into table
Extract Address into table

Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table

Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above

Which is usually what you got from any list of addresses online, and you want to convert it to table.

This is exactly what one of clients had and needed, so here is the file that does that.

Basically you need to have 5 columns with formula in column G as:

=OFFSET($B$1,(ROW()-5)*4+4,0)

to extract first line (Starting from cell B5

Then in column H as:

Read More “Extract Address into table” »

Formulas - combined functions, LEFT, Lookup and References, MID, OFFSET, ROW, SEARCH, Standard functions, Texts and Strings

Convert 2-column into wide (column-row) table

Posted on February 9, 2012 By ANmar No Comments on Convert 2-column into wide (column-row) table
Convert 2-column into wide (column-row) table

This Excel file will convert a table of two columns into a Column-Row table
Using functions only and auto-updated once the Main table update

In another word, analyze the table into wider form

I needed this one few days ago and you will need this one too.

The main formula that you need to use is:

Read More “Convert 2-column into wide (column-row) table” »

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

Multiple Dual-Validations

Posted on February 9, 2012 By ANmar No Comments on Multiple Dual-Validations
Multiple Dual-Validations

It is to demonstrate how to do multiple validations in the same sheet, or as we want to call it

Mutliple-dual-validations

that depend on each other.

Checking out sheet “Data”, you can tell that Validations on column B is for the heads (Main categories), and on column D is for the sub category of the main that used in column B in that row.

Creating something like this basically falls in three parts

Part1: is the Names you need to define, so after we create the table found “Data” sheet …..

We need to set two names, one for the left-top cell for that table (used as reference) and another name with formula:

Read More “Multiple Dual-Validations” »

COUNTA, Data Validation, Formulas - combined functions, Lookup and References, MATCH, Names, OFFSET, Standard functions

Posts pagination

Previous 1 … 5 6 7 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
  • Excel ad from early 90s Non-functions
  • Recover the unrecoverable Excel User tips
  • Repeated Offset Basic Math
  • Limited Ceiling Basic Math
  • Convert cell into textbox Excel User tips
  • Edit directly in cell Excel User tips
  • Filter. Google Sheets function Google Sheets
  • iframe in Excel (XLiFrame) ADDRESS

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme