Excel with Excel — Part 2

FI-Log-On-With-Larry-SchneiderDear Larry,

I’m confused by your previous column on Excel (archived at Greenwich-post.com). When you had us copy the formula for Jane’s pay to Bill’s pay and Susan’s pay, Excel did exactly what it should have done and correctly calculated pay for Bill and Susan. I have no complaints there, but why didn’t their pay get set according to Jane’s hours? 

V. E.

 

Dear V. E.,

That’s a very insightful question on your part. Most people are just happy with the fact that Excel works the way they intuitively expect it to work.

Recall that we created the formula for Jane’s Pay (Cell E2) as follows: =C2*B2+D2*1.5*B2.

Although we see Cell E2’s formula as “=C2*B2+D2*1.5*B2,” internally, Excel really sees it this way: “= the cell two columns to my left * the cell three columns to my left + the cell one column to my left * 1.5 * the cell three columns to my left.”

That’s called relative addressing because the cell references are relative to the cell containing the formula. You’ll notice that when you copied Cell E2’s formula to Cell E3, that formula was set to “=C3*B3+D3*1.5*B3.”

So when might you not want to use Relative Addressing? Well, when you want to use absolute addressing instead.

Here’s an example. Let’s say you want to create a multiplication table for one of your kids. Start by typing the number 1 into cell B1. Set the formula in C1 to =B1+1 and copy C1 to D1 through K1. Now do the same thing in column A: Put a 1 in Cell A2, put the formula =A2+1 in Cell A3, and copy A3 to A4 through A11.

Now it’s time to create your first formula in Cell B2. You might think you want that formula to be =B1*A2. Unfortunately, because of relative addressing, that won’t work when we subsequently copy and paste the formula. If you don’t believe me, try it and see.

We actually want the formula for any cell to be “= the cell in column A and my row times the cell in my column and Row 1.” This formula ensures that the multiplication table is built around the values in Row 1 and Column 1.

So how do we enter this formula that uses part relative addressing and part absolute addressing? By preceding the absolute reference with a dollar sign.

In Cell B2, type the formula =$A2*B$1. Note that the dollar sign preceding the A in A2 forces the column reference to always be A, but it allows the row reference 2 to change. Similarly, the dollar sign preceding the 1 in B1 forces the row reference to be 1 but allows the column reference to change. When you copy and paste this formula throughout your multiplication table, everything will work just right.

Knowing when it’s appropriate to use absolute versus relative addressing will make even the beginning Excel user a more advanced Excel user. Well, OK, relatively speaking, that is.

This is Larry Schneider, logging off.

Larry Schneider is the owner of Accent on Computers, a Greenwich-based consulting firm catering to individuals, businesses and professional offices. Services include computer setup, training, troubleshooting, virus resolution, networking, Internet, database and programming. Call 625-7575, visit Accentoncomputers.com, or send email to [email protected] 

By participating in the comments section of this site you are agreeing to our Privacy Policy and User Agreement

© Hersam Acorn. All rights reserved. The Greenwich Post, 10 Corbin Drive, Floor 3, Darien, CT 06820

Designed by WPSHOWER

Powered by WordPress