PERCENTAGE FORMULA

[QueSpr04]

CR 10
ODBC [RDO] CONNECTION
INFORMIX DATABASE

I HAVE A SCENERIO WHERE I AM WRITING PERCENTAGE FORMULA'S THAT IS DROPPING PENNIES WHEN THEY ARE BEING SUMMED. SAMPLE DATA FROM MY PROGRAM LOOKS LIKE THIS...
TOTAL 5% BALANCE 72% 7% 21%
28.00 1.40 26.60 19.15 1.86 5.59
1.00 .05 .95 .68 .07 .20
6.00 .30 5.70 4.10 .40 1.20
172.00 8.60 163.40 117.65 11.44 34.31
1.00 .05 .95 .68 .07 .20
11.00 .55 10.45 7.52 .73 2.20
2.00 .10 1.90 1.37 .13 .40
1.00 .05 .95 .68 .07 .20
.00 .00 .00 .00 .00 .00
1.00 .05 .95 .68 .07 .20
.00 .00 .00 .00 .00 .00
95.00 4.75 90.25 64.98 6.32 18.95
---------------------------------------------------------
318.00 15.90 302.10 217.49 21.16 63.45

THIS IS THE DATA OUTPUT FROM FOR THE REPORT. THE "5% COLUMN" IS 5% OF THE TOTAL COLUMN. THE "BALANCE COLUMN" IS (TOTAL COLUMN - 5% COLUMN). THE "72% COLUMN" IS 72% OF THE BALANCE. THE "7% COLUMN" IS 7% OF THE BALANCE COLUMN. AND THE 21% COLUMN IS 21% OF THE BALANCE. AND UNDER THOSE COLUMNS UNDERNEATH THE DOTTED LINE IS THE TOTAL FOR EACH RESPECTFUL COLUMN. THE PROBLEM THAT I AM RUNNING INTO IS THE THE NUMBERS IN THE BOLD ARE OFF BY PENNIES. FOR INSTANCE, 2.20 IS REPORTING AS 2.19 ON MY REPORT. AND THE TOTALS AT THE BOTTOM OF THE REPORT ARE ALSO OFF BY PENNIES. 217.49 IS REPORTING 217.51 AND 21.16 IS SHOWING 21.15 AND 63.45 IS SHOWING 63.44. I CALCULATED EACH FIELD SEPERATELY WITH THE SAME FORMULA FOR EACH. BUT FOR SOME ODD REASON, IT STARTED TO DROP PENNIES. DOES ANYONE HAVE A SOLUTION FOR THIS PROBLEM? IF YOU NEED MORE INFORMATION, PLEASE LET ME KNOW.

 

[hilfy]

Are you summing the values down each column or are you taking a percent of your total value (which would be summed from the first column)?

 

[QueSpr04]

I AM SUMMING THE GRAND TOTALS AT THE BOTTOM DOWN EACH COLUMN. BUT AS FAR AS THE NUMBERS IN THE COLUMNS, THEY ARE INDIVIDUALLY FIGURED FROM THEIR OWN FORMULA.

 

[MrBillSC]

It appears that you are rounding your figures in the individual rows. I say this because the columns add up correctly. If you were to calculate 72% column using the balance total, you would get the correct 72% figure, but the column would not add up correctly. If you want the 72% column to be 72% of the balance column, do not round the amount in the individual rows.

I hope this helps,

mrbill

 

[QueSpr04]

HOW CAN I STOP IT FROM ROUNDING UP OR DOWN? IT DOES IT ON ITS OWN. THANKS!

 

[MrBillSC]

I think you would have to display 3 decimal places in the calculated percent columns and the calculated percent totals.

This is probably not ideal, but at least the column will add up and calculate correctly at the footing.

MrBill

 

[MrBillSC]

Another thought for you.
If you don't want to show more than 2 decimal places and don't care about the columns add up correctly, you can have your 72% formula calculation calc to 4 decimal places, but only format to show 2 decimal places:

Round(Balance * .72,4)

If you do this and then subtotal the 72% column it should sum all 4 decimal places and then round to 2 decimal places if you format the footing for 2 decimal places.

It will be odd that the columns don't add up but the calculations across should be correct.

MrBill

 

[QueSpr04]

THANKS MRBILLSC, I DID WHAT YOU SUGGESTED AND IT WORKED FOR SOME OF THE FIELDS BUT THE FIELDS THAT WERE INCORRECT DID NOT WORK BECAUSE FOR SOME REASON, THOSE FIELDS ARE DROPPING PENNIES FROM THEIR TOTAL. IT ALMOST LOOKS AS IF IT IS UN-EXPLAINABLE, BUT I KNOW THAT IT IS SOME KIND OF ARITHMETIC FORMULA THAT CAN STOP THE FIELDS FROM DROPPING PENNIES FROM THEIR TOTAL. I KNOW IT SIMPLE BUT I'M HAVING A BRAIN FREEZE MOMENT RIGHT NOW. DO YOU KNOW OF A FORMULA THAT CAN STOP IT FROM DROPPING PENNIES?

 

[MrBillSC]

You can calc the 72% footing with a formula:

Sum(Balance) * .72

This will properly calc your footing. You can do it for each Percent column.

MrBill

 

[mathblaster]

I have not figured out how to sum the calculated columns, how did you do that?