Thread: Excel (2000) multiple row/column calculation conversion

The easiest way is to insert a new sheet and then reference the sheet with the numbers as part of your formula.

edit: I did it for you. see attachment

Thanks Wongdai, that is certainly a neat solution and has the advantage of preserving the original inch dimensions.

The thing that concerns me however is that the new formula would have to be entered/copied for each cell and there are close to 5000 cells, so a bit of ork involved there.

I was hoping to just select a range of cells and apply a converison factor to that range of cells, but can't find any reference to a function for that.

Big Shed
I'm no guru but he way I would do it is to place your conversion factor (i.e.) 25.4 in a cell ..say G1 just to the right of your current array then in cell ..say H1 ..place the formula

= A1*$g$1

Select this cell , click and drag it to the right as many columns as you have in your first array and then once that is done select that row and click and drag it down the number of rows you have in your first array and bingo - done!!

May sound complicated but it's only 4 clicks of the mouse

Originally Posted by Ian Smith

Big Shed
I'm no guru but he way I would do it is to place your conversion factor (i.e.) 25.4 in a cell ..say G1 just to the right of your current array then in cell ..say H1 ..place the formula

= A1*$1

Select this cell , click and drag it to the right as many columns as you have in your first array and then once that is done select that row and click and drag it down the number of rows you have in your first array and bingo - done!!

May sound complicated but it's only 4 clicks of the mouse

Ian, I'm with you up until =A1*$g$1, that gives me a conversion in H1, after that you've lost me, sorry.

I clicked and dragged the number of colums, nothing happens, nor for the rows.

Hi

You need to move the mouse cursor down to the bottom left of the cell and drag the little square box that appears.

Good luck

Andrew

Big Shed - sent you a PM - give me a call - it's far easier to sort it out over the phone

Ian

Originally Posted by Ian Smith

Big Shed - sent you a PM - give me a call - it's far easier to sort it out over the phone

Ian

Done, you're a genius

Thanks very much indeed, that'll save a lot of time.

Also decided to use the new worksheet idea and keep both the inches and mm.

Looks like I'm a bit late, but for the sake of a story I recently made up a one page spreadhseet of manually entered fractions down one column that automatically converted them to decimal in the next column, then to mm in the last. The plan being it is something I can print out to hang in the shed for a quick reference. If I had the thing here I'd upload it. Maybe later in the week if I remember to get it.

I can't view the workbooks attached but in case it hasn't been used, a nifty, simple, little function to convert values between units is (oddly enough) CONVERT.

Also very late but I did the sample file in about 10 seconds.

Insert a new sheet.
Type formula in A1 on new sheet =Sheet1!A1*25.4
Select all cells in use in Row A of the original sheet on the new sheet and type Ctrl + R (Copy right) to copy A1 formula to all required cells in row A.
Then select entire rows A to end of table and press Ctrl + D (Copy Down) to copy formulae to all required rows down the sheet.

i.e. create the formula in one cell then copy it across the row in one action then down the columns in another action. Does the job in three simple moves and preserves the original data.

Only thing is that it returns 0 for empty cells. Two ways around this, formal and informal.

For formal use the folowing formula =IF((Sheet1!A1)="",,(=Sheet1!A1*25.4)) . This tells the system to check whether the cell on the original sheet is blank, to keep the new sheet blank if it is, or apply the conversion if populated.

For informal use, you could go to Tools,Options, View and suppress displaying zeros