Problem Calculating Fractions

[shawnkey]

I routinely accept payments on behalf of a client. For my work, I charge a 33 1/3% commission. I have tried to set this up in a spreadsheet to calculate payment (column A) minus commission (column (column A)) equals profit to client (column C).

I have tried several formulas, all of which render outputs that are not always exactly accurate.

As an example, if I enter 100 in Column A, and formula A/3 in B, the output is correct:
100.00 -33.33 66.67

The output is INCORRECT if 50 is entered in Column A; for my purposes the calculation needs to be exact or 16.67:

50.00 -16.66 33.34

To try to resolve this problem, I used the ROUNDUP function. This fixes the problem with an input of 50 but causes a problem with an input of 100.

Finally, I also tried using the TRUNC function. The TRUNC function gives me similar results; most of the outputs are correct, but some are off by a penny.

I've attached a copy of the most recent spreadsheet that I used.

[oeldere]

Maybe like this.

See the attached data in column N O P

[mehmetcik]

In F3

Please Login or Register  to view this content.

In G3

Please Login or Register  to view this content.

[gmr4evr1]

Another way, put this in E3 and copy down

Please Login or Register  to view this content.

Not sure which method is more accurate.

[MarvinP]

Have you tried the formula of just Round()?

Like

=Round(A1,2)

[shawnkey]

Again, the formula that requires a division by 3 (X/3) works sometimes but not all the time.

For example, if I enter 2401.58, I need a result to return of $800.43 (2401.58*.3333). If I divide 2401.58 by 3, I get $800.53.

Similarly, if I enter 2031.31, I need a result to return of $677.04. Dividing 2031.31 by 3, I get $677.10.

[shawnkey]

Gmr4evr1, I tried the TRUNC formula you provided in several formats, none of which worked:

=TRUNC(-E3*0.3333,2)
=TRUNC(-E3*0.3333,3)
=TRUNC(-E3*0.3332,2)

[gmr4evr1]

My bad, I told you to put it in the wrong cell. It goes in F3, not E3. All I did was take the formula you had and changed the ,3 to ,2 and it seemed to round column G (Net Amount).