PDA

View Full Version : Excel Rounding off from REF sheet


kandisnz
Nov 7, 2008, 09:57 AM
My Excel is rounding off or up numbers when I multiply a cell in a sheet to a reference page in the workbook. (ie =(c38*'Bid Sheet'!k441) If I were to enter the number and not the reference cell it comes out correctly. This is simple math and not several decimals. 3*6 very often comes out as 19 as opposed to 18. What can I do? Thank you.

JBeaucaire
Nov 7, 2008, 10:28 AM
Excerpt out a sample of the incorrect data and post it here so we can look at it. Or email me the sheet to keep it off the web if your concerned. Click on my name (to the left) and send the email address.

JB

kandisnz
Nov 7, 2008, 10:40 AM
I attached the file. You'll see the inconsistencies on the Proposal Page totals C column xms the F column. Thank you

kandisnz
Nov 7, 2008, 10:41 AM
Humm. Not so sure it attached

JBeaucaire
Nov 7, 2008, 10:44 AM
Click on my name and send me an email, I'll reply, you can send me the file directly and I'll take a look. If your AMHD account doesn't have an email address in it, include it in the email you send to me.

kandisnz
Nov 7, 2008, 10:52 AM
There is no option to send you an e-mail when I click. [email protected] here is mine. E-mail me and I'll send you what I have. Thank you so much!

kandisnz
Nov 7, 2008, 10:52 AM
That's kandis@bluefabrication geez the spelling:)

JBeaucaire
Nov 7, 2008, 12:38 PM
Got the file. Don't see examples of what you're talking about. Specific cells please... this thing is huge.

kandisnz
Nov 7, 2008, 12:44 PM
I sent this to your personal e-mail as well. I am assuming that you received the entire workbook.
On the Proposal Sheet Line I-41 is a formula multiplying C41 and F41. F41 is referring to the Bid Sheet (page 1 of the entire book) as =+'Bid Sheet'!K1231 if you notice in the total column the total is not correct 72*186.53 is not 13430.05. Well, at least it's not normally. It's off by .11
Thanks for your time. I can chat on gmail.

JBeaucaire
Nov 7, 2008, 03:21 PM
I sent you a background macro that can suppress the behavior you're seeing. I can't spot why it's happening, but my macro should override it.

kandisnz
Nov 7, 2008, 03:25 PM
I have it and am working on it right now.
Thanks again!
Kandis

JBeaucaire
Nov 7, 2008, 11:10 PM
Well, I finally know why the formulas aren't working as expected. It's because the value in that cell is really 186.52841592 and not 186.53.

72 of those 0.00158408's add to to the 11 cents that is missing.

Now, how to change that... change the code in F41 to:

=ROUND( 'Bid Sheet'!K1231, 2)
Change the other cells accordingly. You can trash that macro I wrote you.

kandisnz
Nov 10, 2008, 08:19 AM
Perfect! Thanks~