Convert an algorithm to JavaScript

mikky21 · May 3rd, 2011, 10:34 AM

Hi,

Would appreciate it very much if someone could help me with this.
I'm having a little trouble converting the following condition to JavaScript.

((COUNTIF(C20:C21,"n")+COUNTIF(C22:C26,"y"))=3

It looks for values in an Excel sheet in Column 'C' and checks if there are 'y' or 'n' values within them and depending on how many there are it returns true or false.

This is a small section of a larger formula, but I believe if I can get my head around this part of it I can do the rest. Just for reference the rest of the formula is as follows (it sets a priority value):

=IF((COUNTIF(C20:C21,"n")+COUNTIF(C22:C26,"y"))<=2 ,"Low",IF((COUNTIF(C20:C21,"n")+COUNTIF(C22:C26,"y "))=3,"Medium",IF((COUNTIF(C20:C21,"n")+COUNTIF(C2 2:C26,"y"))=4,"Medium",IF((COUNTIF(C20:C21,"n")+CO UNTIF(C22:C26,"y"))=5,"Medium",IF((COUNTIF(C20:C21 ,"n")+COUNTIF(C22:C26,"y"))>5,"High")))))

Hope my request makes sense without causing a headache.
Thank you.

joefawcett · May 3rd, 2011, 11:02 AM

How are you going to identify the cells? For example do they all have individual IDs, a common class name or something?

mikky21 · May 3rd, 2011, 11:06 AM

Apologies I completely forgot.

I'll be using true/false checkboxes instead of cell values, it will be independent of the Excel sheet, done on a website.

So instead of 'y' it would be 'true' for whatever the label is.
There are 7 'C' cells, so 7 checkboxes.

joefawcett · May 3rd, 2011, 11:42 AM

And do they all have IDs, or some way of identifying them?
For example, if the ones you wanted to check for true all had the the class 'class1' then you could use jQuery to count the checked ones:

Code:

alert($(".class1:checked").length);

If you want to do it yourself then you need to:

Loop through each checkbox that needs to be true
Test if the checked propoerty is tru
If it is increment a variable
Repeat for checkboxes that need to be false

mikky21 · May 3rd, 2011, 12:20 PM

Thanks for this Joe,

They will all have their own IDs, and I wont be using jQuery, so it looks like I'll be running through it using your second set of steps.

mikky21 · May 4th, 2011, 05:34 AM

My JavaScripting is pretty weak, would it be possible to run through your explanation on the first segment:

((COUNTIF(C20:C21,"n")+COUNTIF(C22:C26,"y"))=3

Also what do you mean by point 4.

Thank you very much.

joefawcett · May 4th, 2011, 05:53 AM

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title>Count Checkboxes</title>
  <script type="text/javascript">
    function doCount()
    {
      var arrBoxesToBeFalse = ["chk1", "chk2"];
      var iFalseCount = countState(arrBoxesToBeFalse, false);
      alert("False count: " + iFalseCount);
      var arrBoxesToBeTrue = ["chk3", "chk4", "chk5", "chk6", "chk7"];
      var iTrueCount = countState(arrBoxesToBeTrue, true);
      alert("True count: " + iTrueCount);
    }
    function countState(elements, stateToCheckFor)
    {
      var iCount = 0;
      for (var i = 0, l = elements.length; i < l; i++)
      {
        var oTarget = document.getElementById(elements[i]);
        if (oTarget.checked == stateToCheckFor)
        {
          iCount++;
        }
      }
      return iCount;
    }
  </script>
</head>
<body>
  <table>
    <tbody>
    <tr><td><input type="checkbox" id="chk1" /><label for="chk1">C20</label></td></tr>
    <tr><td><input type="checkbox" id="chk2" /><label for="chk2">C21</label></td></tr>
    <tr><td><input type="checkbox" id="chk3" /><label for="chk3">C22</label></td></tr>
    <tr><td><input type="checkbox" id="chk4" /><label for="chk4">C23</label></td></tr>
    <tr><td><input type="checkbox" id="chk5" /><label for="chk5">C24</label></td></tr>
    <tr><td><input type="checkbox" id="chk6" /><label for="chk1">C25</label></td></tr>
    <tr><td><input type="checkbox" id="chk7" /><label for="chk7">C26</label></td></tr>
    </tbody>
  </table>
  <input type="button" value="Count" onclick="doCount();" />
</body>
</html>

mikky21 · May 16th, 2011, 10:48 AM

Worked perfectly.

Thank you for your help.