How do I use the IF formula in Excel?

IF Definition:  IF(CONDITION, action or value if CONDITION is true, action or value if CONDITION is false)  “If” Formula can work for anything that is based off of the data in another column.

=IF(E15=“ALUMINUM 2219”, “TRUE”, “FALSE”)

If the text of cell E15 is exactly “ALUMINUM 2219-T81” then the selected cell will display “TRUE”, otherwise “FALSE” will be the value.



More on IFs including nested ifs and use in conjunction with OR formula coming soon!

How do I use the LEFT, RIGHT, and LEN functions in Microsoft Excel spreadsheets?

The LEFT or RIGHT function returns the leftmost or rightmost characters from a text value. Example:

=LEFT(A2,3)

If value in cell A2 were 184770 the formula would display the number 4.

=Right(B3,2)

If value in cell B3 were 16579 the formula would display the number 7.

LEN (length) returns the number of characters in a text string. Example:

=LEN(A2)

If the value in cell A2 is 16498 then the formula will display 5.


Combine the two in a single formula example. Say you have a single digit which indicates length in .125 increments. Double digits indicates length in whole inches AND .125 increments.

=IF(LEN(D2)=2,LEFT(D2,1)+(RIGHT(D2,1)*0.125),RIGHT(D2,1)*0.125)

If the length of the value of cell D2 (the length number) equals 2 (which means it is double digit), then add the left digit plus the right digit multiplied by 0.125, otherwise take the single digit and multiply by 0.125.

How do I create a message box vba macro in Excel?

This is a fairly easy macro to write and is good practice for beginners. Now we will make a message box automatically pop up when opening your workbook. You can either use the Auto Open method or the Workbook Open method. These macros will display the message "Hello" when you open the workbook.

Sub Auto_Open()
Msgbox "Hello"
End Sub

This code would be located in the module. However, if you use the second method, the code must be in the workbook (double click "This Workbook" in the explorer window). Click on the drop down list (that says General) and select Workbook. Click on the drop down list (that says declarations) and select Open.

Private Sub Workbook_Open()
Msgbox "Hello"
End Sub

You've just created your first macro. I will try to integrate different levels of difficulty from beginner, intermediate, and advanced tips for macros, formulas, and Excel tips in general.

What is an absolute reference in Microsoft Excel 2007 spreadsheet?

Dollar signs specify an absolute reference to a cell’s column and/or row.

Examples: =A1, =$A1, =$A$1

When copying a formula with an absolute reference, the $'d column or row will not change.

Example:

=A11 moved down will be A12
=A$11 moved down will be A11

How do I check for duplicate entries in Excel?

Go to ‘Data’ tab
Click ‘Remove Duplicates’
Click ‘Select All’ or pick which columns to remove duplicates.
Click ‘OK’
A window will pop up letting you know how many duplicates were removed, if any, and how many unique values remain. Click OK. That's all there is to it!

How do I make a random list in Excel?

How do you make a random list in Microsoft Excel 2007? Well, one way to go about it is as follows. First we have our list.



In the column next to it, put a =rand() into every cell.



This is the random number function, which generates a random number between 0 and 1.



Next, highlight both columns. Sort by the random numbers column and that's it!



Your list has been randomized!

How do I combine two cells in Excel?

Sometimes you may want to combine into one column text that is currently contained in two columns. One way to do this is to write a formula in a third column which uses the concatenation operator, the ampersand (&). This formula in cell C1 would return the contents of A1 followed by the contents of B1:

=A1&B1

If you want to include a literal character, say a space, use the form:

=A1&" "&B1

EXAMPLE: Rating Comparison Spreadsheet Template

In my very first post I mentioned that I use Excel all of time and in a variety of ways. One good example is when I began working at a new job after I graduated college and I needed to quickly find a new place of residence. I didn’t want to settle for just any old place. I wanted to know which location was best for me. What did I do? I turned to Excel to illustrate the pros and cons of different apartments I had chosen as candidates in order to help me decide which was the best.



Along the top you can see the names of all the apartment complexes. Down the left hand side I listed all of the features I was interested in knowing and specifically asked about when I visited the apartments. In-between resides the gathered data. The majority of the wanted features required a simple yes or no answer. The apartment rating line is the average rating given to the apartment from an online apartment rating website. I computed the yearly cost just to get an idea of how much it would be and if I could afford it. 

The final line is the most important, the scores for each apartment. How did I come up with those? Well if you look at the left hand side you might have noticed a bunch of hidden rows. That is where the magic happens. Each feature is given or subtracted points based on a factor of importance I created. The last column of the spreadsheet is the base line, my perfect living arrangement, and you can see the highest possible score is 12.5.



This is what the rows look like unhidden. I gave each listed feature a rating as some of them were more important than others. I have my own microwave already so that was not as important as having a washer and dryer in the room. Price of rent was essential but having a covered parking lot was not.



Here are the formulas I used for each feature:

Monthly Cost: =IF(B5<680,1.5,0)

Sq Feet = IF(B7>790,0.5,0)

Lease =IF(B8=6,1,0)

Pet Friendly =IF(B9="YES",1,0)

Dish-washer =IF(B10="YES",1,0)

Microwave =IF(B11="YES",0.5,0)

Washing Machine =IF(B12="YES",1.5,0)

Outdoor Pool =IF(B13="YES",1,0)

Indoor Pool =IF(B14="YES",0.5,0)

Covered Parking =IF(B15="YES",0.5,0)

Gated =IF(B16="YES",1,0)

Water =IF(B17="YES",0.5,0)

Private Entry =IF(B18="YES",1,0)

Deposit =IF(B19>200,-0.5,0)

Ap Fee =IF(B20>100,-0.5,0)

Distance to Work =IF(B21<12,0.5,0)

Online Rating =IF(B22>=0.5,0.5,0)

By using this Excel spreadsheet I no longer have to guess which apartment suits my needs, I now KNOW the best fit.

How do I create a two variable lookup in Excel Spreadsheet?

One way to create a two variable lookup formula in Excel 2007 is to use the INDEX and MATCH functions.

First, the INDEX function returns either the value or the reference to a value from a table or range. The syntax for the INDEX function is:

Index( array, row_number, column_number ) where array is a range of cells or table, row_number is the row number in the array to use to return the value, and column_number is the column number in the array to use to return the value.

The MATCH function searches for a value in an array and returns the relative position of that item. The syntax for the Match function is:

Match( value, array, match_type ) where value is the value to search for in the array, array is a range of cells that contains the value that you are searching for, and match_type is optional.

Putting it all together:

=INDEX(LookUp!$AA$2:$AA$156,MATCH($C2&$D2,LookUp!$X$2:$X$156&LookUp!$Y$2:$Y$156,0))

An important note is you MUST enter it via ctrl+shift+enter, not just enter. You'll know you did it correctly when it creates an extra set of special brackets around your formula ({}).

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula.

How do I make a "contain" formula in Excel?

There a simple way to create a "contains" formula in Excel. It works the same way as the AutoFilter feature 'Contains'.

For one cell, you could use the SEARCH formula that returns the position on a string where you can find another string or an error. With some logical formulas you can make Excel return the value if it is present or not. Example:

=NOT(ISERROR(SEARCH("x",A1)))

In order to count in a range, you could use the SUMPRODUCT formula. This will return the number of cells that contains the "x" text in the A1:A10 range. Something like this:

=SUMPRODUCT(--NOT(ISERROR(SEARCH("x",A1:A10))))


This formula, which is a little bit of a longer formula, returns whether it contains ABCD.

=IF(ISERROR(FIND("ABCD",A1)),"False","True")

Or there is also this simpler formula, but will return TRUE if the cell DOES NOT contain ABCD

=ISERROR(FIND("ABCD",A1))

=FIND("what you are looking for", cell ) -- the output is the number of characters from the beginning of the cells value where it finds "what you are looking for". If it doesn't find it, it returns #Value.

=ISERROR(cell) -- output is whether that cell returns an error (such as #Value)

=IF(ISNUMBER(SEARCH(Exceptions!$J$2,Sheet2!A2)), "DELETE","")

How do I count by increments in Microsoft Excel 2007?

You may be wondering how to make a list in excel count from one specified number to another in user defined increments. Well, I recently had to use this application myself and this is the format I created. A user enters the number to start counting from in the first yellow square, the number to end at in the next square, and finally, in what increments they would like to count up at. Here is the driving formula:

=IF(I2="","",IF(I2+1*$F$1<=$D$1,I2+1*$F$1,"")) and =B1 in cell I2.

Pictured below are two examples.

Welcome!

Hello! Welcome to the Excel Spreadsheet help blog. I will share some of my tips and tricks for making Microsoft Excel spreadsheets. I use Excel almost every single day. First, let me tell you a little bit about myself. I graduated from the University of Toledo with a degree in Mechanical Engineering. School got me started on Excel but it was my work experience at Emerson Climate Technologies that really taught me how to use Excel and its advantages. I continue to learn and improve my skills in my current engineering job as well as for my own personal uses which I will share with you. The purpose of this blog is to share my knowledge and help others so please ask any questions that you may have!

-NTW