Help with rounding ? please

[Jess]

I need some help with a rounding formula please, Excel 2007.

This will be dealing with adding and subtracting total feet, then
converting to miles. I've tried all sorts of things, but I'm just not
fluent with Excel

Cell A1 = 5,280
Cell B1 = miles no decimal places showing =B2 (144)
Cell B2 = miles @ 3 decimal places =B3/A1 (144.395)
Cell B3 = total feet = B6+B5+B4 (762,403)
Cell B4 = total feet from previous year 755,457
Cell B5 = total new feet added this year 7,049
Cell B6 = total feet removed this year -103

Cell C1 miles no decimal places showing =C2 (9)
Cell C2 = C3/A1 (9.068)
Cell C3
Cell C4
Cell C5
Cell C6

Cell D1 miles no decimal places showing =D2 (3)
Cell D2 = D3/A1 (3.286)
Cell D3
Cell D4
Cell D5
Cell D6

Cell E1 formula = B1+C1+D1 (157) I need this to be 156 as when
you add 144 + 9 + 3. It is true that when you add, 3.286 + 9.068
+144.395 (156.749) it does round up to 157. I don't want to use
roundup or roundown due to next year it could cause a different
problem. Just needs to total the number that shows in B2, C2, D2.
Please keep it simple for a simple mind.

Thanks,

Jess n FL
To email remove the underscore
(e-mail address removed)

 

[joeu2004]

Cell B1 = miles no decimal places showing    =B2 (144)
Cell B2 = miles @ 3 decimal places           =B3/A1 (144.395) [....]
Cell C1 miles no decimal places showing      =C2 (9)
Cell C2                                      =C3/A1 (9.068) [....]
Cell D1 miles no decimal places showing      =D2 (3)
Cell D2                                      =D3/A1 (3.286) [....]
Cell E1 formula     = B1+C1+D1 (157)
 I need this to be 156 as when you add 144 + 9 + 3.

One of the following normal (non-array) formulas should do the trick,
depending on which intermediate computations above you actually want
to retain.

=SUMPRODUCT(ROUND(B33/A1,0))
=SUMPRODUCT(ROUND(B22,0))
=SUMPRODUCT(ROUND(B11,0))

 

[Jess]

Ron and Joe,

I want to thank both of you for your help! Both formulas will work
for me. I'm sorry for not replying before now, but I'm just finding
time to get back to my work. Even though the formulas work great, it
still didn't fix my problem with the Feds form I have to use in the
end, imagine that.

Thanks and I hope you have a great weekend!

Jess n Florida

I need some help with a rounding formula please, Excel 2007.

This will be dealing with adding and subtracting total feet, then
converting to miles. I've tried all sorts of things, but I'm just not
fluent with Excel

Cell A1 = 5,280
Cell B1 = miles no decimal places showing =B2 (144)
Cell B2 = miles @ 3 decimal places =B3/A1 (144.395)
Cell B3 = total feet = B6+B5+B4 (762,403)
Cell B4 = total feet from previous year 755,457
Cell B5 = total new feet added this year 7,049
Cell B6 = total feet removed this year -103

Cell C1 miles no decimal places showing =C2 (9)
Cell C2 = C3/A1 (9.068)
Cell C3
Cell C4
Cell C5
Cell C6

Cell D1 miles no decimal places showing =D2 (3)
Cell D2 = D3/A1 (3.286)
Cell D3
Cell D4
Cell D5
Cell D6

Cell E1 formula = B1+C1+D1 (157) I need this to be 156 as when
you add 144 + 9 + 3. It is true that when you add, 3.286 + 9.068
+144.395 (156.749) it does round up to 157. I don't want to use
roundup or roundown due to next year it could cause a different
problem. Just needs to total the number that shows in B2, C2, D2.
Please keep it simple for a simple mind.

Thanks,

Jess n FL
To email remove the underscore
(e-mail address removed)

To email remove the underscore
(e-mail address removed)

 

[joeu2004]

Even though the formulas work great, it still didn't fix
my problem with the Feds form I have to use in the end

If you are referring to US forms, which one(s). I might be able to
help you construct Excel formulas that do the computations in the
manner that the forms expect.