Solved

Get Average of Numbers in a Column of HTML Table

Posted on 2007-03-21
13
246 Views
Last Modified: 2008-02-01
This simple javascript calculates and displays the sum total of the 6th column in a table (which happens to have a header, then 12 rows of data, then a blank row at the bottom, where the sum of column 6 is displayed):

<script>
 function calcSum(){
      for (j=5;j<6;j++){
            sum=0
            for (i=1;i<13;i++) sum+=1*document.getElementById("numTable").rows[i].cells[j].innerHTML;
            document.getElementById("numTable").rows[i].cells[j].innerHTML=sum
     }
}
</script>

Instead of showing the sum of Column 6, however, I would like to show the mean average.  How would I modify the script to do that?
0
Comment
Question by:Randall-B
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 14

Expert Comment

by:Tchuki
ID: 18764277
<script>
 function calcSum(){
      for (j=5;j<6;j++){
            sum=0
            for (i=1;i<13;i++) sum+=1*document.getElementById("numTable").rows[i].cells[j].innerHTML;
            document.getElementById("numTable").rows[i].cells[j].innerHTML=sum/j // <------------
     }
}
</script>
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 18764404
Shouldn't you need to divide by j*i?

document.getElementById("numTable").rows[i].cells[j].innerHTML
  = Math.round(sum/(j*i), 2)

-r-
0
 

Author Comment

by:Randall-B
ID: 18764415
Tchuki,
   Thanks, but dividing by j gives the wrong math average. For example, if each cell in the column has a "2" in it, the average should be 2.  But this modification gave an average of 4.8 .  Also, what if a cell is blank or simply has &nbsp; ? Then it gives an error of NaN.  How can I avoid that?
0
 
LVL 49

Assisted Solution

by:Roonaan
Roonaan earned 250 total points
ID: 18764435
<script>
 function calcSum(){
      for (j=5;j<6;j++){
            sum= 0;
            ints =0;
            for (i=1;i<13;i++) {
                var value = 1*document.getElementById("numTable").rows[i].cells[j].innerHTML;
                if(!isNaN(value)) { ints++; sum += value;}
             }
             document.getElementById("numTable").rows[i].cells[j].innerHTML
                 = ints > 0 ? sum : 0;
     }
}
</script>

-r-
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 18764474
Back to school for me ...  >.<
0
 

Author Comment

by:Randall-B
ID: 18764528
It seems to work if I divide by (i-1). I think that is because  i  is the number of data rows plus footer row.  Subtracting 1 gives number of data rows only. So this seems to work:  
     . . .     .innerHTML=sum/(i-1)
   I tried to put Roonaan's rounding feature into it, also, like this:
    . . .     .innerHTML=Math.round(sum/(i-1), 2)
but it is rounding to 1 digit, when I actually want two decimal places.  For example, it is rounding to 3 when the average is 2.77829102934 .  How can I round to 2.78 , instead?

     
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Randall-B
ID: 18764601
Roonan,
   Your script appears to add up a sum, instead of calculating an average.  But it works great for ignoring empty cells to avoid the NaN error.
    How would keep that great feature of working around empty cells, but calculate the average instead of the sum?  (And round to 2 decimal places)?  Based on my prior post, I think it has something to do with (i-1), but that would not account for empty cells, which would make the average come out wrong.  Thanks.
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 18764646
Yeah sorry, I was mixed up with other things. Please change:
= ints > 0 ? sum : 0;
To
= ints > 0 ? Math.round(sum/ints, 2) : 0;
0
 

Author Comment

by:Randall-B
ID: 18764709
Roonaan,
    Good, that gives the average, but it is still rounding to an single digit (integer) instead of two decimal places.  For example, it rounded 2.346 to simply 3.  But I would like it to round to 2.35 .  How?  
0
 
LVL 49

Accepted Solution

by:
Roonaan earned 250 total points
ID: 18764777
Then it might be possible that Round does not accept the ,2 argument.

Lets try:

= ints > 0 ? (Math.round(100*sum/ints, 2) / 100) : 0;

Regards

-r-
0
 

Author Comment

by:Randall-B
ID: 18764952
Yes, that works.  So the complete script is:

<script>
 function calcSum(){
      for (j=5;j<6;j++){
            sum= 0;
            ints =0;
            for (i=1;i<13;i++) {
                var value = 1*document.getElementById("numTable").rows[i].cells[j].innerHTML;
                if(!isNaN(value)) { ints++; sum += value;}
             }
             document.getElementById("numTable").rows[i].cells[j].innerHTML = ints > 0 ? (Math.round(100*sum/ints, 2) / 100) : 0;

     }
}
</script>

Thanks!
0
 
LVL 75

Expert Comment

by:Michel Plungjan
ID: 18765959
or just

(sum/ints).toFixed(2)
0
 

Author Comment

by:Randall-B
ID: 18766596
mplungjan:  Thanks. I also appreciate how that code makes it have 2 decimal places even if it would otherwise be a single digit.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Avoid defining the variables in the global scope; trying to define them in a local function scope. Because:   • Look-up is performed every time a variable is accessed.   • Variables are resolved backwards from most specific to least specific scope…
In Part 1 (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/A_7849-Hex-Maze.html) we covered the hexagonal maze basics -- how the cells are represented in a JavaScript array and how the maze is displayed.  In this part, we'…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now