# Medical Transcription

This blogging is totally dedicated to both those who are freshers as well as experienced transcriptionists who can use it for reference as well as gain some idea about what does medical transcription means.

## Saturday, November 19, 2016

## Monday, August 1, 2016

### Named Ranges in Excel

## Home>>

A Named
Range is way to describe
your formulas. So you do not have to
have this in a cell:

**=SUM**(B2:B4)

You can replace the cell references between the round
brackets. You replace them with a
descriptive name, all of your own. So
you could have this, instead:

**=SUM**(Monthly_Totals)

Behind the Monthly_Totals, though, Excel is hiding the cell
references. We will see how it works,
now.

Open up Excel and create the spreadsheet below:

The formula is in cell B5 and just adds up the monthly
totals in the B column.

**Define a Name**

Setting up a Named
Range is a two-step
process. You first Define the Name, and
then you Apply it. To Define your name,
do this (make sure you have the formula in cell B5):

Highlight the cells B2 to B4 (Not B5), then click the

**Formulas**menu.
Locate the Named cells panel in Excel 2007. In Excel 2010 and 2013, locate the

**Defined Names**panel instead.
Click

**Name a Range**in Excel 2007 and**Define Name**in Excel 2010 and 2013.
From the

**Name a Range**menu, click**Name a Range (Define Name**again in Excel 2010/13):
You will then get the following dialogue box:

Click OK on the New Name dialogue box. Notice that the Name is our heading of

**Monthly_Totals**.
When you click OK, you will be returned to your
spreadsheet. You would not see anything
changed. But what you have done is to
Define a Name. You can now Apply it.

**Apply a Name**

To apply your new Name, click in to cell B5 where your
formula is, and do this:

On the

**Named Cells**panel, click**Name a Range**. For Excel 2010/13 users click**Define Name>Define Name**
From the menu, select

**Apply Names**
From the Apply Names dialogue box, select the Name you want
and click OK:

When you click OK, Excel should remove all those cell
references between the round brackets, and replace them with the Name you
defined:

In the image above, cell B5 now says:

**=SUM**(Monthly_Totals)

The cell references have been hidden. But Excel still knows about them – it is you
that can not see them.

**Exercise**

Study the spreadsheet below, now that we have added another Named Range
to cell C5:

Using the same techniques just outlined, create the same Named Range
as in our image above. Again, the
formula we have used is just a SUM formula:

**=SUM(C2:C4)**

You need to start with this, before you Define the Name and
Apply it.

**Using Named Ranges in Formulas**

We will now use two Named Ranges
to deduct the tax from our montly totals.

So, to define two new Names, do the following:

Click inside cell B5 to highlight it.

From the

**Formulas**menu bar, locate the**Named cells**panel, and click**Name a Range**> (Excel 2007). In Excel 2010/13, click**Define Name> Define Name**from the**Defined Names**panel.
From the

**New Name**dialogue box, click in to the**Name**textbos at the top and enter**Monthly_Result**(with the underscore character).
Click OK

Click inside cell C5 and do the same as step 2 above. This time, however, enter

**Tax_Result**as the Name
You should now have two new Names defined. We will now Apply these new names. First, add a new label to your spreadsheet:

Click in to cell B7, next to your new label, and enter the
following formula:

**=B5-C5**

With the formula in place, we can Apply the two new Names we
have just defined:

From the

**Formulas**menu bar, locate the**Named Cells**panel, click**Name a Range>Apply Names**(Excel 2007). In Excel 2010/13, click**Define Name>Apply Names**from the**Defined Names**panel.
The apply Names dialogue box appears.

Click

**Monthly_Result**to select it
Click on

**Tax_Result**to select it:
Click the OK button

Excel will replace your cell references with the two Names
you defined.

Your spreadsheet should look like ours:

If you look at the formula bar, you will see the two Named Ranges . The formula is easier to read like this. But it is not terribly easy to set up. They can be quite useful, though.

## Home>>

## Wednesday, July 27, 2016

## Tuesday, July 26, 2016

### MS Excel Goal Seek

### HOME

Goal Seek is used to get a particular result when you are not too sure of the starting value. For example, if the answer is 56, and the first number is 8, what is the second number? Is it 8 multiplied by 7 or 8 multiplied by 6? You can use Goal Seek to find out. We will try that example to get you started, and then have go at a more practical example.

Create the following Excel spreadsheet:

In the spreadsheet above, we know that we want to multiply
the number in B1 by the number in B2.
The number in cell B2 is the one we are not too sure of. The answer is going in cell B3. Our answer is wrong at the moment, because we
has a goal of 56. To use Goal Seek to
get the answer, try the following:

From the Excel menu bar, click on Data.

Locate the Data Tools panel and then What if Analysis
item. From the What if Analysis menu,
select

**Goal Seek**.
The following dialogue box appears:

The first thing Excel is looking for is “Set cell.” This is not very well named. It means “Which cell contains the Formula
that you want Excel to use.” For us,
this is cell B3. We have the following
formula in B3:

**= B1 * B2**

So enter B3 in to the “Set cell” box, if it is ot already in
there.

The “To value” box means “What answer are you looking for?” For us, this is 56. So just type 56 in to the “To value” box.

The “By changing cell” is the part you are not sure of. Excel will be changing this part. For us, it was cell B2. We were not sure which number when multiplied
by 8 gave the answer 56. So type B2 in
to the box.

Your Goal See dialogue box should look like ours below:

Click OK and Excel will tell you if it has found a solution:

Click OK again, because Excel has found the answer. Your new spreadsheet will look like this one:

As you can see, Excel has changed cell B2 and replaced the 6
with a 7 – the correct answer.

We will now try a more practical example.

**Goal Seek Number Two**

Consider this problem:

Your business has a modest profit of 25,000. You have set yourself a new profit Goal of
35,000. At the moment, you are selling
1000 items at 25 each. Assume that you
will still sell 1000 items. The question
is to hit your new profit of 35,000, by how much do you have to raise your prices?

Create the spreadsheet below, and we will find a solution
with Goal Seek.

The spreadsheet is split in to two: Current sales and future sales. We will changing the Future sales with Goal
Seek. But for now, enter the same values
for both sections. The formula to enter
for B4 is this:

=B2 * B3

And the formula to enter for E4 is this:

=E2 * E3

The current price per item is 25.00. We want to change this with Goal Seek,
because our prices will be going up to hit our new profits of 35,000, So try
this:

From the Excel menu bar, click on Data

Locate the Data tools panel and the What if Analysis
item. From the What if Analysis menu,
select Goal Seek

The following dialogue box appears:

For “Set cell”, enter E4.
This is wehre the formula is. The
“To Value” is what we want our new profits to be. So enter 35,000. The “By changing cell” is the part we are not
sure of. For us, this was the price each
item needs to be increased by. This was
coming from cell E3 on our spreadsheet.
So enter E3 I the “By changing cell” box. Your Goal Seek dialogue box should now look
like this:

Click OK to see if Excel can find an answer:

Excel is now telling that it has indeed found a
solution. Click OK to see the new
version of the spreadsheet:

## Monday, July 25, 2016

### MS Excel Scenarios

### HOME

Scenarios come under the heading of “What-If Analysis” in Excel. They are similar to tables in that you are changing values to get new results. For example, what if I reduce the amount I am spending on food? How much will I have left then? Scenarios can be saved, so that you can apply them with a quick click of the mouse.

An example of a scenario you might want to create is a family
budget. You can then make changes to
individual amounts, like food, clothes, or fuel, and see how these changes
affect your overall budget.

We will see they work now, as we tackle a family
budget. So, create the spreadsheet below:

The figure in B12 above is just a SUM function, and is your
total debts (=SUM(B3:B10). The figure in
D13 is how much you have left after you deduct all your debts. In cell D13, then enter =D3 – B12.

With only 46 pounds spending money left each month, clearly
some changes have to be made. We will
create a scenario to see what affect the various budges cuts have.

From the top of Excel click the

**Data**menu.
On the Data menu, locate the

**Data Tools**panel.
Click on the

**What if Analysis**item, and select**Scenario Manager**from the menu:
When you click

**Scenario Manager**, you get the following dialogue box:
We want to create a new scenario. So click the Add button. You will then get another dialogue box
popping up:

The J22 in the image is just whatever cell you had selected
when you brought up the dialogue boxes.
We will change this. First, type
a Name of your Scenario in the

**Scenario Name**box. Call It**Original Budget**.
Excel now needs you to enter which cells in your spreadsheet
will be changing. In this first scenario,
nothing will be changing (because it is our original). But we still need to specify which cells will
be changing. Lets try to reduce the Food
bill, the clothes bill, and the phone bill.
These are in cells B7 to B9 in our spreadsheet. So in the

**Changing Cells**box, enter B7:B9.
Do not forget to include the colon in the middle. But your Add Scenario box should look like
this:

Click OK and Excel will ask you for some values:

We do not want any values to change in this first scenario,
so just click OK. You will be taken back
to the Scenario Manager box. It should
now look like this:

Now that we have one scenario set up, we can add a second one. This is where we will enter some new values –
our savings.

Click the Add button again.
You will get the Add Scenario dialogue box back up. Type a new Name, something like

**Budget two**. The Changing Cells area should already say B7:B9. So just click OK.
You will be taken to the Scenario Values dialogue box
again. This time, we do want to change
the values. Enter the same ones as in
the image below:

These are the new values for our Budget. Click OK and you will be taken back to the
Scenario Manager. This time, you will
have two scenarios to view:

As you can see, we have our Original Budget, and Budget
two. With Budget Two selected, click the

**Show**button at the bottom. The values in your spreadsheet will change, and the new budget will be calculated. The image below shows what it looks like in the spreadsheet.
Click on the Original Budget to highlight it. Then click the Show button. The first values will be displayed.

Click the

**Close**button on the dialogue box when you are done.
So a Scenario offers you different ways to view a set of
figures, and allows you to switch between them quite easily.

**How to create a report from a scenario?**

Another thing you can do with a scenario is create a
report. To create a report from your
scenarios, do the following:

Click on

**Data**from the Excel menu bar
Locate the

**Data Tools**panel
ON the Data tools panel, click

**What if Analysis**
From the Scenario Manager dialogue box, click the
summary button to see the following dialogue box:

What you are doing here is selecting cells to go in your
report. To change the cells, click on
the spreadsheet. Click individual cells
by holding down the CTRL key on your keyboard, and clicking a cell with your
left mouse button. Select the cells D3,
B12, and D13. If you want to get rid of
a highlighted cell, just click inside it again with the CTRL key held
down. Click OK when you have selected
the cells. Excel will then create your
Scenario Summary:

All right, it is now terribly easy to read, but it looks
pretty enough. Perhaps it will be enough
to convince our family to change their ways.

### HOME

## Sunday, July 24, 2016

### MS Excel Data Table Part 2

### HOME

We will do one more Data Table, just so that you get the hang of things. This time, we will use a more simple formula than PMT, and we will use Rows instead of Columns. This is the scenario:

You have 250 items that you want to sell on Ebay. Your unique selling point in this – All items
are only $5 each. Except, you feel $5
may be a bit expensive for the goods you are selling, what you want to know is
how much profit you will make if you reduce your prices to $4.50, how much if
you reduce to $4.00, and how much for a reduction to $3.50. Assume that everything gets sold.

To start creating your table, construct a spreadsheet like
the one below. Make sure that you start
on a new sheet.

In cell B1 is the number of items we want to sell
(250). Cell B2 has the original price
($5.00), and the

**Reductions**row has our new values. Cell B3 has a 0 because there is no reduction for $5. Row 4 is where our Profits will go.
The formula to work out the profits is simply the

**Number of Items**multiplied by the**Price per Item**. So click inside cell B4 and enter the following formula:**=B1 * B2**

Your spreadsheet will then look like this:

So, if we manage to sell all our items at $5, we will make
$1,250. We are a bit dubious,
though. Realistically, all our items
won’t sell at this price. Lets use an
Excel Data Table to work out how much profit we would make at the other
prices.

Again, we put the answer in cell B4 for a reason. This is because when you want Excel to
calculate a Data Table in Rows, the formula must be inserted one Column to the
Left of your first new value, and then one Row down. Our first new value is going in cell C3. So one column to the left takes us to the B
column. One row down is Row 4. So the formula goes in cell B4.

Next, click inside cell B3 and highlight to cell
E4. Your spreadsheet should now look
like this one:

Excel is going to use our formula in cell B4. It will then look at the new values on Row 3
(not counting the zero), and then insert the new totals for us. To create a Data Table then, do the
following:

From the Excel menu bar, click on

**Data**.
Locate the Data Tools panel

Click on the “What if analysis” item

Select

**Data Table**from the menu.
Just like last time, you will get the Data Table dialogue
box. The one we want now, though is Row
Input cell. But what is the Input Cell
this time?

Ask yourself what you are trying to work out, and what you
want Excel to recalculate. You want to
work out the new prices. The formula you
enter was:

=B1 * B2

Excel is going to be changing this formula. You only need to decide if you want Excel to
alter the B1 or the B2. B1 contains the
number of items; B2 contains the price of each item. Since we are trying to work out the profits,
we would get if we change the price, we need Excel to change B2. So enter B2 for the Row Input cell:

When you click OK, Excel will work out the new
profits:

So setting a price of $3.50 per item, you would make $875
profit. You would make $1000 at $4.00
per item, and $1125 if you sell for $4.50.

### HOME

### MS Excel Data Tables Part 1

### HOME

In Excel, a Data Table is a way to see different results by altering an input cell in your formula. As an example, we are going to alter the interest rate, and see how much a $10,000 loan would cost each month. The interest rate will be our input cell. By asking Excel to alter this input, we can quickly see the different monthly payments. Want to know how much would pay back each month if the interest was 24 percent per year. But other banks may be offering better deals. So we will ask Excel to calculate how much we would pay each month if the interest ate was 22 percent a year, 20 percet a year, and 18 percent a year.

The formula needed is– PMT (). Here it is:

PMT (rate, nper, pv, fv, type)

We only need the first three aguments. So for us, its just this:

PMT (rate, nper, pv)

Rage means the interest rate. The second argument, nper, is how many months
you have got to pay the loan back. The
third argument, pv, is how much you want to borrow.

So we will put our starting interest rate in cell B3 (rate),
our loan length in cell B4 (nper), and our last loan amount in cell B5 (pv).

So you need to enter 24.00% in cell B3, 60 in cell B4, and
$10,000 in cell B5.

We will enter our formula now. Click inside cell D2 and enter the following:

=PMT(BE/12, B4, -B5)

Cell B3 is the interest rate. But this is for the entire year. In the formula, we are dividing whatever is
in cell B3 by 12. This will get us a
monthly interest rate. B4 in the formula
is the number of months, which is 60 for us.
B5 has a minus sign before it. It
is a minus figure because it is a debt.

When you press the enter key on your keyboard, Excel should
given you an answer of $287.68.

Now that we have our function in place, we can create an
Excel Data Table. First though we need
to tell Excel about those other interest rates.
It will use these to work out the new monthly payments. Remember, Excel is recalculating the PMT
function. So it needs some new values to
calculate with.

So enter some new values in cells C3, C4, and C5. Enter the same ones as in the image below:

We have put the PMT function in cell D2 for a reason. This is one Row up, and one Column to the
right of our first new interst rate of 22%.
The new monthly payments are going to go in cells D3 to D5. Excel needs the table setting out this way.

So that Excel can work out the new totals, you have to
highlight both the new values and the function you are using.

So highlight the cells C2 to D5. Your spreadsheet should look like this:

As you can see, the cells C2 to D5 are now highlighted. This includes our new interest rate values in
the C column, and our PMT function in cell D2.
We can now creat an Excel Data Table.
This will work out new monthly payments for us. So do this:

From the Excel menu bar, click on

**Data**
Locate the

**Data Tools**panel
Click on the “Whate if Analysis” item:

When you click on the “What if Analysis” item,
you will see the following menu:

Click on

**Data Table**, and you will see this small dialogue box:
In the dialogue box, there is only a

**Row input cell or a Column input cell.**We want Excel to fill downwards, down a column. So we need the second text box on the dialogue box “Column input cell.” If we were filling across in rows, we would use the “Row input cell” text box.
So click inside the column input cell box and enter
B3:

Click OK.
When you do, Excel will work out
the new monthly payments:

So if we could get an 18 percent interest rate, our monthly
payments would be $253.93.

If you click inside any of the cells D3 to D5, then look at
the formula bar, you will see this:

**{=TABLE(,B3)}**

That’s Excel’s way of telling you that a table has
been created, based on the input cell B3.

Labels:
MS Excel Data Tables Part 1

### MS Excel Add Error Messages

You can also restrict what goes in to a cell on your spreadsheet, and display an error message for your users. We will do this with our comments
column. If users enter too much test, we
will let them know by displaying a suitable error box. Try the following:

Highlight the E column on your spreadsheet (the comments
column).

From the

**Data Tools**panel, click**Data Validation**to bring up the dialogue box again.
From the

**Allow**list, select Text length:
When you select Text length from the list, you
will see three new areas appear:

What we are trying to do is to restrict the amount of text a
user can input into any one cell on the Comments column. We will restrict the text to between 0 and 25
characters.

The first of the new areas (Data) is exactly what we
want – Between. For the minimum textbox,
just type a 0 (Zero) in there. For the
maximum box, type 25. Your dialogue box
should then look like this:

To add an error message, click the

**Error Alert**tab at the top of the Data Validation dialogue box:
Make sure there is a tick in the box for “Show error alert
after invalid data is entered.”

You have three different Styles to choose from for your
error message. Click the drop down list
to see them:

In the

**Title**tex tbox, type some text for the title of your error message.
Now click inside the error message field and type some text
for the main body of your error message.
This will tell the user what he or she did wrong.

Click OK on the Data validation dialogue box when you are
done.

To test out your new error message, click inside any cell in
your Comments Column. Type a message longer
than 25 characters. Press the enter key
on your keyboard and you should see your error message appear:

As you can see, the user is prompted to Retry or
Cancel. But our title (Too many
characters) is at the top, our stop symbol is to the left, and our error message
is displaying nicely.

**Hiding Spreadsheet Data in Excel 2007 to 2013**:

The data that went in to our lists does not need to be on
show for all to see. You can hide this
text quite easily.

Highlight the columns with your data in it (F, G, and H for
us).

Click on the

**Home**tab from the top of Excel.
Locate the

**cells**panel.
On the cells panel, click on

**Format**.
You will see the following menu:

Move your mouse down to

**Hide and Unhide**and you will see a sub menu appear:
Click on Hide columns from the Sub menu. Excel will hid the columns you selected:

In the spreadsheet above, the columns F to H are no longer
visible.

To get them back again, highlight the columns E and I. From the same submenu, click

**Unhide Columns**.### HOME

Labels:
MS Excel Add error messages

Subscribe to:
Posts (Atom)