" />

So far we have just done calculations and a little bit of text. Now we will cover combining text and calculations. The underlying concepts of cell combinations means much more than just numbers can be displayed.

Example:

Lets go to C1 and format the cell as text. Remember you do that with a right click and then go down to format. Then within the format selection choose text.

Then go to C2 and enter 5. All cells are set up for values by default so unless you need something special leave that part alone … for now

Go to C3 and enter =C1*A1

At A1 set the value to 3

Now at C1 enter text Apples

At C4 enter =(C3+C1). To do combinations with text the starting and ending () must in place for it to work.

Note the C4 cell has a #VALUE! error.  We entered a + where it should have been a &. Go into C4 and enter =(C3&C1). The & function allows you to combine text (in programming lingo you concatenate the values). Putting a + function between cell values and text won’t work.  Now the value show up as 15Apples. That’s nice but it can be made to read easier. At C4 enter =(C3&” “&C1) now the value will show as 15 Apples. The “ “ allows you to enter a space into it. This method will allow an infinite amount of adjustment to the final presented value and description. Any text value can be entered in the double quotes.

Yes I know I promised you database info we will get to that but you need to understand a lot of basics first. Going directly to the database calculations now would be an invitation to confusion.

## Combining Text cells

1. With C2 and C3 with data goto C4
2. At C4 enter = C2&C3
3. Now look at the combined text  hello(claim jumper) green 52(clammy jumper)
4. At C4 enter = C2&” “&C3
5. Now look at the combined text  hello(claim jumper)  green 52(clammy jumper)
6. The double quoted space is entered in the output

## Extracting Text subsets

1. Now for the harder part we need to separate hello from claim jumper from C2 and Green52 from Clammy jumper in C3
2. The first command to use in D2 is to find the length of the total text from each cell
1. Enter = len(C2) into D2
2. The len command brings back the length in characters in D2
3. Copy D2 into D3 now it shows the number of characters in D3
4. Now we need to get tricky we need to know at what character the ( is at
1. In E4 enter =FIND(“(“,B4). This will count the number of characters from the left in B4 to match the first (
2. The answer for B4 will be 6
3. Copy E4 into E5
4. From this we can get =MID(B4,E4+1,C4-E4-1)
1. B4 is the location of the text string
2. E4 is the count for the position of the ( since don’t want to include the “(“ we add one. This value is now the place where we start extraction
3. C4 is the total length of the string since we don’t want the final “)” we subtract 1 we also subtract the starting point for the “(“ E4
4. From all of this work we get just the text section in the () regardless of the length of the text string
5. Now imagine how quickly you can extract the right text data from a spreadsheet. Also if you need to combine text from multiple locations it can be done without typing it manually. Let Excel do the work.

Example

Today’s exercise from this starting point how do you get the first portion of the text string out? Enter your calculation into G4

## Cell formatting

Each cell can be adjusted for its use and presentation. It can be set for the accuracy of the calculation, money, percentage and more.  I’ll show you how to format a cell.

1. Left click cell to select it
2. Right click mouse
3. A floating menu will appear on the right. Use the mouse to scroll down the menu and select format
4. Cell formatting selections

5. Lots of things to play with here
1. Number tab adjusts it’s behavior and accuracy.
2. Alignment tab adjusts how the cell contents will be displayed.
3. Font adjusts both the size, font type and color of the displayed information
4. Border adjusts the border around the cell. Size, color and line type selections.
5. Fill adjusts the color of the cell.
6. Protection tab does two things. It doesn’t have much particle use. Unless you are sending it to someone that you can’t trust to use it right. Then perhaps it has a use.

i.      Hides the value/calculation in the cell

ii.      Locks the cell from changing

I invite you to explore this area thoroughly. There is enough going on in this little area that a good explanation of all the options could take a month of posts. Many of these items should be self explanatory. I’ll take questions on individual selections.

Opening Excell the first time can be intimidating. The functions across the top of the screen and all those little empty boxes.

1)      Take note of the Letters across the top and the numbers on the rows. If look at the very top left cell it’s location is A for the column and 1 for the row or A1. Calculations can use the information at that location. The calculations can be textual or numerical. Most of the time the calculations will be the first use but there are other usages for the text that are unexpected

1. Exercise

i.      Goto  cell B1

ii.      Enter =

iii.      With the  mouse select A1

iv.      Enter a +

v.      Put in a value of 5 and enter

vi.      The total in cell B1 will be 5

vii.      Choose A1 enter a 10

viii.      Note that the value in B1 changes to a 15

ix.      Select B1 and you will see the following display

x.      That is the basic usage of Excel referencing other cells to create a new value

xi.      The B1 in the upper left side shows the cell selected

xii.      To its’ right the =5+A1 shows the calculation in that cell

Very very basic now try it yourself

1. Choose A2 and enter 24
2. Choose B2 and enter 12
3. Choose C2 and enter = A2/B2
4. See that the answer is .5
5. Edit C2 and change the calculation to = A2*B2 ( The * indicates a multiplication)
6. Now choose all three cells A2&B2&C2 and pull downward
7. It will populate A3&B3&C3
8. Choose C3 and note that the calculation is the same but it now references A3 * B3

That’s enough to start with. There are other secrets to copying and references coming soon

Keep up the practice there’s lots to learn

This is not to sing the praises of Microsoft it’s to show what can be done with a readily available tool with some customizations.

Excel by itself has lots of places and functions to explore and I’ll take you though a few.  There are too many capabilities in Excel to learn them all. I’m going to take you on a trip into new ways to use it.

Warning this will go well into the programming zone but I’ll take you one small step at a time.

You will learn:

1. How to program cells for individual calculations
2. How to do automated searches for data
3. Dynamic extraction of data
4. How to turn Excel into a moving text marque
5. How to rearange data by  using Visual Basic
6. How to create a interface to control your program
7. How to speed up your programs execution

So hold on we are just getting started. Real exercises to come soon. After you learn a few tricks it will get much much easier.

English: Illustration of subroutine in Microsoft Excel that reads the x-column, squares it, and writes the squares into the y-column. All proprietary Microsoft art work has been cropped to leave a generic spreadsheet (Photo credit: Wikipedia)

Page 1 of 23  1  2  3  4  5 » ...  Last »
3 visitors online now
0 guests, 3 bots, 0 members
Max visitors today: 7 at 08:00 am CDT
This month: 11 at 05-01-2013 12:45 am CDT
This year: 36 at 03-28-2013 06:28 pm CDT
All time: 84 at 05-28-2012 04:35 pm CDT

Featuring YD Feedwordpress Content Filter Plugin