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

Forum|Forum|3 years ago
December 1, 2022
1 reply
1336 views

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.

JavaScript

This topic has been closed for replies.

try67

Community Expert

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?

M

MML27009716js05Author

Participant

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

Thank you once again for any insight.

Nesa Nurani

Community Expert

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;

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded