search

OfficeDev / office-js

A repo and NPM package for Office.js, corresponding to a copy of what gets published to the official "evergreen" Office.js CDN, at https://appsforoffice.microsoft.com/lib/1/hosted/office.js.
https://learn.microsoft.com/javascript/api/overview
Other
681 stars 95 forks source link

Custom Functions do not work with What-If Analysis (Goal seek) #4745

Open 4tti opened 3 months ago

4tti commented 3 months ago

Provide required information needed to triage your issue

Your Environment

Expected behavior

Users can use what-if analysis

Current behavior

What if analysis do not work with custom functions

Steps to reproduce

  1. In D4 add value 5
  2. Register script lab's default custom function BASICCUSTOMFUNCTION.SPHEREVOLUME
  3. In cell D9 insert "=SCRIPTLAB.BASICCUSTOMFUNCTION.SPHEREVOLUME(D4)"
  4. It will return 523,5988
  5. Now try to use What-IF analysis/Seek goal like this: image

Result will be some nonsense image but it should actually return D4 defined as close to 0

If you add this "=4/3*PI()*D4*D4*D4" into H9 and you will try same process: image you will get image

which is totally different result.

Context

What-if analysis is pretty useful and powerful feature that can be used for identifying various solutions or problems in the data.

4tti commented 3 months ago

Any update?

4tti commented 2 months ago

@Wenjun-Gong FYI

shanshanzheng-dev commented 2 months ago

Hi @4tti Thanks for reporting this issue and sorry for slow response. I can repo it. We'll take a look and report back if we have a suggestion for you. Thanks.

4tti commented 1 month ago

Similarly it does not work with Data Table in what if analysis