IF(AND

Today I want to examine the often confusing If statement when using the Boolean AND.

=IF(AND

To the beginner Excel user, this can sometimes be confusing to understand.

Let’s look at a basic time sheet for a small company. All the employees get paid the same hourly rate of $10 an hour but not every employee is entitled to overtime pay (anyone who works over 40 hours). And just for simplicity of the formula demonstration (and cause the boss is super cool), if an employee works even 1 hour over 40, they get time and a half for all the hours worked for the week! I call this company Fantasy Land and the boss’s name is Richie Rich.

Employee	Eligible For Overtime	Hrs Worked	Pay Rate	Weekly Pay
Bill	Yes	45	10.00	675
Steve	No	42	10.00	420
Dave	No	38	10.00	380
Thomas	Yes	40	10.00	400
Nancy	Yes	52	10.00	780
Sue	Yes	52	10.00	780
Rebecca	Yes	38	10.00	380

If we look at the first employee, Bill is eligible for overtime and has worked 45 hours. So if we do the math, $45 X $15 (time and one half), he is owed $675 for the weeks work.

Steve on the other hand has also worked over 40 hours but is not eligible for overtime. So his pay is $42 X $10 = $420. It would be frustrating to have to manually calculate the weekly pay for every employee. Fortunately Excel allows you to factor multiple criteria with If statements that will help Richie Rich easily calculate his payroll expense.

Employee	Eligible For Overtime	Hrs Worked	Pay Rate	Weekly Pay	Formula in Column E
Bill	Yes	45	10.00	675	=IF(AND(B2="Yes",C2>40),(C2*(D2*1.5)),(C2*D2))
Steve	No	42	10.00	420	=IF(AND(B3="Yes",C3>40),(C3*(D3*1.5)),(C3*D3))
Dave	No	38	10.00	380	=IF(AND(B4="Yes",C4>40),(C4*(D4*1.5)),(C4*D4))
Thomas	Yes	40	10.00	400	=IF(AND(B5="Yes",C5>40),(C5*(D5*1.5)),(C5*D5))
Nancy	Yes	52	10.00	780	=IF(AND(B6="Yes",C6>40),(C6*(D6*1.5)),(C6*D6))
Sue	Yes	52	10.00	780	=IF(AND(B7="Yes",C7>40),(C7*(D7*1.5)),(C7*D7))
Rebecca	Yes	38	10.00	380	=IF(AND(B8="Yes",C8>40),(C8*(D8*1.5)),(C8*D8))

If we write out the logic it would look like this…

If an employee is eligible for overtime (YES) AND Hours worked > 40, multiply Hours worked X (Hourly Pay Rate X 1.5) ELSE multiply Hours Worked X Hourly Pay Rate.

The Boolean logic for writing this would be as follows…

=IF(AND(First Test),(Second Test) are both TRUE, Execute overtime pay calculation, Else Execute regular pay calculation)

So using the Boolean AND in our test for the If statement helps up easily join multiple evaluations to help us determine the correct answer.

Custom View

Bill, a manager in the ACME Sales Company reviews the same departmental sales report weekly, monthly and quarterly. He digs into the report differently depending if it’s the weekly, monthly or quarterly review. Additionally other managers in the ACME Sales Company also review the same report. As a result, Bill cannot save his custom formatting so every time Bill gets the report, he spends time setting it up in a way significant to his needs.

Well the good news for Bill is that Excel provides a way to create a custom views for a workbook that can be reused over and over again. He can setup a Custom View to save specific display settings (such as column widths, row heights, hidden rows / columns, filter settings and print settings (such as page settings, margins, headers / footers, and sheet settings). He can then reuse these custom views whenever he needs.
There is one limitation with custom views. If any worksheet in the workbook contains an Excel table, the Custom Views command will not be available for use anywhere in the workbook.

To create a custom view you start by opening the file. Next change the settings that you want to save in the view (i.e. add filters, hide columns, etc.)

In Excel 2007 select  View tab > Workbook Views group > Custom Views

Select Add

A name box will open. Click the add button and then type a name for the Custom View.  To help you in the future include the active sheet name to make the custom view easier to identify. In Bills case he might name his custom filter “Bills Quarterly Report”.

Under Include in view, select the options you want. In the above example both Print Settings and Hidden, rows, columns and filter settings are checked.

To use the filter Bill would do the following…

Excel 2007 View tab > Workbook Views group > Custom Views

A view box will appear. Bill then would select the name of the custom view he wants to apply. Then he would click the show button.

Bill can also add the custom view button on the quick access toolbar. 

Right mouse click on the menu bar > select customize the quick access toolbar > select view in the categories > drag and drop the customize view in the menu bar.

A warning about custom views. If you save a worksheet with a custom view applied, the next time you open the worksheet, that custom view will still be applied.

Right about now you are saying… Hey! Why not just record a macro to accomplish the same custom filter?” Well truth be told, you can. However using a customized view is another tool available for data analysis.

Creating a Combo Box to display a drop down pick list

You can use combo boxes to ensure continuity in formulas and user selections. This helps enhance the user experience and guide the user via standard options. Confused? Let me show you an example.

Let’s say you want to have the user select an item color so that you can return the sales for that item / color combination. Well you could just leave a cell blank and have the user type in the color. However they could type the color name incorrectly or perhaps select a color that is not valid.

I can guide the user by creating a combo box. In this example I have created a sample list of colors in column A.

	A
1	Colors
2	Red
3	Orange
4	Yellow
5	Green
6	Blue
7	Indigo
8	Violet

From the developer tab I select Insert then Form Control Combo Box (not to be confused with the Active X Combo Box). I then place the Combo Box in Cells C3 and D3. Since the box is free form I can make it as large or small as I wish.

Next I right mouse click on the Combo Box and choose Format Control. On the control tab, I set the input range to be A2 – A8. I want to put my Combo Box result in cell G2. I also display 7 lines since I have 7 options. If I had a large data set such as 50 States, I could set the display to 10 and a scroll bar would appear.

When done I can click on the combo box drop down arrow to display my colors.

The end result is that the number 4 is placed into Cell G2 (our cell link).

	A	B	C	D	E	F	G	H
1	Colors
2	Red						4
3	Orange
4	Yellow
5	Green
6	Blue
7	Indigo
8	Violet

But wait a minute. You are staying that I selected Green and you are correct. Why does cell G2 not have the word Green? Well the Combo Box will return the number of the selection. Green is the 4^th item in the list. If I selected Indigo, then cell G2 would show 6.

I can use the number returned in formulas or can use a Vlookup to return the Color based on the number returned.

So the next time you need to need to have a user enter in a value, consider using a Combo Box. 

Dashboard Noise

I recently purchased a 2000 Ford Focus. As with older vehicles this car comes equipped with a dashboard that rattles and hums. At a minimum this is annoying and at other times, darn distracting. Well this dashboard rattle and hum got me thinking about Excel (go figure) and Dashboard Noise(I am so enamored with the term that I am trade marking and copywriting it) Please pay me a penny every time you use the term J.

What’s Dashboard Noise? Well Dashboard Noise  would be superfluous data, overtly fancy graphics, poor layout and distracting colors / fonts.

A dashboard should be clearly defined, easy to understand and most importantly, capable of conveying the information in a straight forward manor.

Let’s face it, we all like to play and Excel is our playground. We are the king of dodge ball in the Excel playground. Look into a mirror and tell me that’s not true.

As developers we take great joy in creating dynamic transparent fuel chart graphics juxtaposed against scatter arrays that are based in dynamic regression. Then we raise our hands like grade school children and say… “Teacher! Look what I have done.”

The end user marvels at our genius design but never uses the dashboard since it does not provide them an easy way to get the data they are looking for. Instead the ask Earl from Accounting for the sales forecasting spreadsheet.

It’s easy to lose focus on the basics of the dashboard, and let’s face it. It does not matter how fancy and fantastic the dashboard looks if it does not provide an easy way for the user to identify the information they are looking for.

So the next time you are creating a dashboard, try keeping the noise to a minimum. You may find the old saying KISS “Keep It Simple Stupid” has merit.

I know it does for me.

Display formulas quickly

There are times when you want to view the formulas in your worksheet.

Pressing CTRL + ~ will quickly toggle formulas so you can troubleshoot them.

Sometimes the best tips are short ones.

Whats your best quick tip?