Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Is there any way that I can get this formula to work, taking partial pennies into account? see attached......

    =IF((SUM(D5,H5,L5,P5,T5,X5)=AB5),""Balanced"",""Er ror"")

    [attachment=83321:Error.xls]
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thinking something like

    =IF((SUM(D5,H5,L5,P5,T5,X5)-AB5<>0.0099999999),"Error","Balanced")

    But still reports error. ??

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello VegasNath:
    You can try with "Screen precision" inside to options...

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could also use the Round function

    =IF((SUM(Round(D5,2),Round(H5,2),Round(L5,2),Round (P5,2),Round(T5,2),Round(X5,2)
    )-Round(AB5,2)<>0),"Error","Balanced")

    Round uses the figure in the cell rounded to 2 Decimal Places in this case so should eliminate those small rounding errors.

    Round(SUM(whatever),2)-Round(AB5,2) might work as well
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about something like:
    =IF(ROUND(SUM(D5,H5,L5,P5,T5,X5)-AB5,2)=0,"Balanced","Error")

    Change the 2 to how many decimal points are important to you. This will balance if the difference is <0.005. Increasing the 2 to 3,4,5, changes the number of zeroes to <0.0005, <0.00005, 0.000005, etc

    Steve

  6. #6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •