functions of excel

[dalee]

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?

 

[Greg Truby]

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

 

[jimbojones]

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

 

[KenWright]

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

 

[Zack Barresse]

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.

 

[Greg Truby]

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,

 

[Zack Barresse]

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.