Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a table in which row 2 has tons of columns. Each of those columns in row 2 has a varying value in the range of 1-1000000 represented by X in the example below.

How can I add modify the cells in row 2 to look like this:

=X/1000

keeping the value of X, but adding the division of 1000 to each cell?

share|improve this question
Welcome to SuperUser. I edited your question, feel free to revert the changes or edit it again. – Informaficker Sep 8 '12 at 18:14

2 Answers

up vote 0 down vote accepted

The good news is you are just looking to divide by 1000, so we just need to shift the decimal place. You could use a custom format on the cells. Highlight the entire row. Right-click a cell, and select "Format Cells".

Under "Custom", enter the format:

#0"."000

You'll only end up with 3 decimal places, but as long as you're starting with integers, you should be fine.

share|improve this answer
2  
Instead of forcing the location of the decimal point you can use commas after the number format to display the number divided by multiples of 1000. For example, #,##0.000, displays a value divided by one thousand and #,##0.000000,, displays the number divided by one million. (Depending on your locale you may need to switch . and ,) – Mike Fitzpatrick Sep 11 '12 at 5:42
i want a % sign added so i did '#,##0.000,%' and it's not giving the correct results – kittensatplay Oct 4 '12 at 21:30
1  
@kittensatplay, put double quotes around the percentage sign: "%" and that should stop Excel trying to use its regular percentage formatting rules. – Mike Fitzpatrick Oct 5 '12 at 0:31
#,##0.000,"%" did not work, i also tried #,##0.0,"%" and others, i also read up on the custom formmating codes and not sure how to combine them into what is needed -- superuser.com/questions/483630/… – kittensatplay Oct 6 '12 at 5:53
I tired both #0.000,"%" (@MikeFitzpatrick style) and #0"."000"%". Both seem to format in the percent sign. Make sure you have double-quotes around the percent sign. – dangowans Oct 6 '12 at 22:26
up vote 0 down vote

Presumably, this is something you only want to do once, precisely because it makes no sense to have a circular loop in your formulas causing the values to be divided by 1000 every time you recalc the sheet (even supposing Excel would let you do that.)

So here's what I would do: Create a new row and copy a formula dividing the original value above by 1000 into each cell. Copy the new row and then "Paste special ... values" into the original row. Then delete the new row.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.