Apply factor to range of cells

[DKTaber]

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?

 

[downwitchyobadself]

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...?

 

[DKTaber]

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.

 

[downwitchyobadself]

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.

 

[DKTaber]

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.