Converting GoalSeekfunction from MS XCEL to DronaHQ

Hello,
I have an Excel based macro sheet which contains macros with a Goal seek function (E.g. which input value does need to be given to generate a desired output).
How can I set this up in DronaHQ?

Hi @martijn.craje,

You can create a Goal Seek functionality using a simple JS code in DronaHQ Studio. I have created a sample app for your reference. In this sample app, I have considered a Sales Profit use case. That is, based on a target Profit or Margin, the system can calculate the Sales Unit, Sales Price, or Variable Cost.

In this sample app, on click of the Goal Seek button, I am redirecting to a popup screen where I will select the desired Target field (Profit or Margin in this case), its desired output value, and the changing (input) field. Then on clicking the Calculate button, a JS code task executes the script to return the ideal value of this changing (input) field and show it in the respective control on the home screen.

Here is the JS code used in this sample app:

function JSCode( output, fixedcellName, fixedcellValue, changingcellName, price, unit, profit, variableprice, fixedcost ) {

	var changedMargin;
   
	if(fixedcellName == "Profit") {
		var profitValue = fixedcellValue;

		if(changingcellName == "Units") {
			changedMargin = profitValue - fixedcost;
			var changedUnit = changedMargin / (variableprice + price);
			output = changedUnit;
		}
		
		else if(changingcellName == "Price") {
			changedMargin = profitValue - fixedcost;
			var changedPrice = (changedMargin / unit) - variableprice;
			output = changedPrice;
		}

		else if(changingcellName == "Variable Cost") {
			changedMargin = profitValue - fixedcost;
			var changedVariableCostPrice = (changedMargin / unit) - price;
			output = changedVariableCostPrice;
		}
	}
	
	else if(fixedcellName == "Margin") {
		var marginValue = fixedcellValue;

		if(changingcellName == "Units") {
			var changedUnit = marginValue / (variableprice + price);
			output = changedUnit;
		}

		else if(changingcellName == "Price") {
			var changedUnit = (marginValue / unit) - variableprice;
			output = changedUnit;
		}

		else if(changingcellName == "Variable Cost") {
			var changedVariableCostPrice = (marginValue / unit) - price;
			output = changedVariableCostPrice;
		}
	}    
	
	return output;
}

Similarly, you can create your own Goal Seek functionality as per your business requirements.

I hope this will help in achieving what you are looking for. :slightly_smiling_face: