1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Apply factor to range of cells

Discussion in 'Business Applications' started by DKTaber, Jan 15, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    Back in the DOS days, I used a program called Enable. It was the DOS equivalent of MS Office, in that it had all the modules (spreadsheet, database, word processing, etc.) and you could have multiple modules open in multiple windows.

    One wonderful feature of the spreadsheet was that you could highlight a range of cells, and apply a factor to all the values in the range. E.g., if I wanted to change hourly rates in a range of cells to annual salaries, I could highlight the range, enter the factor "*2080", and all the values in that range were instantly multiplied by 2080.

    I can find no way to do that in Excel 2000. Is there?
     
  2. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Hi, Don. My guess is no, not without a macro. But I'm not quite sure I see the "greatness" of the feature you're describing. I mean, you lost the original values, right?

    If you create just one cell using your formula, and then copy/paste for the range, you've only used a couple more clicks/KB shortcuts than what you're talking about, and the original values are still there...?
     
  3. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    I guess I didn't explain it very well. I do compensation surveys that often involve jobs on salary and jobs paid hourly. Sometimes in the middle of recording the data in both salary and hourly terms, I decide I need to convert all the hourly entries to salary. What I have to do now is go to EACH cell, one by one, click the = sign, and add (e.g.) "*2080" to convert the value to an annual salary. Not a problem for one or two cells, but I frequently have 50 to do that way. With Enable, I could just select the cells, click "Modify values", and enter the modifier (in this case, "*2080"), and all the cells would instantly change to the salaried figure.

    Enable had this feature in 1984. As far as I can tell, Excel never had it, and still does not have it... unless you know a way to do it.
     
  4. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Except that when you copy/paste the formula moves with you. In other words, if you have the value 100 in cell A1, and 200 in B1, and you create the formula =A1*2080 in A2, and then copy it to B2, it becomes B1*2080. So you only have to put the formula into one cell, once, and then copy and paste as many times as you need it, as long as you keep the same physical relationship (e.g. a column) between the two cells. And you haven't lost the original values, in case you change your mind back (as has been known to happen).

    Otherwise, you're looking at writing macros, and if the cell(s) in question are constantly changing, copy/paste is just about as fast.

    An alternative method would just be to set up an entire column with the formula already copy/pasted next to the hourly one, you'll see that as you go through and put in the hourly values, the annual values are filled in automatically. Again, makes no sense to me to change a value from thing1 to thing2 when you could someday need thing1 back.
     
  5. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,871
    I guess that's the only way I can do it. It was just so convenient with the old DOS program.

    Thanks for the response.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/65009

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice