show a formula to a number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report with the following columns
[InvoiceAmount], [Percentage], [Subtotal], [GST], [QST], [Total]
The invoice amount is multiplied by the percentage which gives the subtotal
which is then multiplied by the GST, then the amount of the subtotal and the
GST is multiplied by the QST, which gives me the total.
The [Subtotal], [GST], [QST] and [Total] control boxes contain formulas.
When I try to do a total in my report footer for these items I get 0, I
believe that's because there are no actual number in the control boxes, only
formulas.
How can I work around this so that I have totals showing up in my report
footer.
Thanks
 
Jeannie

If I recall correctly, you have to "do the math" over again to get the
footer value. I believe you've correctly identified the issue -- the
controls only contain formulas, not values.
 
I have tried that, and it works on the reports that do not have the
percentage control, however, when I try to do a sum of the percentage so that
I can multiply the InvoiceAmount and then get all the other values I require,
I get the weirdest numbers. My other values are calculated with formulas
such as:
=[TotalSubTotal]*0.07 for the GST, which works fine, but what kind of
formula am I supposed to put in to calculate the total of percentages
throughout the report without getting a number such as 5980%.
Thanks for your help
Jeannie
 
You are right, I have to do the math over, however, the problem resides with
my reports that have the percentage control box, the other reports which do
not have this control work fine.
Any idea for a formula that can I put in to do a sum of all the percentages
throughout the report (which will not bring back something like 5,800%) which
I will then be able to redo the math with.
Thanks
Jeannie
 
Jeannie

If I recall my stats and math correctly, you can't "add" percentages to come
up with any meaningful number. After all, if 50% of a group of 2 answered
"yes", and 25% of a group of 1,000,000 answered "yes", the overall
percentage answering "yes" is only a hair above 25%!

Can you go back to the underlying numbers, add them, then calculate your
overall percentage?

Or did I misunderstand?
 
You do understand and I thank you for your help. Here it is, this is my
partial report

Amount Payable Subtotal GST QST Total
$250.00 10% $25.00 $1.75 $2.01 $28.76
$75.00 50% $37.50 $2.63 $3.01 $43.13
$60.00 50% $30.00 $2.10 $2.41 $34.51
$100.00 50% $50.00 $3.50 $4.01 $57.51

$485.00 29% $142.50 $9.98 $11.44 $163.91

I calculated manually in Excel that the percentage in the report footer
should be 29.382% to get the amount of $142.50 in my report footer, however,
I have absolutely no idea how to come to that number since in Excel I did it
by just typing in numbers until I got the right one. I am really crappy at
math.
Would there be any way to have Access show that the numbers in the subtotal
column are in fact not formulas but numbers, I tried typing
==Val([InvoiceAmount]*([Percentage])) in the control boxes, but that didn't
work.
Thanks again for all your help
Jeannie
 
Well, since you brought it up, is there a reason you couldn't/shouldn't run
your query to get the raw values, then export to Excel to finish?

Does your query include the Amt, and the Payable Percentage? If so, you
could have your query also calculate the Amt Payable (your "subtotal"), your
GST, your QST, and your "Total".
 
I think that the formula to get the aggregate percentage would probably be
something like:

=Sum(Amount * Payable)/Sum(Amount)

That assumes that those are your field names and that payable is a number
displayed as a percentage. Otherwise you may have to adjust things. Also, you
may end up with a small accuracy error if your numbers are of the type Double or Single.
You do understand and I thank you for your help. Here it is, this is my
partial report

Amount Payable Subtotal GST QST Total
$250.00 10% $25.00 $1.75 $2.01 $28.76
$75.00 50% $37.50 $2.63 $3.01 $43.13
$60.00 50% $30.00 $2.10 $2.41 $34.51
$100.00 50% $50.00 $3.50 $4.01 $57.51

$485.00 29% $142.50 $9.98 $11.44 $163.91

I calculated manually in Excel that the percentage in the report footer
should be 29.382% to get the amount of $142.50 in my report footer, however,
I have absolutely no idea how to come to that number since in Excel I did it
by just typing in numbers until I got the right one. I am really crappy at
math.
Would there be any way to have Access show that the numbers in the subtotal
column are in fact not formulas but numbers, I tried typing
==Val([InvoiceAmount]*([Percentage])) in the control boxes, but that didn't
work.
Thanks again for all your help
Jeannie

Jeff Boyce said:
Jeannie

If I recall my stats and math correctly, you can't "add" percentages to come
up with any meaningful number. After all, if 50% of a group of 2 answered
"yes", and 25% of a group of 1,000,000 answered "yes", the overall
percentage answering "yes" is only a hair above 25%!

Can you go back to the underlying numbers, add them, then calculate your
overall percentage?

Or did I misunderstand?

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Back
Top