 ## Welcome to the Community!

We have a brand new look! Take a tour with us and explore the latest updates on Adobe Support Community.

## Adobe Support Community

• Custom Validation or Calculation - Stair Stepped A...

# Custom Validation or Calculation - Stair Stepped Answers

New Here ,
Sep 08, 2021 Sep 08, 2021

Copied

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.

TOPICS
Create PDFs, Edit and convert PDFs, How to, PDF forms

Views

34

Likes

Report

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

Adobe Community Professional , Sep 09, 2021 Sep 09, 2021
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;

Likes 4 Replies 4
Sep 08, 2021 Sep 08, 2021

Copied

Are you currently using a script for the calculation? If so, please post it.

Likes

Report

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

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}

Likes

Report

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

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;

Likes

Report

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

Copied

LATEST

Wow! Seems to work! Thank you!

Likes

Report

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