Sum Total in Percent on Report

L

Laura

I have created a report that gives me subtotals "Summed"
fields. I am having a problem trying to get it to total
by percent, the percent needs to be the difference of two
other summed totals. I tried both of these formulas,
however the first one won't work using "Sum Of", so I
tried the second one, and received an error due to the
percent sign, I remove the Percent sign (on both formulas)
but it still doesn't total correctly.

=IIF([Sum Of LYR YTD SALES]+[Sum Of CUR YTD SALES]=0,0,IIF
([Sum Of LYR YTD SALES]=0,100%,IIF([Sum Of LYR YTD SALES]
<0,(+[Sum Of CUR YTD SALES]-[Sum Of LYR YTD SALES])/[Sum
Of LYR YTD SALES]*-1,(+[Sum Of CUR YTD SALES]-[Sum Of LYR
YTD SALES])/[Sum Of LYR YTD SALES])))

=IIF([LYR YTD SALES]+[CUR YTD SALES]=0,0,IIF([LYR YTD
SALES]=0,100%,IIF([LYR YTD SALES]<0,([CUR YTD SALES]-[LYR
YTD SALES])/[LYR YTD SALES]*-1,([CUR YTD SALES]-[LYR YTD
SALES])/[LYR YTD SALES])))

Does anyone have any suggestions or help, and why won't
access accept the percent sign?

-Thanks,
Laura
 
F

Fons Ponsioen

Why not try to calculate the percentile on the report of
the summed subtotals i.e.;
=Sum(Subtot1)/Sum(Subtot2)
now set the format to percent or if you wish you can
add "/100 & "%"
Hope this helps. Hope I understood your question
correctly.
Fons
 
L

Laura

Thanks Fons I appreciate your response, Unfortunally this
doesn't work. I will try to explain a little better.

I have created a report(which will be a template, so the
data will always change).
In setting up the report I selected Grouping Level by
Salesman. At the end of each salesman the totals are
summed for each column(done automatic by wizard).
Summing the entire percentage column will not give a
correct amount. What I am looking for is the percent
difference between two of the "Summed" amounts(2003 total
vs. 2002 total). In order to do this I placed the formula
in "Control Source". I even tried using a simple formula
eg:=([2002]-[2003])/ABS([2003])and it works but the total
is not right.

What I am thinking is you can't calculate off of
the "Sum" fields, but I am not sure. Logically, if you
wanted to see the total of two named fields you would
have to use the exact name, and the exact names are "Sum
Of 2002" & "Sum Of 2003", but it won't recognize "Sum Of".

Please help again!
Laura
-----Original Message-----
Why not try to calculate the percentile on the report of
the summed subtotals i.e.;
=Sum(Subtot1)/Sum(Subtot2)
now set the format to percent or if you wish you can
add "/100 & "%"
Hope this helps. Hope I understood your question
correctly.
Fons
-----Original Message-----
I have created a report that gives me subtotals "Summed"
fields. I am having a problem trying to get it to total
by percent, the percent needs to be the difference of two
other summed totals. I tried both of these formulas,
however the first one won't work using "Sum Of", so I
tried the second one, and received an error due to the
percent sign, I remove the Percent sign (on both formulas)
but it still doesn't total correctly.

=IIF([Sum Of LYR YTD SALES]+[Sum Of CUR YTD SALES] =0,0,IIF
([Sum Of LYR YTD SALES]=0,100%,IIF([Sum Of LYR YTD SALES]
<0,(+[Sum Of CUR YTD SALES]-[Sum Of LYR YTD SALES])/ [Sum
Of LYR YTD SALES]*-1,(+[Sum Of CUR YTD SALES]-[Sum Of LYR
YTD SALES])/[Sum Of LYR YTD SALES])))

=IIF([LYR YTD SALES]+[CUR YTD SALES]=0,0,IIF([LYR YTD
SALES]=0,100%,IIF([LYR YTD SALES]<0,([CUR YTD SALES]- [LYR
YTD SALES])/[LYR YTD SALES]*-1,([CUR YTD SALES]-[LYR YTD
SALES])/[LYR YTD SALES])))

Does anyone have any suggestions or help, and why won't
access accept the percent sign?

-Thanks,
Laura
.
Click to expand...
.
Click to expand...
 
F

Fons Ponsioen

So, I see you wan the percentile the difference[2002]-
[2003] is of the [2003]
I suspect the error comes from where the textbox with your
formula is placed. Make sure you place it in the group
footer and do not do any summing on this textbox.
Hope this helps
-----Original Message-----
Thanks Fons I appreciate your response, Unfortunally this
doesn't work. I will try to explain a little better.

I have created a report(which will be a template, so the
data will always change).
In setting up the report I selected Grouping Level by
Salesman. At the end of each salesman the totals are
summed for each column(done automatic by wizard).
Summing the entire percentage column will not give a
correct amount. What I am looking for is the percent
difference between two of the "Summed" amounts(2003 total
vs. 2002 total). In order to do this I placed the formula
in "Control Source". I even tried using a simple formula
eg:=([2002]-[2003])/ABS([2003])and it works but the total
is not right.

What I am thinking is you can't calculate off of
the "Sum" fields, but I am not sure. Logically, if you
wanted to see the total of two named fields you would
have to use the exact name, and the exact names are "Sum
Of 2002" & "Sum Of 2003", but it won't recognize "Sum Of".

Please help again!
Laura
-----Original Message-----
Why not try to calculate the percentile on the report of
the summed subtotals i.e.;
=Sum(Subtot1)/Sum(Subtot2)
now set the format to percent or if you wish you can
add "/100 & "%"
Hope this helps. Hope I understood your question
correctly.
Fons
-----Original Message-----
I have created a report that gives me subtotals "Summed"
fields. I am having a problem trying to get it to total
by percent, the percent needs to be the difference of two
other summed totals. I tried both of these formulas,
however the first one won't work using "Sum Of", so I
tried the second one, and received an error due to the
percent sign, I remove the Percent sign (on both formulas)
but it still doesn't total correctly.

=IIF([Sum Of LYR YTD SALES]+[Sum Of CUR YTD SALES] =0,0,IIF
([Sum Of LYR YTD SALES]=0,100%,IIF([Sum Of LYR YTD SALES]
<0,(+[Sum Of CUR YTD SALES]-[Sum Of LYR YTD SALES])/ [Sum
Of LYR YTD SALES]*-1,(+[Sum Of CUR YTD SALES]-[Sum Of LYR
YTD SALES])/[Sum Of LYR YTD SALES])))

=IIF([LYR YTD SALES]+[CUR YTD SALES]=0,0,IIF([LYR YTD
SALES]=0,100%,IIF([LYR YTD SALES]<0,([CUR YTD SALES]- [LYR
YTD SALES])/[LYR YTD SALES]*-1,([CUR YTD SALES]-[LYR YTD
SALES])/[LYR YTD SALES])))

Does anyone have any suggestions or help, and why won't
access accept the percent sign?

-Thanks,
Laura
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
L

Laura

I have it placed in the group footer, and I am not
summing, I am using the formulas I stated below. I am
trying to take two of the totals (2002 & 2003) from the
group footer to get the percent difference.

Can anyone help!
Laura
-----Original Message-----
So, I see you wan the percentile the difference[2002]-
[2003] is of the [2003]
I suspect the error comes from where the textbox with your
formula is placed. Make sure you place it in the group
footer and do not do any summing on this textbox.
Hope this helps
-----Original Message-----
Thanks Fons I appreciate your response, Unfortunally this
doesn't work. I will try to explain a little better.

I have created a report(which will be a template, so the
data will always change).
In setting up the report I selected Grouping Level by
Salesman. At the end of each salesman the totals are
summed for each column(done automatic by wizard).
Summing the entire percentage column will not give a
correct amount. What I am looking for is the percent
difference between two of the "Summed" amounts(2003 total
vs. 2002 total). In order to do this I placed the formula
in "Control Source". I even tried using a simple formula
eg:=([2002]-[2003])/ABS([2003])and it works but the total
is not right.

What I am thinking is you can't calculate off of
the "Sum" fields, but I am not sure. Logically, if you
wanted to see the total of two named fields you would
have to use the exact name, and the exact names are "Sum
Of 2002" & "Sum Of 2003", but it won't recognize "Sum Of".

Please help again!
Laura
-----Original Message-----
Why not try to calculate the percentile on the report of
the summed subtotals i.e.;
=Sum(Subtot1)/Sum(Subtot2)
now set the format to percent or if you wish you can
add "/100 & "%"
Hope this helps. Hope I understood your question
correctly.
Fons
-----Original Message-----
I have created a report that gives me subtotals "Summed"
fields. I am having a problem trying to get it to total
by percent, the percent needs to be the difference of two
other summed totals. I tried both of these formulas,
however the first one won't work using "Sum Of", so I
tried the second one, and received an error due to the
percent sign, I remove the Percent sign (on both formulas)
but it still doesn't total correctly.

=IIF([Sum Of LYR YTD SALES]+[Sum Of CUR YTD SALES] =0,0,IIF
([Sum Of LYR YTD SALES]=0,100%,IIF([Sum Of LYR YTD SALES]
<0,(+[Sum Of CUR YTD SALES]-[Sum Of LYR YTD SALES])/ [Sum
Of LYR YTD SALES]*-1,(+[Sum Of CUR YTD SALES]-[Sum Of LYR
YTD SALES])/[Sum Of LYR YTD SALES])))

=IIF([LYR YTD SALES]+[CUR YTD SALES]=0,0,IIF([LYR YTD
SALES]=0,100%,IIF([LYR YTD SALES]<0,([CUR YTD SALES]- [LYR
YTD SALES])/[LYR YTD SALES]*-1,([CUR YTD SALES]-[LYR YTD
SALES])/[LYR YTD SALES])))

Does anyone have any suggestions or help, and why won't
access accept the percent sign?

-Thanks,
Laura
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
B

Bill Crawford

quote:
in "Control Source". I even tried using a simple formula
eg:=([2002]-[2003])/ABS([2003])and it works but the total
is not right.
Click to expand...
Click to expand...

hmmm shouldn't it be :

=ABS([2002]-[2003])/([2003]) for a positive decimal %, the other way your
probably adding a negative percentage with positive percentages.


Laura said:
I have it placed in the group footer, and I am not
summing, I am using the formulas I stated below. I am
trying to take two of the totals (2002 & 2003) from the
group footer to get the percent difference.

Can anyone help!
Laura
-----Original Message-----
So, I see you wan the percentile the difference[2002]-
[2003] is of the [2003]
I suspect the error comes from where the textbox with your
formula is placed. Make sure you place it in the group
footer and do not do any summing on this textbox.
Hope this helps
-----Original Message-----
Thanks Fons I appreciate your response, Unfortunally this
doesn't work. I will try to explain a little better.

I have created a report(which will be a template, so the
data will always change).
In setting up the report I selected Grouping Level by
Salesman. At the end of each salesman the totals are
summed for each column(done automatic by wizard).
Summing the entire percentage column will not give a
correct amount. What I am looking for is the percent
difference between two of the "Summed" amounts(2003 total
vs. 2002 total). In order to do this I placed the formula
in "Control Source". I even tried using a simple formula
eg:=([2002]-[2003])/ABS([2003])and it works but the total
is not right.

What I am thinking is you can't calculate off of
the "Sum" fields, but I am not sure. Logically, if you
wanted to see the total of two named fields you would
have to use the exact name, and the exact names are "Sum
Of 2002" & "Sum Of 2003", but it won't recognize "Sum Of".

Please help again!
Laura

-----Original Message-----
Why not try to calculate the percentile on the report of
the summed subtotals i.e.;
=Sum(Subtot1)/Sum(Subtot2)
now set the format to percent or if you wish you can
add "/100 & "%"
Hope this helps. Hope I understood your question
correctly.
Fons
-----Original Message-----
I have created a report that gives me
subtotals "Summed"
fields. I am having a problem trying to get it to total
by percent, the percent needs to be the difference of
two
other summed totals. I tried both of these formulas,
however the first one won't work using "Sum Of", so I
tried the second one, and received an error due to the
percent sign, I remove the Percent sign (on both
formulas)
but it still doesn't total correctly.

=IIF([Sum Of LYR YTD SALES]+[Sum Of CUR YTD SALES]
=0,0,IIF
([Sum Of LYR YTD SALES]=0,100%,IIF([Sum Of LYR YTD
SALES]
<0,(+[Sum Of CUR YTD SALES]-[Sum Of LYR YTD SALES])/
[Sum
Of LYR YTD SALES]*-1,(+[Sum Of CUR YTD SALES]-[Sum Of
LYR
YTD SALES])/[Sum Of LYR YTD SALES])))

=IIF([LYR YTD SALES]+[CUR YTD SALES]=0,0,IIF([LYR YTD
SALES]=0,100%,IIF([LYR YTD SALES]<0,([CUR YTD SALES]-
[LYR
YTD SALES])/[LYR YTD SALES]*-1,([CUR YTD SALES]-[LYR
YTD
SALES])/[LYR YTD SALES])))

Does anyone have any suggestions or help, and why won't
access accept the percent sign?

-Thanks,
Laura
.

.

.
Click to expand...
.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

0 and spacing 3
YTD and MTD math in a report. 4
Excel Getting an annualized figure based on YTD in Excel 1
report shows fields with 0.00 vlaues 2
Month and Year to Date on a Report 2
Running total by group 1
Expression used to calculate previous ytd count 4
Report Totals 3

Top