Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access 2003:  Math question....Calculating a percentage

Posted on 2010-08-26
14
Medium Priority
?
498 Views
Last Modified: 2012-05-10
Hi EE,

I got help last year from EE in how to calcuate percentages.

A student can have one of the following grades in a course:
------------------------------------------------------------------
H - honors
HP - high pass
P - pass

Using a cross tab query, calculated (number of student) totals for each grade for a course
for ex:

 course                 H       HP       P
=============================
MEDI 1107             26     41      102      <-- break down of how many students scored a particular grade

What the snippet below does is convert the total of students below to percentages.
            26+41+102 = 169

            26/169  * 100 = 15.38
            41/169  * 100 =  24.26
           102/169  * 100 =  60.35

      Notice how the decimals portion is less than .50,
              so the results of the calc are:
                         15%
                          24%
                          60%
              that totals 99%

         Is there any way to get the percentages to total 100%  or is this to be expected in math?

tx for your thoughts and ideas, sandra



lngHCount = CLng(Nz(Me.txtHCount, 0))
lngHPCount = CLng(Nz(Me.txtHPcount, 0))
lngPCount = CLng(Nz(Me.txtPcount, 0))


lngTotal = lngHCount + lngHPCount + lngPCount

If lngTotal > 0 Then
    lngHpct = (lngHCount / lngTotal) * 100
Else
    lngHpct = 0
End If

If lngTotal > 0 Then
   lngHPpct = (lngHPCount / lngTotal) * 100
Else
   lngHPpct = 0
End If

If lngTotal > 0 Then
   lngPpct = (lngPCount / lngTotal) * 100
Else
   lngPpct = 0
End If


Me.txtHpct = lngHpct
Me.txtHPpct = lngHPpct
Me.txtPpct = lngPpct
0
Comment
Question by:mytfein
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 11

Expert Comment

by:Runrigger
ID: 33533890
when rounding to integer percentages, that is what you are going to get in this case, its a rareity for percentages to work out like that though!
0
 
LVL 40

Accepted Solution

by:
als315 earned 288 total points
ID: 33533910
I think the only opportunity to get 100% is add this 1 to the value with maximal decimal part. In your example it will be 15.38. Change it to 16 and you will have minimal deviation from reality.
0
 
LVL 3

Assisted Solution

by:PHaddock
PHaddock earned 856 total points
ID: 33533925
You can't do it.  You also have to be careful what happens with the rounding  ie does .05 round up or down?

What you could do is make the last percentage the difference between 100 and the sum of the other two percentages.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 288 total points
ID: 33533934
It's always a risk with rounding. And it's not that rare, Runrigger. Just imagine that you have 1/3 exactly distributed. Each would be 0.3. Rounding to integer would be 0, instead of 1. This applies in many scales. There is no way to avoid this, except programatically checking the sum, and if there's one missing, then check the highest decimal and add to that. In your case, it would add to H total.
0
 

Author Comment

by:mytfein
ID: 33533957
Hi RunRigger,

so the calcs are correct, even though it's odd
   (other class total to 100%, btw)

i googled this:
     http://msdn.microsoft.com/en-us/library/ck4c5842(VS.85).aspx

so the clng function is doing what the article above says, since decimal is less than .50,
does not round up the integer portion, just leaves as is....

pls confirm... tx, s
0
 
LVL 11

Assisted Solution

by:Runrigger
Runrigger earned 568 total points
ID: 33533965
You could use a declaration of "double" instead of long and round the initial calculation to 2 decimal places.

assign the integer value to the forms text box (besure to round the insteger value from the double, that way, you may get a round up for the decimals greater than 0.5) and retain the decimal in an additional variable, do that for all three values.

At the end of the above bit of code above, add all three "integer" values and if not equal to 100, then simply add one/subtract to the form's text box where its respective decimal portion was the greatest (or least, if subtracting)!
0
 
LVL 11

Assisted Solution

by:Runrigger
Runrigger earned 568 total points
ID: 33534004
Cluskit, I concede, I am not a statistician.

We are essentially advising Sandra the same thing, programmatically checking the total and making a corrective add/subtract, she will however need to change variable decalaration to double in order to check the decimal value.
0
 

Author Comment

by:mytfein
ID: 33534020
Hello als315
         Phaddock
         Cluskitt,

Thx for writing....

It's very clever of all of you with the suggestion to pgmatically adjust the percentage when the
total is less than 100.

What i am understanding is that i just adjust the number with the highest decimal portion.
Is it possible to explain why the number with the highest decimal portion, and not the number with
the highest INTEGER portion...

tx all very much, s
0
 
LVL 3

Assisted Solution

by:PHaddock
PHaddock earned 856 total points
ID: 33534084
Personally I usually arbitrarily make it the third one that gets adjusted (in your example) because it's easy to write into a query because you don't need to devise a way to determine which of the three has the highest decimal before you adjust it.

The reason it is slightly more correct to adjust the highest decimal is because you are always making a smaller change to the numbers as a percentage of each number itself that gets changed.
0
 

Author Comment

by:mytfein
ID: 33534151
Hi RunRigger,

Thx for alerting me to work with DOUBLE variables....
Question, pls:
            ====>  HOW do you isolate  the decimal part of the double result
            to get  .38   of 15.38

In the code window is how would modify the code, but got stuck on the above question
tx, s


So would create another 3  variables:

dim dblHCount  as double
dim dblHPCount as double
dim dblPCount  as double

then i would execute the exising logic like this:

lngHCount = CLng(Nz(Me.txtHCount, 0))
lngHPCount = CLng(Nz(Me.txtHPcount, 0))
lngPCount = CLng(Nz(Me.txtPcount, 0))


lngTotal = lngHCount + lngHPCount + lngPCount

If lngTotal > 0 Then
    lngHpct = (lngHCount / lngTotal) * 100
Else
    lngHpct = 0
End If

If lngTotal > 0 Then
   lngHPpct = (lngHPCount / lngTotal) * 100
Else
   lngHPpct = 0
End If

If lngTotal > 0 Then
   lngPpct = (lngPCount / lngTotal) * 100
Else
   lngPpct = 0
End If

==== add this logic

lngTotalPercent = lngHpct + lngHPpct + lngPpct
If lngTotalPercent < 100 then
   dblHCount = (Nz(Me.txtHCount, 0))
   dblHPCount = (Nz(Me.txtHPcount, 0))
   dblPCount = (Nz(Me.txtPcount, 0))

====>  HOW do you isolate  the decimal part of the double result
            to get  .38   of 15.38
end if

Open in new window

0
 
LVL 3

Assisted Solution

by:PHaddock
PHaddock earned 856 total points
ID: 33534212
This one originally came from http://www.freevbcode.com/ShowCode.Asp?ID=427

Public Function DecimalPortion(Number As Double) As Double
  Dim lPos As Long

  lPos = InStr(1, Number, ".")
  If lPos > 0 Then
    DecimalPortion = Val(Mid(Number, lPos))
  Else
    DecimalPortion = 0
  End If

End Function
0
 

Author Comment

by:mytfein
ID: 33534257
Thx sooo much LPaddock...

ok, will go work on it...

tx everyone, s

0
 
LVL 40

Expert Comment

by:als315
ID: 33534437
"Is it possible to explain why the number with the highest decimal portion, and not the number with
the highest INTEGER portion..."

You have good idea - take maximum from decimal part divided to value.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33535369
>> Is it possible to explain why the number with the highest decimal portion, and not the number with
the highest INTEGER portion

Because, statistically, that is the one closest to the next integer. Accounting for ratio, the highest integer would be the first, but with the current sample universe, the highest decimal is the closest to 1.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question