=IF is a Tremendous Time Saver

If ever you had a doubt whether Microsoft Excel’s =IF formula can save you time, here’s my favorite =IF formula story.

Learn =IF, It Can Save You Hours of Work Each Week

Be patient, read this entire article.  You may save yourself hours of work each week.

 

(=IF is a featured chapter in The World’s Shortest Excel Book, available for purchase on this website.)

 

One =IF Formula Saved me 6 Weeks of Work

In the 1990’s I worked as an Excel temp for a long distance phone company in downtown Chicago (perhaps they were MCI, I can’t remember).

On my first day on the 6 week job in November, the manager placed a paper report one foot high on my desk and gave me this verbal assignment.

Our company doesn’t use phone lines, we rent them.  You need to go through each line of this report to make sure that each phone company we use hasn’t overcharged us for the use of their phone lines.

Basically, I had to check about three different columns for each line of data (thousands of lines of data or charges equal to a one foot high paper print out).

The manager spent about 30 to 45 minutes explaining the entire problem to me and in my mind my task sure sounded like an =IF problem.  The manager asked if I had any questions.  I said.

Can I have the spreadsheet that generated the report?

The manager asked me why I needed that and I honestly said I thought it would help me do the assignment faster.  I chose not to tell him about my =IF theory.

At about 9:30 AM I took the floppy diskette (they had no network, there was no cloud computing, no jump drives) inserted it into my old personal computer at work, launched Lotus 1-2-3, and then loaded the spreadsheet.  And then I waited about 30 seconds or more for the spreadsheet to open.  I was holding my breath to see if the old computer they gave me would work.

135 Character =IF Formula Saves Six Weeks of Work

I wish I had saved that formula from 20 years ago.  All I remember is that it was 135 characters long.  And I was nervous as heck working on it and here’s why I was nervous.

First Day of my Temp Project

So by 9:30 AM the manager gave me my Excel assignment walked me away and I was all alone surrounded by strangers working in cubicles.  I was supposed to be sifting through my one foot high report looking for overcharges to the company.

I looked through the first line of data trying to understand the project better.  Remember, look at 3 columns in a row, perform some math on those numbers, and make sure that number wasn’t greater than the “correct” estimated charge for the service.

10 AM on the First Day

The first row of data took about 15 minutes to do.  The second row took about 10 minutes.  The third row 5 minutes.  Then five minutes per row each time.  It took about 30 minutes to get accustomed to this tedious task.  It was now about 10 AM.

10:30 AM on the First Day

Than I began building my =IF test.  It had to be perfect.

Do you realize =IF formulas can be used to generate simple English answers?  For example, let’s say you analyze sales numbers to see if a salesmen grossed over $11,000 dollars in a month.  If they sold more than $11,000 in a month they get a “Bonus”.  See below.

The =IF problem I was working on as a spreadsheet temp worker in the early 1990’s was much more difficult than the one shown above.

Is it possible nobody at this company realized they could do this entire project with one powerful =IF test?  Will it work?

For the next 90 minutes I worked the problem.  My first =IF worked.  Then I tried it on maybe 20 rows of data and it had problems.  I received ERR messages (formula doesn’t make sense).  I changed the formula and tried the same 20 rows of data.  The formula was working.

I spot checked all 20 rows of data, everything worked.

Noon on the First Day – Lunch

I knew that my 135 character =IF formula had worked on 20 rows of data.  Could it work on thousands of rows of data with one formula.  Could Lotus 1-2-3 and the old computer they gave me be able to handle this =IF test.  (In the early 1990’s, our personal computers were much slower and had memory limits.)

I wanted to take my lunch and think about my =IF test before running it at 1 PM.

1 PM on the First Day

When I came back from lunch it was the moment of truth.

I copied my working formula and wanted to paste it to thousands of rows of data.  It was perhaps 10,000 rows of data.

I was cautious because I knew my computer was slow and Lotus 1-2-3 might not be able to handle my request because of memory problems.

I pasted my formula into 100 cells.  It worked but it took about 10 seconds.

I pasted my formula into 1000 cells and watched as Lotus 1-2-3 flashed its “WAIT” message in one of the corners of the screen.  It took a minute or so for the paste to complete.

I pasted the formula into several thousand cells.  This took several minutes.

I was cautious and just pasted the cell repetitively for just a few thousand rows at a time.  My old personal computer and Lotus 1-2-3 were working.

I checked my data, the formulas were working.  I spent an hour spot checking data to be sure.

2:30 PM on the First Day

Throughout all of this I was saving my file.  I made sure my floppy diskette (or was it the 3 1/2 inch diskette?) had enough room to save the entire file.

I performed my final File – Save and then considered my options.

1. Give it to the manager and tell him the 6 week project was finished.  I did this at the risk of the manager saying “thank you” and my project would be over.  My 6 week project would be over and I would be paid for one day of work.
2. Not give it to the manager.  They had asked me to go through the one foot pile of reports and I could do that instead.

3 PM – The Project is Done

At 3 PM I walked to the manager, gave him my diskette, and told him the project was done.

He was astonished.  Luckily he was open minded.

How did you finish the project?

It took me about 45 minutes to prove to the manager that each row of data had been processed correctly.  I explained the =IF test to him but I don’t know if he understood the formula.

His 6 week project full of potential human errors had been done perfectly in one business day.

My Reward?

I was nervous.  The manager could have thanked me and “let me go” instead of keeping me employed.

At about 4 PM he started introducing me to staff and this is what he said.

This is Richard.  He’s a spreadsheet consultant working on various projects for us this holiday season.  If you have any Lotus 1-2-3 problem just ask him for help.

That’s how I spent one November/December during the 1990’s.

Save Time and Learn Microsoft Excel’s =IF Formula

What can you use =IF for?

1. Examine the monthly budget items for a non-profit to see if they’re spending too much on a line item.
2. Determine who has worked overtime this week among hundreds of employees.
3. Determine how many of your patients have just turned 50 years old and need certain checkups.
4. Estimate timely re-ordering of critical supplies when they drop below a certain supply level.

I could go on with this indefinitely.  You need to understand and use =IF in your spreadsheets.  It will save you time and help you make better decisions for your company or organization.

Purchase The World’s Shortest Excel Book today, learn how to use =IF, and have some fun.

The World’s Shortest Excel Book costs $19.95 (U.S.) and I guarantee it will make you a better Excel user.  It comes with a 60 money back guarantee.

 

 

Thanks for visiting Your Excel Coach today and learning more about Microsoft Excel’s =IF formula.