stop exel from rounding
 

hi we have a simple formula to work out employees pay (hourly rate x hours
worked = gross amount) however currently exel is not recognising a 6 as a 6
in the final total eg 520.16 +520.10 should = 1040.26 however it is showing
1040.15
also if .11cents shows it rounds it to 0 and 520.16=520.16 the total shows
1040.11 can someone please help i have not set it up to round up or down

stop exel from rounding
 

If the hourly rate is in Cell A1 and the Hours are in Cell B1 then use this
formula.
=Round(A1*B1,2)


"adelaide" wrote:

hi we have a simple formula to work out employees pay (hourly rate x hours
worked = gross amount) however currently exel is not recognising a 6 as a 6
in the final total eg 520.16 +520.10 should = 1040.26 however it is showing
1040.15
also if .11cents shows it rounds it to 0 and 520.16=520.16 the total shows
1040.11 can someone please help i have not set it up to round up or down

stop exel from rounding
 

Post the formulas that you are using. Formatting cells does not change the
underlying value that formulas would use, but that would not account for a
0.1 discrepancy when adding only two numbers.

Jerry

"adelaide" wrote:

hi we have a simple formula to work out employees pay (hourly rate x hours
worked = gross amount) however currently exel is not recognising a 6 as a 6
in the final total eg 520.16 +520.10 should = 1040.26 however it is showing
1040.15
also if .11cents shows it rounds it to 0 and 520.16=520.16 the total shows
1040.11 can someone please help i have not set it up to round up or down

stop exel from rounding
 

WAGES SHEET FOR WEEK ENDED 02.06.06
hope this helps
FULL NAME O/TIME HOURS NORMAL Overtime GROSS AMP TAX CASH Misc
HOURS WAGE Amount due SUPER Deduction DUE Deduction
15 =SUM(D5*C41) =SUM(C5*E41) =SUM(F5+E5) 47 =SUM(G5-I5-H5)
38 =SUM(D6*C42) =SUM(C6*E42) =SUM(F6+E6) 185 =SUM(G6-I6-H6)
24 =SUM(D7*C43) =SUM(C7*E43) =SUM(F7+E7) 42 =SUM(G7-I7-H7)
0 38 =SUM(D8*C44) =SUM(C8*E44) =SUM(F8+E8) 49 93.5 =SUM(G8-I8-H8)
0 38 =SUM(D9*C45) =SUM(C9*E45) =SUM(F9+E9) 54.63 112.37 =SUM(G9-I9-H9)
16 =SUM(D10*C46) =SUM(C10*E46) =SUM(F10+E10) 58 =SUM(G10-I10-H10)
0 38 =SUM(D11*C47) =SUM(C11*E47) =SUM(F11+E11) 147.68 =SUM(G11-I11-H11) =SUM(K11-M11) 30.32 C/Sup
0 38 =SUM(D12*C48) =SUM(C12*E48) =SUM(F12+E12) 86.6 =SUM(G12-I12-H12)
0 38 =SUM(D13*C49) =SUM(C13*E49) =SUM(F13+E13) 96 =SUM(G13-I13-H13)
=SUM(D14*C50) =SUM(C14*E50) =SUM(F14+E14) 0 =SUM(G14-I14-H14)
0 38 =SUM(D15*C51) =SUM(C15*E51) =SUM(F15+E15) 79.1 =SUM(G15-I15-H15)
0 38 =SUM(D16*C52) =SUM(C16*E52) =SUM(F16+E16) 86.6 =SUM(G16-I16-H16)
0 38 =SUM(D17*C53) =SUM(C17*E53) =SUM(F17+E17) 86.6 =SUM(G17-I17-H17)
0 38 =SUM(D18*C54) =SUM(C18*E54) =SUM(F18+E18) 86.6 =SUM(G18-I18-H18)
0 38 =SUM(D19*C55) =SUM(C19*E55) =SUM(F19+E19) 118.37 =SUM(G19-I19-H19)
0 =SUM(D20*C56) =SUM(C20*E56) =SUM(F20+E20) =SUM(G20-I20-H20)
0 =SUM(D21*C57) =SUM(C21*E57) =SUM(F21+E21) =SUM(G21-I21-H21)
=SUM(D22*C58) =SUM(C22*E58) =SUM(F22+E22) =SUM(G22-I22-H22)
=SUM(D23*C59) =SUM(C23*E59) =SUM(F23+E23) =SUM(G23-I23-H23)
=SUM(D24*C60) =SUM(C24*E60) =SUM(F24+E24) =SUM(G24-I24-H24)
=SUM(D5:D24)
TOTALS =SUM(G5:G24) =SUM(H5:H24) =SUM(I5:I24) =SUM(K5:K24)
Less Misc deduction _ =SUM(M5:M24) C/sup
Less Misc deduction _ 0
Less Misc deduction _ 0
TOTAL =SUM(K26-K27-K28-K29)
P/CASH + 0
TOTAL =SUM(K30+K31) CASH DUE






FULL NAME H/RATE O/TIME
RATE EMPLOYER SUPER
25.666666 =SUM(C41)/2+C41
22 =SUM(C42)/2+C42
14.708333 =SUM(C43)/2+C43
14.3289 =SUM(C44)/2+C44 49
15.973684 =SUM(C45)/2+C45 54.63
14.375 =SUM(C46)/2+C46
18.421052 =SUM(C47)/2+C47 80.76
13.778947 =SUM(C48)/2+C48 47.12
14.605263 =SUM(C49)/2+C49 49.95
14.41666 =SUM(C50)/2+C50
13.186842 =SUM(C51)/2+C51 45.09
13.778947 =SUM(C52)/2+C52 47.12
13.778947 =SUM(C53)/2+C53 54.49
13.778947 =SUM(C54)/2+C54 47.12
13.778947 =SUM(C55)/2+C55 0
=SUM(C56)/2+C56 0
=SUM(C57)/2+C57 0
=SUM(C58)/2+C58 0
=SUM(C59)/2+C59 0
=SUM(C60)/2+C60 0


"adelaide" wrote:

hi we have a simple formula to work out employees pay (hourly rate x hours
worked = gross amount) however currently exel is not recognising a 6 as a 6
in the final total eg 520.16 +520.10 should = 1040.26 however it is showing
1040.15
also if .11cents shows it rounds it to 0 and 520.16=520.16 the total shows
1040.11 can someone please help i have not set it up to round up or down

stop exel from rounding
 

"adelaide" wrote:
hope this helps

Not really; there is no context: Where do these formulas go. Which two
cells display as 520.16 and 520.10 but add to 1040.15? ...

One observation is there is much unnecessary use of the SUM function
=SUM(D5*C41) can be written more simply as =D5*C41
=SUM(F5+E5) can be written more simply as =F5+E5
=SUM(C41)/2+C41 can be written more simply as =C41/2+C41
...
Only formulas like =SUM(D5:D24) really need the SUM function.

Jerry

stop exel from rounding
 

On Fri, 07 Jul 2006 03:20:02 +0100, Jerry W. Lewis
wrote:

"adelaide" wrote:
hope this helps

Not really; there is no context: Where do these formulas go. Which two
cells display as 520.16 and 520.10 but add to 1040.15? ...

I guessed this was a typo and should have been 1040.25
520.155 + 520.095 would round up to the numbers shown and give that total

One observation is there is much unnecessary use of the SUM function
=SUM(D5*C41) can be written more simply as =D5*C41
=SUM(F5+E5) can be written more simply as =F5+E5
=SUM(C41)/2+C41 can be written more simply as =C41/2+C41
...
Only formulas like =SUM(D5:D24) really need the SUM function.

users learn something and get carried away with it's use, but why anyone
would write sum(c41) defeats me :)


--
Steve (3)