Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Worksheet name, dynamically using formula CELL
  • Count cells with condition – multiple conditions COUNTIFS
  • Get column name (columnname as A,B,C, etc) as input inside cell ADDRESS
  • When Excel tells you to go get some life Excel User tips
  • 0% interest promotion Basic Math
  • 6174 Kaprekar’s constant Basic Math
  • Spreadsheet Compare application Excel User tips
  • Excel @ before function names in formulas Excel User tips

Category: Formulas – combined functions

Two or more functions = One Formula, how to combine more than one function to present a powerful formula that does the job well

Looplist – Repeating months using formulas

Posted on December 11, 2018 By ANmar
Looplist – Repeating months using formulas

Using formulas to repeat list, I call it LoopList

Used to list Months for several years, in away that with 1 formula, it will loop through all months, then jumps into next year and loop through its months again, and so on

Read More “Looplist – Repeating months using formulas” »

Basic Math, Date and Time, Formulas - combined functions, MATCH, MONTH, OFFSET, ROW, YEAR

Scorring, saving multiple outputs in 1 number

Posted on December 10, 2018 By ANmar
Scorring, saving multiple outputs in 1 number

I have seen this practice before several locations

Returning multiple outputs in 1 single number.

for example

Read More “Scorring, saving multiple outputs in 1 number” »

Basic Math, Conditional Formatting, Formulas - combined functions, Logical, Non-functions, Standard functions

Compare2, a tool to compare two sheets using formulas

Posted on December 5, 2018 By ANmar
Compare2, a tool to compare two sheets using formulas

Tool will compare between two sheets using formula/functions method. Once you type in full folder locations (for both files), file names and sheets names in file1 and file2. Make sure these two files are open, then sheet will be updated with comparison results, showing that every cell is checking for its equivalent addresses from both…

Read More “Compare2, a tool to compare two sheets using formulas” »

Conditional Formatting, Formulas - combined functions, IF, Logical, Standard functions

Skills Grid

Posted on October 26, 2018 By ANmar
Skills Grid

A small Excel file to show how we can create a chart-like Excel sheet

Used in my Resume to show different skill sets and the level of expertise in each

Read More “Skills Grid” »

Conditional Formatting, Data Validation, Formulas - combined functions, IF, IFERROR, Logical, Lookup and References, Names, Non-functions, OFFSET, Standard functions, VLOOKUP, Worksheet

IFS (and IFNA) to avoid nested IFs

Posted on March 25, 2018 By ANmar
IFS (and IFNA) to avoid nested IFs

I tend to see more and more the usage of nested IF functions recently

Now, do not get me wrong, IF is great, but come on, are you going to use it for more than 2 conditions? seriously?

Excel 2013 comes with IFS, the perfect alternative to nested IFs

As you can see, takes up to 127 conditions, neat, right?

Read More “IFS (and IFNA) to avoid nested IFs” »

Formulas - combined functions, IFNA, IFS, Logical, Standard functions

Limited Ceiling

Posted on March 13, 2018 By ANmar
Limited Ceiling

The function CEILING (CEILING.Math as of Excel 2016) is a very good powerful function.

It allows you to round  number to any value up

Unlike ROUND, ROUNDUP and ROUNDDOWN, that are limited to number of decimal places.

CEILING can round to any number, decimal or not.

Below is an example of rounding the number in D2 which is 22 into the next 7 up, 7 is in D3, result is 28 as you can see

Read More “Limited Ceiling” »

Basic Math, CEILING, Formulas - combined functions, INT, Math and Trig, Standard functions

Count cells with condition – multiple conditions

Posted on February 16, 2018 By ANmar
Count cells with condition – multiple conditions

We did see who we can get total of a column for a certain month in certain year using SUMIFS.

SUMIFS got a sister function, COUNTIFS

Which will count how many times cells found with more than 1 condition

So, when you have a table like this…

Read More “Count cells with condition – multiple conditions” »

COUNTIFS, Formulas - combined functions, Math and Trig, Statistical, SUMIFS

IF with AND and IF with OR

Posted on December 25, 2017 By ANmar
IF with AND and IF with OR

The powerful function If can already do a lot of tricks, but we can for sure do more when we use it with AND or OR functions.

Also, once we understand that, we can use the power of combining AND and OR inside the Logical test of IF, to make it even smarter

Once good example is when we need to see if a certain date is within the same month as this month like below…

Read More “IF with AND and IF with OR” »

AND, Formulas - combined functions, IF, Logical, OR, Standard functions

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

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

Posts pagination

Previous 1 2 3 4 … 6 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
  • Middle name + 1st name Basic Math
  • Text-to-Columns, dynamically using formulas IFERROR
  • Excel Limits Excel User tips
  • IF with AND and IF with OR AND
  • Return 12 values in one number AND
  • CamelCase to Camel Case CHAR
  • Insert Blank rows into table using functions CHAR
  • Schedule column Basic Math

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme