Excell formula help needed

  tonyq 08:07 25 Aug 2019
Answered

Hi all, I have a list of numbers eg 1 to 50 in column "A" that I need 90% of each number then rounded up or down to a full number in column "B",

  wiganken2 09:06 25 Aug 2019

Assuming the first number is in cell A1 then right-click in column B to the right of it. That's, cell B1.
Select 'Format Cells' from the context menu.
Choose 'Number' tab then choose 'Number' in the list.
Change the 'Decimal places' to zero '0'. This formats the cells contents as a 'number' to '0' decimal places (a whole number).
In that same cell type (without quote marks) "=A1*0.9" and press 'Enter' key.
This gives you your first whole 90% number in cell B1. Cell B1 is now formatted and contains the formula you need to be 'copied' to other cells below it. I say 'copied' but in actual fact the cell references will get changed during the 'dragging' procedure that follows.
Now select cell B1 and move your cursor to the bottom right of that cell and you will see a cross (+) appear. When you see this cross (+) hold the left click on it and drag the cursor (keeping the left-click pressed) down through all the rows you want the formatting and formula to be 'copied' to. You should see all the 90% 'whole' numbers appear in column B.

Hope it helps.

  tonyq 15:26 25 Aug 2019
Answer

wiganken2 Thank you for the reply.I would like to round up/down the 90% numbers

eg 5.0 5.1 5.3.5.4 rounded down to 5

   5.5 5.6 5.7 5.8 5.9 rounded up to 6

is this possible ? thanks again.

  hastelloy 15:58 25 Aug 2019

Wiganken2 said "Select 'Format Cells' from the context menu. Choose 'Number' tab then choose 'Number' in the list. Change the 'Decimal places' to zero '0'. This formats the cells contents as a 'number' to '0' decimal places (a whole number)."

This will round up/down as you want.

  tonyq 16:10 25 Aug 2019

hastelloy, I thought I had Changed the 'Decimal places' to zero '0' but seemingly not. I have now changed it and all is working ok. Thank you both once again.

What is Markdown?

Markdown lets you add more formatting to your post. Simply type in your post and it will display as written.

If you wish to add bold or italic characters, add a hyperlink to another website, a heading or a horizontal line, simply use the relevent icons above the text input field.

A preview of your post will appear in the grey box below. If you make a change and you're not happy, simply press the back arrow icon to undo.

Post a Reply

4500

Elsewhere on IDG sites

How to watch the Huawei Mate 30 launch live: Munich, September 19

Create live VFX

iOS 13 release date, time & new features

Comparatif : Apple Watch Series 5 vs Series 4