Formulas

All about formulas and functions

The TRIM Function in Excel and VBA

Excel’s TRIM Function is a great way to remove extra spaces before, after, and within text in a spreadsheet. However, if you think the identically named VBA function does the same thing you’d be sadly mistaken, as I was once upon a time. Here’s a normal sentence. Here’s a sentence with extra spaces – two …

Read more

VLOOKUP Function in Excel: The Essential Guide

Introduction VLOOKUP, standing for ‘Vertical Lookup,’ is a powerhouse in Microsoft Excel’s suite of functions. At its core, VLOOKUP is designed to search for specific data located in a column and return a value from a different column in the same row. This functionality is paramount for data analysis, allowing users to efficiently sift through …

Read more

Logical Operations in Excel

Every once in a while you get fixated on something and the only way to let it go is to share it with others. So …. while reviewing the logical IF function and nested IF statements, I came up with a chart or table, that sort of evolved out of curiosity. It started by looking …

Read more

Excel ISNUMBER or IsNumeric Function

I don’t like it when Excel worksheet functions are different from their VBA counterparts. Makes for some aggravation. For example, I have a user generated data range that’s supposed to be made up of either numbers or empty cells. However, Excel users sometimes bump their keyboards inadvertently (when you hit the space bar) and things …

Read more

Calculate the Xth Weekday of Any Month in Excel

I’ve a simple formula for calculating the Xth Weekday of ANY month. It takes four inputs: Year, Month, Week, and Day. And requires a couple of lookup tables for data validation, one with special formatting. The History It all started when my wife mentioned there were a couple of meetings she had to schedule at …

Read more

Convert Seconds with the TIME Function in Excel

I often download an iTunes playlist just to see the time increments for each song, but iTunes only gives you the number of seconds for each song duration. In a previous post I spent a great deal of time showing how to use a combination of various formulas to convert these seconds, when it’s really …

Read more

3 Proven Formulas to Calculate Letter Grades in Excel

In the educational landscape, accurately calculating and managing student grades is a task of paramount importance. It not only reflects a student’s performance but also guides future teaching strategies. This article is designed to guide educators and academic professionals through the process of efficiently calculating student grades in Excel.

Read more

Nested IF Functions in Excel

The simplest Nested IF Function is using one IF Function inside another. When you have more than a few choices, nesting more IF Functions can quickly get complicated and, quite frankly, there are better ways to make decisions with Excel. Having said that, I have a simple method to account for the different choices that …

Read more