Posts Tagged ‘MS Excel’
How to Aggregate MS Excel Daily Data with Pivot Table
Introduction If you have daily data that still makes sense when aggregated into weekly or monthly data, then you can accomplish that very easily in Microsoft Excel, thanks to the pivot table. You can apply this method to, for example, a data of “trucks arrived” or “shirts bought” per day since the total aggregate amounts…
Read More
How to Calculate Running Average in Excel
Introduction Knowing how to calculate running average in Excel can save you a lot of time and energy. Sample Data Imagine your Microsoft Excel data consists of “Days of Accidents”. For each day on this list there has been one accident: As you can see, not all days are listed. There have been no accidents…
Read MoreISNA Function for "Not Available" Excel Data
Data not available creates ugly error messages when we use functions in MS Excel tables. For example, in the below table:
Read MoreHow to Use SUMIF Conditional SUM Function in Excel
Microsoft Excel SUM Functions: SUMSQ – Square and Add Cell Values SUMPRODUCT – Multiply and Add Two Arrays SUMIF – Add with a Condition SUMIFS – Add with Multiple Conditions SUMX2PY2 – Add Sums of Squares of Two Arrays SMX2MY2 – Add Differences of Squares of Two Arrays SUMIF(), Conditional Sum, is a wonderful function that allows you to find the sum…
Read MoreHow to Line Up Numbers on Decimal Points in Excel
© Ugur Akinci Did you have a column of numbers in MS Excel that looked ugly like this: You can have a really nice looking column of numbers by centering them on the DECIMAL POINT — even if some of them do not have a decimal point. The first thing to do is to define…
Read MoreHow to Extract Text or Numbers from Character Delimited Cell Content in MS Excel
© Ugur Akinci Character-delimited data is fairly common. Here is an example: 100&4657&23&495&103 or Apple$Pear$Orange$Cherry$Grapes Imagine having a MS Excel table with cells populated with such content. How would you separate the text or the numeric values and post them under their own separate columns? Here is an example of some imaginary data about SEASONAL…
Read MoreHow to Use Excel NESTED IF Function
To reward the customer with quantity discounts for bulk purchases is a common practice in business. Consider the below situation: There are FOUR different IF statements here illustrated by the above table: (1) Here the seller is charging $10 per item if the customer purchases 100 or less items. (2) If the customer buys more…
Read MoreHow to Calculate Average Number of Items Sold and Average Revenue Generated by a Salesperson in a Transaction by Using MS Excel’s AVERAGEIF Function
How to Calculate Average Number of Items Sold is an important task to perform in many MS Excel projects. Imagine you have the following sales data: How can we find the average number of items sold by a salesperson in an average sales transaction? The formula is: AVERAGEIF = ([RANGE_NAME],”Name”,[AVERAGE_RANGE]) This formula will find the…
Read MoreHow to Color a Continuum of Values in a MS Excel Data Range
© Ugur Akinci Imagine you have a continuum of values in a MS Excel range, like the student grades in a class. Here is how you can color each cell automatically so that at one look you can tell which grades are HOT and which are not. (1) Select the data (cell) range. In this…
Read MoreMS Excel Short Cut for Changing the width of an Excel column
Changing the width of an Excel column to accommodate all text in a single line is something I do frequently while working with MS Excel worksheets. After a while it gets tiresome to select Format > AutoFit Column Width from the ribbon all the time. So here is a wonderful Windows short cut that will…
Read More