Science Tutor

The IF Function - M. S. Excel Tutorials - Science Tutor

Anoop Kumar Sharma September 24, 2018

The IF function can be quite useful in a spreadsheet. It is used when you want to test for more than one value. For example, has a bill been paid or not? If it has, you can deduct the amount from the money you have left to spend; if it hasn't, keep it on your debt list. Later, you'll see how to use the IF Function to grade student exam scores. If the student has above 80, award an A grade; if the student has below 30, award a fail grade. First, here's what an IF Function looks like:

IF(logical_test, value_if_true, value_if_false,)

The thing to note here is the three items between the round brackets of the word IF. These are the arguments that the IF function needs. Here's what they mean:

logical_test: The first argument is what you want to test for. Is the number in the cell greater than 80, for example?
value_if_true: This is what you want to do if the answer to the first argument is YES. (Award an A grade, for example)
value_if_false: This is what you want to do if the answer to the first argument is NO. (Award a FAIL grade.)

If that's not terribly clear, an example may clear things up. Open a new spreadsheet, and do the following:

Widen the B column a bit, as we'll be putting a message in cell B1
Now click in cell A1 and type the number 6
Type the following in the formula bar (The right angle bracket after A1 means "Greater Than".)

=IF(A1 > 5, "Greater than Five", "Less than Five")

Hit the enter key on your keyboard and your spreadsheet should look like ours below:

(Make sure you have all the commas and double quotes in the correct place, otherwise Excel will give you an error message. That right angle bracket ( > ) is known as a Conditional Operator. You'll meet some others shortly.)

But what we're saying in the IF function is this:
logical_test: Is the value in cell A1 greater than 5?
value_if_true: If the answer is Yes, display the text "Greater than Five"
value_if_false: If the answer is NO, display the text "Less than Five"

So your first tell Excel what you want to check the cell for, then what you want to do if the answer is YES, and finally what you want to do if the answer is NO. You separate each part with a comma.

Exercise

Try this:

Click into cell A1
Change the 6 into a 4
Hit the enter key on your keyboard

What happens?

Exercise
Now type the number 5 in cell A1. What happens now?

For the last exercise above, Excel should tell you that 5 is "Less than 5"! It does this because the answer to your logical test was NO. We were testing if the number in cell A1 was greater than 5. Since 5 is not greater than 5, the answer to the question is NO. We've told Excel to display a message of "Less than 5", if the answer was NO. In other words, we didn't tell Excel what to do if the value in cell A1 was the same as 5.

The solution to this is to use a different Conditional Operator. We used the Greater Than ( > ) operator. Here's some more:
< Less Than

>= Greater than Or Equal To

<= Less than Or Equal To

<> Not Equal To

For the second and third operators above, you type an angle bracket followed by the equals sign. There are no spaces between the two. For the final one, it's a left angle bracket followed by a right angle bracket.

So for our exercise, the symbol we should have used was the one for Greater than Or Equal To. Change your IF function to this and try again:

=IF(A1 >= 5, "Greater than or Equal to Five", "Less than Five")

Exercise
Test the A1 cell to see if the value is less than or equal to 5. If it is, display a suitable message. If it's not, display the message "Greater than Five".

Complex If Functions

The If Functions you've just met are consider fairly simple ones. They can get really complex!
Consider our Student Exam problem. The spreadsheet we created to track our students looks like this, from an earlier section:

However, we want to display the following grades as well:

A If the student scores 80 or above
B If the student scores 60 to 79
C If the student scores 45 to 59
D If the student scores 30 to 44
FAIL If the student scores below 30

With such a lot to check for, what will the IF Function look like? Here's one that works:

=IF(B2>=80, "A", IF(B2>=60, "B", IF(B2>=45, "C", IF(B2 >=30, "D", "Fail" ) ) ) )

Quite long, isn't it? Look at the colours of the round brackets above, and see if you can match them up. What we're doing here is adding more IF Functions if the answer to the first question is NO. If it's YES, it will just display an "A".

But take a look at our Student Exam spreadsheet now:

After the correct answer is displayed in cell B14 on the spreadsheet above, we used AutoFill for the rest!
Don't worry if that long IF statement is making your brain hurt - it is quite complicated.

In the next part, we'll take a look at Conditonal Formatting. This is about colouring cells depending on their values. Looks nice on a spreadsheet!

Buy Complete M.S. Excel Tutorial Book From Amazon : https://amzn.to/2yEYXgk

The IF Function - M. S. Excel Tutorials - Science Tutor

Reviewed by Anoop Kumar Sharma on September 24, 2018 Rating: 5

Chart Styles and Layouts - M. S. Excel Tutorials - Science Tutor

Anoop Kumar Sharma September 21, 2018

You can easily change the Style of your chart. If you can't see the Styles, click anywhere on your chart to select it, and you should see the Ribbon change. The Styles will look like this in Excel 2007:

In later versions of Excel, your Chart Styles will look like this:

Click on any chart style, and your chart will change. To see more styles, click the arrows to the right of the Chart Styles panel:

You'll then see a drop down sheet of new styles (Excel 2007):

And here's the Styles in Excel 2013:

Work your way through the Styles, and click on each one in turn. Watch what happens to your chart when you select a style.

Chart Layouts

You can also change the layout of your chart in the same way. Locate the Chart Layoutpanel on the Design tab of the Excel Ribbon bar. It looks like this in Excel 2007:

In later versions you may have to click theQuick Layout option on the Chart Layoutspanel:

Click the down arrow to the right of the Chart Layouts panel to see the available layouts you can choose from:

Again, click on each one in turn and see what happens to your chart. In the image below, we've gone for Layout 10:

Changing the Chart Type - 2D Bar Charts

You can change the type of chart, as well. Instead of having a 2D column chart, as above, you can have a 2D bar chart. To change the chart type, locate the Type panel on the Excel Ribbon bar (you need to have your chart selected to see it):

Then click Change Chart Type. You'll see a dialogue box appear. This one is from Excel 2007:

The dialogue box looks slightly different in Excel 2013:

Select Bar from the list on the left of the dialogue box, and click on the first Bar chart (Clustered Bar). Click OK to see your chart change:

You can experiment with the types of chart in the dialogue box. But reset it to Bar chart, as above.

In the next part, you'll see how to format a chart, so that you can change the Series 1 and Chart Title headings.

Chart Styles and Layouts - M. S. Excel Tutorials - Science Tutor

Reviewed by Anoop Kumar Sharma on September 21, 2018 Rating: 5

Move and Resize your Chart - M. S. Excel Tutorials - Science Tutor

Anoop Kumar Sharma September 19, 2018

You might find that your chart from the previous lesson is covering your data. In the image below, our chart is overlapping the ITV data. To move it, hold your mouse over the chart until your cursor changes shape: (We found that the best place for your mouse is over the dots in Excel 2007, as we had problems moving a chart when the cursor was anywhere else! Moving charts in later versions of Excel is easier.)

Press and hold down the mouse button when your cursor looks like the one in the image above, and then drag your chart to a new location. In the image below, we've placed the chart below the data.

You can also place your chart in a different worksheet. To do this in Excel 2007, right click anywhere on your chart. From the menu, select Move Chart:

In Excel 2010 and 2013 there is a Locationpanel to the right of Chart Styles. Click theMove Chart item:

In all versions, you'll then get a dialogue box popping up:

If you want your chart in a new worksheet, select the first option. Then delete the text "Chart1" from the textbox, and then type a name of your own.

If you look along the bottom of Excel , you'll see Sheet1, Sheet2, and Sheet3. Your data is in Sheet1. If you click the drop down list to the right of Object in on the dialogue box above, you'll see the other worksheets you have open. You can select one from the list and click OK. But for this first chart, leave it in Sheet1.

How to Resize an Excel Chart

You can resize a chart, and any elements on it, by moving your mouse over the sizing handles. For the chart itself, the sizing handles are the dots around the edges of the chart in Excel 2007:

In later versions, the sizing handles are white squares:

When your mouse changes shape to a double-headed arrow, hold down your left mouse button. Then drag to a new location. You can resize using the corners, or the edges..

In the next part, you'll see how to use Chart Styles and Chart Layouts in Excel.

Buy Complete M.S. Excel Tutorial Book From Amazon : https://amzn.to/2yEYXgk

Move and Resize your Chart - M. S. Excel Tutorials - Science Tutor

Reviewed by Anoop Kumar Sharma on September 19, 2018 Rating: 5

Create an Excel Chart - M. S. Excel Tutorials - Science Tutor

Anoop Kumar Sharma September 09, 2018

We’re now going to create a chart from our BBC1 Viewing figures. If you haven't yet completed the sorting tutorial, go back one page and follow along with the lesson. You'll then have a some sorted viewing figures to create a chart from.

When our chart is finished, though, it will look like this:

A little later, you'll see how to improve on this basic chart.

To start making your chart, highlight the BBC1 programmes, and the viewing figures. If you have just finished the sorting section, this data should still be highlighted, and look like this:

With your programmes and the viewing figures highlighted, do this:

From the tabs on the Excel Ribbon, click on Insert
Locate the Charts panel. It looks like this in Excel 2007:

In later versions of Excel, the Charts panel looks like this:

For this first one, we'll create a Column Chart. So, in Excel 2007, click the down arrow on theColumn item of the Chart Panel. You'll see a list of available charts to choose from. Select the first one, the chart highlighted below (2D Column):

The Column drop down list in later versions of Excel looks like this:

When you make your selection, a new chart appears on the same spreadsheet that you have open. The chart should look the same as the one at the top if this page.

But notice that the Excel Ribbon has changed. The design menu is selected, along with options for Chart Layouts:

In Excel 2013, you'll see these layouts on the left, in the Chart Layouts panel, under Quick Layouts:

Also on the Design Ribbon, you'll see options for Chart styles:

You'll see how to use these later. For now, your chart may be covering your viewing figures. In the next part, we'll see how to move and resize a chart.

Buy Complete M.S. Excel Tutorial Book From Amazon : https://amzn.to/2yEYXgk