• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

How to covert Excel formulas into Adobe formulas for custom calculation script

New Here ,
Dec 01, 2022 Dec 01, 2022

Copy link to clipboard

Copied

Hello, 

 

Here is the excel formulas I would like to convert to Adobe Acrobat Pro.  Any help is appreciated in regards to conversion ideas.  

1. =SUMPRODUCT(G7:G37,C7:C37)/SUM(C7:C37)

 

2. Also, I how would I go about converting the below into an adobe acrobat pro custom formula?  I would like to take whatever percentage populates (in this example 15%) to then determine points (in this example 20 points).  I would like points to populate within a range per the percentage.  Thank you for any insight you may have.  

 

Adobe Excel to Adobe Calculation.png

 

TOPICS
JavaScript

Views

733

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 01, 2022 Dec 01, 2022

Copy link to clipboard

Copied

1. SUMPRODUCT is just multiplying and then adding up the totals. There's no built in function like that in JS, but you can easily write a function to that effect. SUM is a simple sum command. Again, you could write a function that takes an array as input parameter and returns its sum as the output.

2. You need to better explain how this would work. How does 15% become 20 points? Is this a formula (if so, which one)? Or just rounding up/down to a certain amount (if so, what are the rules for this rounding)? Something else?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 01, 2022 Dec 01, 2022

Copy link to clipboard

Copied

Thank you for your response.  To hopefully better clarify I am looking for the percentage to become points for example:

 

* if  "funds plus non federal funds" are 20% but less than 50% of  "rural development funds" then the points will automatically populate as 30 points

* if  "funds plus non federal funds" are less than 20% of  "rural development funds" then the points will automatically populate as 20 points

* if  "funds plus non federal funds" are 50% but less than 75% of  "rural development funds" then the points will automatically populate as 10 points

*if  "funds plus non federal funds" are 75% or more of  "rural development funds" then the points will automatically populate as 0 points

 

MML27009716js05_0-1669910271139.png

Thank you once again for any insight.  

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 01, 2022 Dec 01, 2022

Copy link to clipboard

Copied

LATEST

To get points you can use this as custom calculation script of 'Points' field:

var rf = Number(this.getField("rural development funds").valueAsString);
var nf = Number(this.getField("funds plus non federal funds").valueAsString);
var per = 0;
if(rf != 0)per = nf/rf;

if(per>= .2 && per < .5)event.value = 30;
else if(per > 0 && per < .2)event.value = 20;
else if(per>= .5 && per < .75)event.value = 10;
else event.value = 0;

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines