functions of excel

dalee

New Member
Joined
Jan 26, 2005
Messages
3
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
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

Well-known Member
Joined
Apr 22, 2002
Messages
776
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

Active Member
Joined
Jan 14, 2005
Messages
267
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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. (y)


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. :)
 

Forum statistics

Threads
1,148,524
Messages
5,747,183
Members
424,068
Latest member
Salim khamis

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top