Copy link to clipboard
Copied
Adobe Acrobat Pro user here.
I have used a custom calculation script to calculate the average of cells, but also ignore blank values when they exist. This is working perfectly.
However, now I need an additional adjustment. I think this may be a validation script but it may call for a calculation script, which would further alter the current one I have.
We are using the previous calulation to determing what commission rate someone gets to use. Each value represents the commission rate the salesperson used, and the final field is the average of all commission rates being used. But the system is bracketed. For example:
Our bracketed Commission Rates are 15%, 18%, & 21%. A sales person sold three products; (2) at a 15% rate, and (1) at the 18% rate. With the current formula I have on the form, the average commission would read at 16% , however, since we do not offer 16% commission, I would like the value to only show values in terms of "Less than 15%, 15%, 18%, and 21%), always rounding down (a value of 17.9999% still reads as 15%).
Is this possible? I know this is complicated, so I can provide further information if needed. THank you for all of you help.
Try replacing your script with this one:
var total = 0;
var num = 0;
for( var i=1; i<=10; i++){
if(this.getField("COP_TotalRow"+i).valueAsString != ""){
total++;
num += Number(this.getField("COP_TotalRow"+i).valueAsString);}}
var cof = num/total;
if(cof >= 15 && cof < 18)cof = 15;
else if(cof >= 18 && cof < 21)cof = 18;
else if(cof >= 21)cof = 21;
if(total == 0) event.value = "";
else event.value = cof;
Copy link to clipboard
Copied
Are you currently using a script for the calculation? If so, please post it.
Copy link to clipboard
Copied
This is the Custom Calculation Script I am using. I have attached the file as well.
var fld1=this.getField("COP_TotalRow1").value;
var fld2=this.getField("COP_TotalRow2").value;
var fld3=this.getField("COP_TotalRow3").value;
var fld4=this.getField("COP_TotalRow4").value;
var fld5=this.getField("COP_TotalRow5").value;
var fld6=this.getField("COP_TotalRow6").value;
var fld7=this.getField("COP_TotalRow7").value;
var fld8=this.getField("COP_TotalRow8").value;
var fld9=this.getField("COP_TotalRow9").value;
var fld10=this.getField("COP_TotalRow10").value;
var num=0;
if (fld1!="")
{num+=1}
if (fld2!="")
{num+=1}
if (fld3!="")
{num+=1}
if (fld4!="")
{num+=1}
if (fld5!="")
{num+=1}
if (fld6!="")
{num+=1}
if (fld7!="")
{num+=1}
if (fld8!="")
{num+=1}
if (fld9!="")
{num+=1}
if (fld10!="")
{num+=1}
if (num==0)
{event.value=""}
else
{event.value=(Number(fld1)+Number(fld2)+Number(fld3)+Number(fld4)+Number(fld5)+Number(fld6)+Number(fld7)+Number(fld8)+Number(fld9)+Number(fld10))/num}
Copy link to clipboard
Copied
Try replacing your script with this one:
var total = 0;
var num = 0;
for( var i=1; i<=10; i++){
if(this.getField("COP_TotalRow"+i).valueAsString != ""){
total++;
num += Number(this.getField("COP_TotalRow"+i).valueAsString);}}
var cof = num/total;
if(cof >= 15 && cof < 18)cof = 15;
else if(cof >= 18 && cof < 21)cof = 18;
else if(cof >= 21)cof = 21;
if(total == 0) event.value = "";
else event.value = cof;
Copy link to clipboard
Copied
Wow! Seems to work! Thank you!