Hi All, could somebody take a look and tell me where I'm going wrong. The result in A1 should show 10 but for some reason still shows 9.
Sorry, I can't work out how to add an attachment, the manage attachment button doesn't appear to work and I can't find the "browse" button either?
Last edited by Marvo; 08-19-2012 at 12:18 PM.
Click on "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area to get the "attach file" dialog.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
What value is in A1? Have you got calculation set to manual, if so you may need to change to automatic (or press F9 to re-calculate)
Audere est facere
Still Struggling (Now I can't find "go advanced" though have seen it before)
Sorry, I feel like a right idiot!
---------- Post added at 04:31 PM ---------- Previous post was at 04:23 PM ----------
Found go advanced but the paper clip is doing nothing. Maybe I have something blocking on my computer, I'll try the laptop see if that is any different.
Managed to do it from my laptop
sum(c1:d1)=9.499999999999990000000000000000 so that will round down to 9
try
=ROUND(ROUND(C1,3)+ROUND(D1,3),0)
Last edited by martindwilson; 08-19-2012 at 11:48 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Soory, I don't understand where the 9.49 comes from. Surely 10.4 - 0.9 = 9.5?
It appears to be caused by floating point precision. http://support.microsoft.com/kb/78113
Try
=ROUND(ROUND(C1+D1,1),0)
Yes that works on this example. What have you actually done there?
---------- Post added at 05:05 PM ---------- Previous post was at 04:57 PM ----------
I think I get it. You're telling it to accept just one figure so the 0.49 is forced into 0.5. Is that what you've done Jason?
That is correct, the figures are rounded twice to remove the floating point error (caused in your case by adding a negative value).You're telling it to accept just one figure so the 0.49 is forced into 0.5.
I've used a slighlty different method to martindwilson, but the effect is the same.
The link in my previous reply gives more information.
you could also try it this way
c1=SUMPRODUCT((MOD(COLUMN($E$1:$AS$1),2)=1)*E1:AS1)
d1=SUMPRODUCT((MOD(COLUMN($E$1:$AS$1),2)=0)*E1:AS1)
then round(c1+d1,0) works
That's brilliant jason, I'll go with your formula because it's shorter than Martins.
Much obilged to both of you, I couldn't get my head round where I'd gone wrong. I'll mark the thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks