Skip to main content
A
AaronIsh
Participant
January 14, 2025
Answered

Convert Complex Excel Calculation Formula to PDF Custom Calculation Script

  • January 14, 2025
  • 2 replies
  • 412 views

I have an Excel formula calculates the Unit_Price which is then multuplied by the quantity to establish a quote for printed matierial. I have been asked to create a PDF Form for orders of printed material but I have very little to no experiience with PDF Custom Calculation Script (or java).

 

I need help with translating the formula below into Custome Calculation Script for the Unit_Price field. It is calculated on 2 variables, the Item (H26) and Quantity (I26). I was able to do a basic custom calculation based just on the item and set a single price but not able to work out how to include the second variable which is the price change based on quantity).  Unfortunatley I don't know javascript at all and nothing I found anywhere was able to really assist me in the translating this Excel formula.

 

If anyone can help or point me to a great tutorial on translating Excel to Java, I would really appreciate the assisatance.

 

(H26) - Printed_Item (var v1 = this.getField("Print_Item").value;)

(I26) - Print_Quantity (var v2 = this.getField("Print_Quantity").value;)

 

EXCEL FORMULA to set UNIT_PRICE

=IF(H26="A4 Flyers",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<151),0.25,IF(AND(I26>=151,I26<451),0.2,IF(AND(I26>=451,I26<951),0.14,IF(AND(I26>=951,I26<1401),0.1,IF(AND(I26>=1401,I26<1901),0.09,IF(I26>=1901,0.08,"")))))))),IF(H26="DL Flyers",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<151),0.08,IF(AND(I26>=151,I26<451),0.07,IF(AND(I26>=451,I26<951),0.05,IF(AND(I26>=951,I26<1401),0.04,IF(AND(I26>=1401,I26<1901),0.04,IF(I26>=1901,0.03,"")))))))),IF(H26="A4 Poster (100 gsm)",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<11),1.00,IF(AND(I26>=11,I26<26),0.07,IF(AND(I26>=26,I26<51),0.05,IF(AND(I26>=51,I26<101),0.03,IF(I26>=101,0.25,""))))))),IF(H26="A3 Poster (100 gsm)",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<151),0.08,IF(AND(I26>=151,I26<451),0.07,IF(AND(I26>=451,I26<951),0.05,IF(AND(I26>=951,I26<1401),0.04,IF(AND(I26>=1401,I26<1901),0.04,IF(I26>=1901,0.03,"")))))))),IF(H26="Corflute - A1 Poster Coreflute",(IF(ISBLANK(I26),"",IF(AND(I26>=1,I26<11),25,IF(AND(I26>=11,I26<26),22.5,IF(I26>=26,20,""))))))))))

Correct answer Nesa Nurani

Try this as custom calculation script of  "Unit_Price" field:

var printItem = this.getField("Print_Item").valueAsString;
var quantity = Number(this.getField("Print_Quantity").valueAsString);
var unitPrice = "";

if (!isNaN(quantity) && quantity > 0) {
    switch (printItem) {
        case "A4 Flyers":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.25;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.2;
            else if (quantity >= 451 && quantity < 951) unitPrice = 0.14;
            else if (quantity >= 951 && quantity < 1401) unitPrice = 0.1;
            else if (quantity >= 1401 && quantity < 1901) unitPrice = 0.09;
            else if (quantity >= 1901) unitPrice = 0.08;
            break;

        case "DL Flyers":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.08;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.07;
            else if (quantity >= 451 && quantity < 951) unitPrice = 0.05;
            else if (quantity >= 951 && quantity < 1401) unitPrice = 0.04;
            else if (quantity >= 1401 && quantity < 1901) unitPrice = 0.04;
            else if (quantity >= 1901) unitPrice = 0.03;
            break;

        case "A4 Poster (100 gsm)":
            if (quantity >= 1 && quantity < 11) unitPrice = 1.00;
            else if (quantity >= 11 && quantity < 26) unitPrice = 0.07;
            else if (quantity >= 26 && quantity < 51) unitPrice = 0.05;
            else if (quantity >= 51 && quantity < 101) unitPrice = 0.03;
            else if (quantity >= 101) unitPrice = 0.25;
            break;

        case "A3 Poster (100 gsm)":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.08;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.07;
            else if (quantity >= 451 && quantity < 951) unitPrice = 0.05;
            else if (quantity >= 951 && quantity < 1401) unitPrice = 0.04;
            else if (quantity >= 1401 && quantity < 1901) unitPrice = 0.04;
            else if (quantity >= 1901) unitPrice = 0.03;
            break;

        case "Corflute - A1 Poster Coreflute":
            if (quantity >= 1 && quantity < 11) unitPrice = 25.0;
            else if (quantity >= 11 && quantity < 26) unitPrice = 22.5;
            else if (quantity >= 26) unitPrice = 20.0;
            break;

        default:
            unitPrice = "";
            break;}}

event.value = unitPrice;

2 replies

Nesa Nurani
Community Expert
Nesa NuraniCommunity ExpertCorrect answer
Community Expert
January 14, 2025

Try this as custom calculation script of  "Unit_Price" field:

var printItem = this.getField("Print_Item").valueAsString;
var quantity = Number(this.getField("Print_Quantity").valueAsString);
var unitPrice = "";

if (!isNaN(quantity) && quantity > 0) {
    switch (printItem) {
        case "A4 Flyers":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.25;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.2;
            else if (quantity >= 451 && quantity < 951) unitPrice = 0.14;
            else if (quantity >= 951 && quantity < 1401) unitPrice = 0.1;
            else if (quantity >= 1401 && quantity < 1901) unitPrice = 0.09;
            else if (quantity >= 1901) unitPrice = 0.08;
            break;

        case "DL Flyers":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.08;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.07;
            else if (quantity >= 451 && quantity < 951) unitPrice = 0.05;
            else if (quantity >= 951 && quantity < 1401) unitPrice = 0.04;
            else if (quantity >= 1401 && quantity < 1901) unitPrice = 0.04;
            else if (quantity >= 1901) unitPrice = 0.03;
            break;

        case "A4 Poster (100 gsm)":
            if (quantity >= 1 && quantity < 11) unitPrice = 1.00;
            else if (quantity >= 11 && quantity < 26) unitPrice = 0.07;
            else if (quantity >= 26 && quantity < 51) unitPrice = 0.05;
            else if (quantity >= 51 && quantity < 101) unitPrice = 0.03;
            else if (quantity >= 101) unitPrice = 0.25;
            break;

        case "A3 Poster (100 gsm)":
            if (quantity >= 1 && quantity < 151) unitPrice = 0.08;
            else if (quantity >= 151 && quantity < 451) unitPrice = 0.07;
            else if (quantity >= 451 && quantity < 951) unitPrice = 0.05;
            else if (quantity >= 951 && quantity < 1401) unitPrice = 0.04;
            else if (quantity >= 1401 && quantity < 1901) unitPrice = 0.04;
            else if (quantity >= 1901) unitPrice = 0.03;
            break;

        case "Corflute - A1 Poster Coreflute":
            if (quantity >= 1 && quantity < 11) unitPrice = 25.0;
            else if (quantity >= 11 && quantity < 26) unitPrice = 22.5;
            else if (quantity >= 26) unitPrice = 20.0;
            break;

        default:
            unitPrice = "";
            break;}}

event.value = unitPrice;
A
AaronIshAuthor
Participant
January 14, 2025

Thanks you. I will try that too.

A
AaronIshAuthor
Participant
January 14, 2025

This what I was able to get out of ChatGPT:

(function () {
    var H26 = this.getField("H26").valueAsString; // Replace with the actual field name for H26
    var I26 = +this.getField("I26").value; // Replace with the actual field name for I26
    var result = "";

    if (H26 === "A4 Flyers") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 151) {
            result = 0.25;
        } else if (I26 >= 151 && I26 < 451) {
            result = 0.2;
        } else if (I26 >= 451 && I26 < 951) {
            result = 0.14;
        } else if (I26 >= 951 && I26 < 1401) {
            result = 0.1;
        } else if (I26 >= 1401 && I26 < 1901) {
            result = 0.09;
        } else if (I26 >= 1901) {
            result = 0.08;
        }
    } else if (H26 === "DL Flyers") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 151) {
            result = 0.08;
        } else if (I26 >= 151 && I26 < 451) {
            result = 0.07;
        } else if (I26 >= 451 && I26 < 951) {
            result = 0.05;
        } else if (I26 >= 951 && I26 < 1401) {
            result = 0.04;
        } else if (I26 >= 1401 && I26 < 1901) {
            result = 0.04;
        } else if (I26 >= 1901) {
            result = 0.03;
        }
    } else if (H26 === "A4 Poster (100 gsm)") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 11) {
            result = 1.00;
        } else if (I26 >= 11 && I26 < 26) {
            result = 0.07;
        } else if (I26 >= 26 && I26 < 51) {
            result = 0.05;
        } else if (I26 >= 51 && I26 < 101) {
            result = 0.03;
        } else if (I26 >= 101) {
            result = 0.25;
        }
    } else if (H26 === "A3 Poster (100 gsm)") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 151) {
            result = 0.08;
        } else if (I26 >= 151 && I26 < 451) {
            result = 0.07;
        } else if (I26 >= 451 && I26 < 951) {
            result = 0.05;
        } else if (I26 >= 951 && I26 < 1401) {
            result = 0.04;
        } else if (I26 >= 1401 && I26 < 1901) {
            result = 0.04;
        } else if (I26 >= 1901) {
            result = 0.03;
        }
    } else if (H26 === "Corflute - A1 Poster Coreflute") {
        if (I26 === "") {
            result = "";
        } else if (I26 >= 1 && I26 < 11) {
            result = 25;
        } else if (I26 >= 11 && I26 < 26) {
            result = 22.5;
        } else if (I26 >= 26) {
            result = 20;
        }
    }

    event.value = result; // Assign the calculated result to the field
})();
A
AaronIshAuthor
Participant
January 14, 2025

Looks like it worked. Hope this may be helpful for others.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices