Splitting an expense

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amature Amy! - 25 Jul 2006 18:56 GMT

Amature here!
Expense amount = 1,370.25 in A1
Client #1 owes 1/2 = $685.13 (A2)
Client #2 owes 1/2 = $685.12 (A3)
if I put the formula =A1/2 in A2, it returns $685.125, and then when I go to
formatting it rounds to $685.13 (Correct)
But then, using that same formula in A3, it gives me $685.13, when it should
be $685.12.
In A3, I tried using the formula =A1-A2, but it's still returning $685.13.
Help please - thank you.

Reply to this Message

Bill Ridgeway - 25 Jul 2006 19:03 GMT

The problem is rounding. Unless cells are formatted (in this case to three
decimal places) so that each may be equal you are never going to get it
right.

Regards.

Bill Ridgeway
Computer Solutions

> Amature here!
> Expense amount = 1,370.25 in A1
[quoted text clipped - 8 lines]
> In A3, I tried using the formula =A1-A2, but it's still returning $685.13.
> Help please - thank you.

Reply to this Message

SteveW - 25 Jul 2006 19:05 GMT

Why should A3 give a different answer to a2 ?

Anyway, each is getting 685.125 and yes when rounded using the 2decimal
format they will both show 685.13

Keep the 1/2 cent - all the big corporations do :)

No simple way around this other than format cells with 3 decimal places

Steve

> Amature here!
> Expense amount = 1,370.25 in A1
[quoted text clipped - 9 lines]
> $685.13.
> Help please - thank you.

Signature

Steve (3)

Reply to this Message

SteveW - 25 Jul 2006 19:10 GMT

All you can do, is make a decision that the first company pays the rounded
up figure.
A2 = roundup(a1/2,2)

now a3 = a1-a2

Steve

> Why should A3 give a different answer to a2 ?
>
[quoted text clipped - 20 lines]
>> $685.13.
>> Help please - thank you.

Signature

Steve (3)

Reply to this Message

VBA Noob - 25 Jul 2006 19:07 GMT

Hi,

Could you round up B1 and round down C1

B1 =ROUNDUP($A$1/2,2)
C1 =ROUNDDOWN($A$1/2,2)

VBA Noob

Signature

VBA Noob

Reply to this Message