More Excel Answers
Question Library
Ask a question about Excel
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Tom Ogilvy
Expertise
Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience
Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials
BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
| | |
| |
You are here: Experts > Computing/Technology > Microsoft Software > Excel > Calculations using Visible results of other Calculations
Excel - Calculations using Visible results of other Calculations
Expert: Tom Ogilvy - 11/7/2009
Question
Hi Tom,
I think there will be blindingly simple answer to this but it eludes me at the moment.
I have a column of answers that are based on a simple calculation example:Y5=(C5/100)*G5 The true answer could be 12.5368745 however I have the cell Y5 formated to two decimal places so it shows 12.54. I would then like another calculation to use the 12.54 rather the whole value.
I have tried altering the calculation and answer cells formats, also tried copying the answer column Y and pasting as new column with paste special - values, but it still has the whole answer in the cells.
The reason I need to use the precise values is because I have based a pivot table on this data and the subtotals and totals in the pivot table use the complete numbers not the restricted to two decimal places. So the results are technically absolutely correct but differ from the sum of the individual rows above the totals in the pivot table.
I hope this is understandable.
Regards
Ian
Answer
IAN,
Based on your stated sitation and needs, I would recommend making the stored value and the displayed value by altering your formula to use the round function
Y5: =(C5/100)*G5
becomes
Y5: =Round((C5/100)*G5,2)
Since I understood that you use Y5 as part of the source of a Pivot Table. If the source is actually cells based on Y5, then you could get away with altering the formula that refers to Y5 (and other such cells)
instead of say =Y5+3.22
you could use =round(Y5,2)+3.22
as a conceptual example. You should know best where the rounding needs to occur.
--
Regards,
Tom Ogilvy
Ask a Question
|
|
