A Cluster Analysis tool for the JADE Excel Add-in

code.js

//0511e31d0a461f2c2b8d38b60bf81303
async function auto_exec(){
  Jade.set_css(gist_files['style.css'])
  tag("tools-body").innerHTML=gist_files['tool.html']
  const tool_data = await jade.read_object_from_workbook(window.active_tool)
  console.log("tool_data",tool_data)
  for(const[key,value] of Object.entries(tool_data)){
    console.log(key, value)
    tag(key).value=value
  }

}

async function save_props(){
  const the_object={result:"it works with the active tool"}
  await jade.save_object_to_workbook(the_object, window.active_tool)
}

async function read_props(){
  const obj = await jade.read_object_from_workbook(window.active_tool)
  console.log("obj",obj)
}



function default_value(input){
  if(!input.value){
    console.log("getting address of active cell")
    Excel.run(async (excel) => {
      let rng = excel.workbook.getSelectedRange();
      rng.load("address");
      await excel.sync();
      input.value = rng.address.split("!")[1]
  })
}
}


async function goal_seek(){
  const set = tag("set-cell").value
  const value = tag("to-value").value
  const change = tag("change-cell").value
  await jade.save_object_to_workbook({
    "set-cell":set,
    "to-value":value,
    "change-cell":change
    }, window.active_tool)

  //set: the address of the target cell, value: the target value for the cell, change: the address of a precedent cell that will change to achieve the goal
  Excel.run(async function(excel){
    
  //get the active worksheet and initialize variables
  const sheet = excel.workbook.worksheets.getActiveWorksheet()
  const changing_cell = sheet.getRange(change)
  const set_cell = sheet.getRange(set)
  const to_value = value
  changing_cell.load('values')

  await excel.sync()

  const initial_change_value = changing_cell.values[0][0]

  //Set initial guess
  let initial_guess = initial_change_value
  let results = []//array of all result_objects
  let interval_found = false
  let upper_bound
  let lower_bound

  
  for(let j=0;j<=25;j++){//This will provide up to 1000 guesses at the interval boundaries, starting with an initial guess, the guesses change 25 times to reflect what is known about a potential solution.
    if(results.length!==0) initial_guess = results[0].guess
    
    results = await add_guesses(initial_guess, results, changing_cell, set_cell)

    //results.sort((a, b) => Math.abs(a.result)-Math.abs(b.result));
  
    for(let k=1;k<results.length;k++){
      if(results[k].sign!==results[k-1].sign){
        interval_found = true
        if(results[k].guess>results[k-1].guess){
          upper_bound = results[k].guess
          lower_bound = results[k-1].guess
        }else{
          upper_bound = results[k-1].guess
          lower_bound = results[k].guess
        }
        break
      }
    }
    
    if(interval_found) break
    
  }


  if(interval_found){
    const epsilon = 0.00001
    let mid_point
    let mid_point_result
    let lower_bond_result
    mid_point = (upper_bound+lower_bound)/2
    
    while(upper_bound-lower_bound>=epsilon){
      // check to make sure the mid_point value doesn't produce the target
      mid_point_result = await try_value(changing_cell,mid_point,set_cell) - to_value

      if(Math.abs(mid_point_result)<epsilon) break

      upper_bound_result = await try_value(changing_cell,upper_bound,set_cell) - to_value      
      //decide the side of the mid_point to process
      
      if(upper_bound_result*mid_point_result>=0){//the interval is not between the upper bound and the midpoint
        upper_bound = mid_point
      }else{//the interval is between the upper bound and the midpoint
        lower_bound = mid_point
      }
      mid_point = (upper_bound+lower_bound)/2

    }
    tag("pre-output").innerHTML = `Solution found when ${change} is ${Math.round(mid_point*100000)/100000}`
  }else{
    tag("pre-output").innerHTML =  `No solution was found after ${results.length+1} iterations`
  }
  //reset changing cell value
  changing_cell.formulas = initial_change_value

    async function try_value(change_cell, value, result_cell){
      //change_cell is the address where the change is to be made, value is the change to be made, result cell contains the value to be returned.
      change_cell.formulas = value
      result_cell.load('values')
      await excel.sync()
      return result_cell.values[0][0]
    }

    async function add_guesses(initial_guess, results, changing_cell, set_cell){
    //search for intervals around initial guess
      for(let i = -5; i<=5; i=i+0.5){
        let guess_factor
        let guess
        let result
        result_sign = 0
          
        if(results.length>0) result_sign = results[0].sign
        guess_factor = 10**i
          
          guess = initial_guess - initial_guess * guess_factor
          result = await try_value(changing_cell,guess,set_cell)
          if(typeof result === "number") {
            result = result - to_value
            result_object = {guess: guess, result: result, sign: result/Math.abs(result)}
            results.push(result_object)
            if(result_sign===0){
              result_sign = result_object.sign
            }else{
              if(result_sign!==result_object.sign) return results
            }
          }

          guess = initial_guess + initial_guess * guess_factor
          result = await try_value(changing_cell,guess,set_cell)
          if(typeof result === "number") {
            result = result - to_value
            result_object = {guess: guess, result: result, sign: result/Math.abs(result)}
            results.push(result_object)
            if(result_sign===0){
              result_sign = result_object.sign
            }else{
              if(result_sign!==result_object.sign) return results
            }
          }
      } 
      return results
    }


  })
}
 
  

style.css

/**/

tool.html

<div style="margin:1rem; text-align: center;"><div style="display:inline-block; text-align: left;">
<table>
    <tbody>
        <tr>
            <td>Set Cell:</td>
            <td><input size="6" type="text" id="set-cell" onfocus="jade_modules.code.default_value(this)"/></td>
        </tr>            
        <tr>
            <td>To Value:</td>
            <td><input size="6" type="text" id="to-value" /></td>
        </tr>            
        <tr>
            <td>By Changing:</td>
            <td><input size="6" type="text" id="change-cell" onfocus="jade_modules.code.default_value(this)" /></td>
        </tr>            
        <tr>
            <td colspan="2" align="right"><button onclick="jade_modules.code.goal_seek()">Seek</button></td>
        </tr>            
    </tbody>
</table>

</div></div>