# functions of excel

#### dalee

##### New Member
I have divided column (a) of numbers and formatted the cells to accounting and 2 decimal places. After dividing the column by 0.9 the value(s) in the formula bar of most cells are several decimal places long, though only show two decimal places on the spread sheet. I would like the actual value in the formula bar to round off to two decimal places as show on the spread sheet- so it correctly computes column (b) whose value is based on a formula of multiplication of quantity times the cell next to it. Can I format the paste special option to round to two decimals? Or is there another way to accomplish this?

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You can either use something like the Round() function. Or else you can go Tools | Options | Calculation (tab) | Precision as displayed (checkbox) but beware that using the Precision as displayed impacted all cells in the workbook, not just a specified range.

As for the other: http://www.mrexcel.com/board2/viewtopic.php?t=127014

Hi,

Can you not leave column A with it's current values and change the formula in column B. When you reference A round it off in the formula.

You don't say what column B does but say it multiplies by 2 then formula in B would be:
=Round(A1,2)*2

Otherwise just =Round(A1,2) in column B then copy paste special values into column A.

?

James

The following will sum values in the range A1:A20 as though they were rounded to 2 decimal places.

=SUM(ROUND(A1:A20,2))

Array entered using CTRL+SHIFT+ENTER

As a side not to Ken's solution, which is quite lovely, if you enter the formula just using Enter, then copy down, you will still be allowed to edit individual cells of data. Whereas trying to edit only part of an array will not meet well. Depends on your needs actually, but is a good thing to keep in mind.

Also, to go to the current array, remember your F5 shortcut key (the GoTo command, Edit --> Goto). From there, use Special --> Current Array. This can be quite a helpful tool, especially when using array formulas.

Hiya Zack,

Ken's array formula is only going to be in one cell. You can still edit any of the input cells. If you copied that array formula to act on other cells its CurrentArray would still be one cell, so not quite sure how your cautionary comment would apply in this situation. Was your comment meant in an "in general" kind of context? Or am I missing something?

Also - assume you're aware that Ctrl+/ is the hotkey for Edit|GoTo... / Ctrl+G / F5 & |Special...|Current Array

Regards,

Yeah, guess it would be an 'in general' context. Doesn't really tackle the OP's exact situation. Just thought I'd throw in a cautionary tale that has snuck up on me in the past. I should not have made the comment; I apologize for confusing the issue.

And yes, I am aware of the hot keys. I try to make it a habit to stay with the dialog boxes for the most part though. Thanks anyway.

Replies
5
Views
372
Replies
2
Views
260
Replies
7
Views
383
Replies
3
Views
398
Replies
3
Views
491

1,219,791
Messages
6,150,285
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back