Formula help for percent

C

Chad

Helo, I have a report that has a text box with this formula as the Control
Source =Sum(Abs([Shift]=1)*[DT REGULAR]/[EMPLOYEE TIME]) it is giving me the
right percent but the decimal place is in the wrong spot. its goving me for
example (400.5%) instead of 40.05%. I se the text box format to percent and
the Decimal Places to 0 but it is still giving me the wrong answer. Im
looking for just 40%. Any help would be greatly appreciated!!!

Thanks,
Chad
 
J

John Spencer

I think your problem is that you are adding together many percentage
calculations instead of calculating ONE percentage for the group.

=Sum(Abs([Shift]=1)*[DT REGULAR]/[EMPLOYEE TIME]) is calculating a percent
for each record on shift and then adding them all up.

Try the following and see if it gives you what you are attempting to
calculate

=Abs(Sum([Shift]=1)*[DT REGULAR]) / Abs(Sum([Shift]=1) * [EMPLOYEE
TIME])

By the way, both expressions depend on employee time NEVER being equal to
zero.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
C

Chad

Thanks john but it doesnt work, when I run the report it now asks me for an
employee name for some reason but when I change the formula back to what I
had it works like I set it up in the query with a date range only? Any other
sugestions... Thanks!

John Spencer said:
I think your problem is that you are adding together many percentage
calculations instead of calculating ONE percentage for the group.

=Sum(Abs([Shift]=1)*[DT REGULAR]/[EMPLOYEE TIME]) is calculating a percent
for each record on shift and then adding them all up.

Try the following and see if it gives you what you are attempting to
calculate

=Abs(Sum([Shift]=1)*[DT REGULAR]) / Abs(Sum([Shift]=1) * [EMPLOYEE
TIME])

By the way, both expressions depend on employee time NEVER being equal to
zero.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
Helo, I have a report that has a text box with this formula as the Control
Source =Sum(Abs([Shift]=1)*[DT REGULAR]/[EMPLOYEE TIME]) it is giving me
the
right percent but the decimal place is in the wrong spot. its goving me
for
example (400.5%) instead of 40.05%. I se the text box format to percent
and
the Decimal Places to 0 but it is still giving me the wrong answer. Im
looking for just 40%. Any help would be greatly appreciated!!!

Thanks,
Chad
 
J

John Spencer

Did you, by any chance, cut and paste from the newsgroup? If you did
the newsgroup probably introduced a line feed between Employee and Time.
The expression should all be on ONE line.

=Abs(Sum([Shift]=1)*[DT REGULAR])/Abs(Sum([Shift]=1)*[EMPLOYEE TIME])

IF that still asks you for an Employee name then post back with more
details.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks john but it doesnt work, when I run the report it now asks me for an
employee name for some reason but when I change the formula back to what I
had it works like I set it up in the query with a date range only? Any other
sugestions... Thanks!

John Spencer said:
I think your problem is that you are adding together many percentage
calculations instead of calculating ONE percentage for the group.

=Sum(Abs([Shift]=1)*[DT REGULAR]/[EMPLOYEE TIME]) is calculating a percent
for each record on shift and then adding them all up.

Try the following and see if it gives you what you are attempting to
calculate

=Abs(Sum([Shift]=1)*[DT REGULAR]) / Abs(Sum([Shift]=1) * [EMPLOYEE
TIME])

By the way, both expressions depend on employee time NEVER being equal to
zero.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chad said:
Helo, I have a report that has a text box with this formula as the Control
Source =Sum(Abs([Shift]=1)*[DT REGULAR]/[EMPLOYEE TIME]) it is giving me
the
right percent but the decimal place is in the wrong spot. its goving me
for
example (400.5%) instead of 40.05%. I se the text box format to percent
and
the Decimal Places to 0 but it is still giving me the wrong answer. Im
looking for just 40%. Any help would be greatly appreciated!!!

Thanks,
Chad
 
C

Chad

Worked like a charm! Thanks a million....

John Spencer said:
Did you, by any chance, cut and paste from the newsgroup? If you did
the newsgroup probably introduced a line feed between Employee and Time.
The expression should all be on ONE line.

=Abs(Sum([Shift]=1)*[DT REGULAR])/Abs(Sum([Shift]=1)*[EMPLOYEE TIME])

IF that still asks you for an Employee name then post back with more
details.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks john but it doesnt work, when I run the report it now asks me for an
employee name for some reason but when I change the formula back to what I
had it works like I set it up in the query with a date range only? Any other
sugestions... Thanks!

John Spencer said:
I think your problem is that you are adding together many percentage
calculations instead of calculating ONE percentage for the group.

=Sum(Abs([Shift]=1)*[DT REGULAR]/[EMPLOYEE TIME]) is calculating a percent
for each record on shift and then adding them all up.

Try the following and see if it gives you what you are attempting to
calculate

=Abs(Sum([Shift]=1)*[DT REGULAR]) / Abs(Sum([Shift]=1) * [EMPLOYEE
TIME])

By the way, both expressions depend on employee time NEVER being equal to
zero.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Helo, I have a report that has a text box with this formula as the Control
Source =Sum(Abs([Shift]=1)*[DT REGULAR]/[EMPLOYEE TIME]) it is giving me
the
right percent but the decimal place is in the wrong spot. its goving me
for
example (400.5%) instead of 40.05%. I se the text box format to percent
and
the Decimal Places to 0 but it is still giving me the wrong answer. Im
looking for just 40%. Any help would be greatly appreciated!!!

Thanks,
Chad
 

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

Percent help 3
Query and text box help on formula 6
Need help with Report formula 6
Formula in text box on RPT help... 9
Query expression help 11
Abs Formula 4
Help with a range formula 4
Format a Percent 4

Top