© Ugur Akinci Imagine your 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 on the days that are not on this list. If a day is listed more than once it’s because […]

## Posts in category MS Excel

## How to Convert Non Editable PDFs into Excel Sheets

By Paulina Gibson Special for TCC As PDF is the dominant format for corporate document sharing, it is no wonder there is a wide selection of complimentary tools that can help you get more done when working with this file format. Even though Adobe also has its set of additional tools and extensions, at times […]

## How To Calculate the Number Of Workdays Between Two Dates Using NETWORKDAYS i...

By BJ Johnston Is is easy in Excel to calculate the number of working days between two dates. Most, but not all businesses, operations or project activities and progress happen during weekdays. So, if you need to report and calculate the number of days that have elapsed between a start date and an end date […]

## How to Use the EXACT Function To Force Upper Case Data Entry Into MS Excel

By BJ Johnston One of the most important factors in good data analysis is clean data. Dirty data can make even simple analysis tasks become much more complicated than they need be, or even give incorrect trends in data. Ensuring that data is entered correctly is best carried out by enforcing data validation and essentially […]

## How to Fix a Microsoft Excel 2010 Crash

By A. Honey Office Excel 2010 is a very popular and useful program came with Office 2010 suite. It allows its users to create spreadsheets and do much more things than its earlier versions. However, it has been noted that Excel 2010 crashes while exiting the program, and on runtime period as well. Some users […]

## How to Batch Delete Sensitive Metadata from a Set of MS Word, Excel, PowerPoi...

© Ugur Akinci MS Office applications can and do store metadata in files created by such applications as MS Word, Excel, and PowerPoint. The metadata is sometimes useful if you’d like to record the author’s name, the date of creation, etc. with the file. But in other times, especially when you are creating legal documents, […]

## Use ISNA Function to Handle “Not Available” Data in a MS Excel Ta...

© Ugur Akinci Data not available creates ugly error messages when we use functions in MS Excel tables. For example, in the below table: #N/A means the functions embedded into Bonus cells cannot find what they need to find to calculate the value appropriately. You can publish any numeric or text value you like in such […]

## How to Use SUMIF Conditional SUM Function in MS Excel 2010

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 © Ugur Akinci SUMIF(), Conditional Sum, is a wonderful function that allows you to find […]

## How to Extract Text or Numbers from Character Delimited Cell Content in MS Ex...

© 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 […]

## How to Calculate Quantity Discount Price for Bulk Purchases with MS Excel NES...

© Ugur Akinci 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 […]

## How to Calculate Average Number of Items Sold and Average Revenue Generated b...

© Ugur Akinci 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 average of the second range for every “name” mentioned in the first range. More specifically: =AVERAGEIF(B2:B20,”Mary”,E2:E20) […]

## MS Excel Short Cut for AutoFit Column Width

© Ugur Akinci Changing the width of a 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 […]

## How to Find the Workday a Specific Number of Days Before of After a “Start Da...

© Ugur Akinci Let’s say you have a reference START DATE like “February 2, 2005″. You would like to calculate the exact WORKDAY 100 days BEFORE or AFTER that date. How would you do it? What’s more, how would you do it if the “weekend” definition varied, like “Sunday only” or “Saturday and Sunday”, or […]

## How to Calculate Optimal Product Mix with Minimum Production Quotas while Ach...

© Ugur Akinci Prerequisite This exercise build directly on the following MS Excel 2007 and 2010 exercises: /how-to-calculate-optimal-product-mix-with-ms-excel-2007-solver/ /how-to-calculate-optimal-product-mix-with-ms-excel-2010-solver-to-maximize-profits/ You have start from one of these previous posts to make sense of this one. Minimum Production Quotas In this exercise, there is a new condition, a new CONSTRAINT on maximizing profits: The plant manager wants […]

## How to Calculate Optimal Product Mix with MS Excel 2010 “Solver” to Maximize ...

© Ugur Akinci Imagine you’re running a manufacturing plant, producing hats, shirts, gloves, and other similar items. You know the following: The number of each item you are now manufacturing. The number of labor units you need to produce each item. The number of raw material units you need to produce each item. The unit […]

## How to Calculate Optimal Product Mix with MS Excel 2007 “Solver” to Maximize ...

© Ugur Akinci Imagine you’re running a manufacturing plant, producing hats, shirts, gloves, and other similar items. You know the following: The number of each item you are now manufacturing. The number of labor units you need to produce each item. The number of raw material units you need to produce each item. The unit […]

## How to Change the SUM of a Range Dynamically in MS Excel

© Ugur Akinci Let’s say you have a data table like this: PROBLEM: We want to create a SUM for the GROSS column that would adjust itself dynamically when new sales records are added to the table. How would you do that? SOLUTION: Easy. STEP 1) NAME the range dynamically by using the OFFSET function. […]

## Correcting Capitalization Errors in MS Excel — How to Retype a List of Names ...

© Ugur Akinci Let’s say you have a list of hundreds of names typed with all kinds of capitalization errors: PROBLEM: How can you correct the capitalization errors in every name and make sure each first and last names begins with a CAPITAL letter, followed by lower-case letters? SOLUTION: It’s very easy. Just select a […]