Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel: Applying formula to entire column


  • Please log in to reply
10 replies to this topic

#1 Hal06

Hal06

  • Members
  • 921 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:New York, New York, USA
  • Local time:05:07 AM

Posted 20 January 2011 - 02:07 PM

How does one apply a formula to an entire column? In my case column K shows the birth year of a large list of persons. Column L shows the age using the formula "2010-column K." So each row in column L has a formula for 2010 - the amount in each row's column K. Now I wish to change that to 2011. Is there any easy way to do so?

Thank you.

BC AdBot (Login to Remove)

 


#2 Eyesee

Eyesee

    Bleepin Teck Shop


  • BC Advisor
  • 3,545 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:In the middle of Kansas
  • Local time:04:07 AM

Posted 20 January 2011 - 02:24 PM

Why not just do a find and replace?

Actually I wouldnt hard code any value into an Excel formula at all.
You could use the =now() fuction to put todays date on the spreadsheet somewhere.
Then use the =year() function to extract the year from that date.
That way you never have to mess with it again.
In the beginning there was the command line.

#3 Hal06

Hal06
  • Topic Starter

  • Members
  • 921 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:New York, New York, USA
  • Local time:05:07 AM

Posted 20 January 2011 - 02:50 PM

Thanks, Eyesee. Do you mean something like this:


In an empty cell type =year(2011) e.g. cell B2
Then in each cell where I need an age create a forumula "B2-Kn" where K is the column with the birth year and "n" is the row number?

#4 Eyesee

Eyesee

    Bleepin Teck Shop


  • BC Advisor
  • 3,545 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:In the middle of Kansas
  • Local time:04:07 AM

Posted 20 January 2011 - 03:50 PM

Close

Say in A1 put =now()
That gets the system date & time
Then in B1 put =year(a1) that strips the year off then use that in your formula
In the beginning there was the command line.

#5 Hal06

Hal06
  • Topic Starter

  • Members
  • 921 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:New York, New York, USA
  • Local time:05:07 AM

Posted 20 January 2011 - 04:46 PM

Thanks again!

#6 Hal06

Hal06
  • Topic Starter

  • Members
  • 921 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:New York, New York, USA
  • Local time:05:07 AM

Posted 20 January 2011 - 05:01 PM

Eyesee,

Sorry to bug you with this, but I have about 490 rows in this spreadsheet. How do I copy a formula to all cells in a column without changing the first portion?

I have =now() in A488
I have =year(A488) in A489

in column K I have birth years.
In column L I want the age.

In L2 I entered the forumla: =A489-K2. That gave me an age.

But when I try to drag that formula down the column in the other cells the formula advances from A489-K2 to A490-K3, A491-K4, etc.

Any thoughts on how to avoid that so that the A489 stays the same while the second part advances each time?

Do I make sense?

Thanks.

#7 Hal06

Hal06
  • Topic Starter

  • Members
  • 921 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:New York, New York, USA
  • Local time:05:07 AM

Posted 20 January 2011 - 05:12 PM

Wait! I think if I enter the formula as $A$489 that keeps that portion constant. Thanks.

#8 Eyesee

Eyesee

    Bleepin Teck Shop


  • BC Advisor
  • 3,545 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:In the middle of Kansas
  • Local time:04:07 AM

Posted 20 January 2011 - 06:01 PM

Yep
Absolute values instead of relative. Cool stuff once you get the hang of it

Consider also putting the =now() function at the top of the spreadsheet in one location and use the same technique to reference it with the formulas thoughout. That way its only in one spot. You dont need it on every line.
In the beginning there was the command line.

#9 Hal06

Hal06
  • Topic Starter

  • Members
  • 921 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:New York, New York, USA
  • Local time:05:07 AM

Posted 21 January 2011 - 03:40 PM

Thanks again.

#10 Eyesee

Eyesee

    Bleepin Teck Shop


  • BC Advisor
  • 3,545 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:In the middle of Kansas
  • Local time:04:07 AM

Posted 22 January 2011 - 02:33 PM

Good job! :thumbsup:
In the beginning there was the command line.

#11 AdamV

AdamV

  • Members
  • 65 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Leeds, UK
  • Local time:10:07 AM

Posted 04 February 2011 - 05:23 AM

Before using NOW you need to be aware that this is a volatile function. This means that every time you make any change to anything in your spreadsheet, the NOW function is recalculated (it fetches the system time and date again), then all the cells which depended on it will also be recalculated.
TODAY (which returns the date without the time element) would make just as much sense for the use you are putting it to here, but again this is volatile.

Since the year only changes once every 365 days or so (I think), it seems a bit daft to have a spreadsheet calculating hundreds or thousands of cells every time you touch it. Sledgehammer > walnut.

Just put the year as a value (2011) in a single cell and reference that in your formulae instead.

Better still, as a learning experience, find out about using defined names and simply have a name such as "CurrentYear" which you can then either point at the cell you have that value in or explicitly defined as a constant value. In this case I would tend to go for the cell reference approach as the year is then visible on the spreadsheet so it is obvious if it is incorrect, and easier to change later. You can also name the whole of column K as "BirthYear" and then your formula in column L cells would read CurrentYear-BirthYear. This is easily understood, it is obvious what it is calculating and you don't need to worry about those $ signs to force relative and absolute references in the right places (CurrentYear points at a single fixed cell, BirthYear points at a whole column, so Excel uses an implicit intersection to give you the value in that column from the row you are currently working on).

Hope this helps
Due to global warming, eskimos now have more than 20 words for water John O'Farrell

Professional geek, consultant and trainer.
MCT, MCSA, MMI, MBMSS, CWNA, COS (I like to have lettuce after my name)
My personal blog - Getting IT right




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users