When using numbers in text strings you usually need to format them. You typically need to use the comma format and handle decimals. There is a function that can simplify this task.
Tag Archives: formula
Excel – How To Create a MINIF and a MAXIF
Without an array
Excel doesn’t have a MINIF or a MAXIF function and many advanced users create an array formula to provide that functionality. If you have Excel 2010 or later there is a non-array solution.
Generating Random Text Entries in Excel
Create random first and last name combinations
In my previous blog post I discussed generating random numbers in Excel. What about generating random text? E.g. generating random names for testing or training purposes.
Two Random Functions in Excel
They both create random numbers
Excel has had the RAND function for a long time. In Excel 2007 a new function was added. Called RANDBETWEEN it made it easier to create random numbers.
Excel Flowchart Technique
Excel has great charts to help you visual your numbers, but it can also allow you to use flowcharts to help visualise numbers in a different way and help explain relationships between numbers and how they are formulated.
Pivot Tables and Different Date Grouping
When you use date grouping (by months) in one Pivot Table report it affects other Pivot Table’s date reporting from the same data. There is a work around to allow you to have daily, monthly and quarterly Pivot Table reports.
Filtering Tricks in Excel
Excel’s Filtering feature (formally called AutoFilter) is quick and easy to use, but it doesn’t always provide enough solutions to your filtering needs.
Identifying if an entry is one of a group
You have a number of options that require the same treatment. What is the easiest way to identify if an entry is one of a list?
No comment, no way
As well as the normal instructions and notes, there is another way you can use a cell comment. Cell comments can be used to capture cell formulas.
Extracting the Column Letter in Excel
There can be times when identifying the current or a specific column letter reference is useful. Check out the comments for a shorter formula – thanks Batuque.
Status Bar Calculations in Excel
Quick and easy calculations
Excel has had an AutoCalculation feature for many versions. This means you can see the result of common functions without typing a single formula.
How to Identify Blank Cells in Excel
Find the technique you need
When developing formulas in Excel you often need to know when a cell is blank. There are different ways to check if a cell is blank, the one you chose will depend on what you are trying to achieve.
Excel Links and Deleted and Inserted Rows
How to avoid #REF! errors
When linking to another sheet, a deleted row can often cause a #REF! error. You can use the INDEX function to avoid these errors.
Excel and Financial Year Quarters
Australian solution
The Australian Financial Year runs from 1 July to 30 June. To determine which quarter a date falls into there are at least a couple of formulas that will do the trick.
Summing Coloured Cells in Excel
A SUBTOTAL trick worth learning
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.
Excel and days within the Financial Year
Australian solution
In Australia our financial year is from 1 July to 30 June. If you need to work with days elapsed or days remaining in the financial year you can use a few formulas to do the calculations for you.
Excel 3D SUMIF
You can achieve it
In a previous blog post I explained how to do a 3D SUM calculation in Excel that SUMs through sheets. You can view it here. I have seen a few posts in forums recently about performing a 3D SUMIF calculation. Whilst the SUM function can work with 3D ranges, the SUMIF function can’t.
Validation in Excel using the ABS function
Avoid issues with negatives
When doing validations in Excel you often need to check values against a tolerance figure, usually 1 cent or 1 dollar.
Excel Data and Formula Entry Tips
Speeding up data entry
There are number of shortcuts you can use to speed up your data and formula entry in Excel.
Number keypad
Use the number keypad on the right of the keyboard. This has all the numbers, as well as most of the formula operators (+ * – /), you need to create formulas. It also has a large Enter key. The numbers are laid out like a calculator and so are easy to use.
Handling Relative References in Excel [VIDEO]
When you copy a formula in Excel, any relative references (those without dollar signs) may change depending on where you paste the formula. If you would like to copy a formula and not have the relative references change you have two options.
Handling Text in Calculation Cells in Excel
If you need to do mathematical calculations on cells that may contain numbers or text, then you can use the IFERROR function to ignore the text.
Insert a Note in an Excel Formula
If you want to place a note inside an Excel formula you can use an old function that is, in most cases, redundant. The N function was used in early spreadsheets, but is hardly ever used in modern formula.
Excel Format as Table Part 2 [VIDEO]
The Format As Table feature has many useful features that are worth taking advantage of. The previous post listed them. The video of this blog is shown at the bottom of the post.
Excel – Last Used Row In A Column
SUMPRODUCT to the rescue again
Sometimes you need to identify the last used cell in a column. The versatile SUMPRODUCT function can calculate that using a couple of other functions.
Counting Characters in Excel
Another useful SUMPRODUCT technique
If you need to know how many characters are in a cell then use the LEN function. What if you wanted to know how many characters were in a range?
YTD Percentages in Excel
Getting percentages right
Calculating YTD percentages in Excel isn’t always straightforward, but in some cases you can use Excel’s most flexible function to achieve the result you need.
FORMULATEXT Function in Excel 2013
New function - great for training
The new FORMULATEXT function in Excel 2013 will make my Excel training job a little easier. It also has a formatting use.
Excel – Convert Text Month to the Month Number
A trick to get a number from a name
Ever needed to convert the text Sep or September into a 9? Well Excel can convert text months into their respective numbers.
Extract the Sheet Name in Excel
The long and the sort versions
Being able to extract the current sheet name is often handy. Sheet names have to be unique, so you can name your sheets after departments or divisions. Excel will stop you duplicating a sheet name.
Get the Right Day In Excel
Formulas and formats
To get the day of the week in a cell you have two choices. You can use a format or a formula.
