Question

Formula error - incorrect number of input parameters

Forms

Updated September 21, 2024

asked on September 19, 2024

Another day, another formula question.

A colleague and I have struggled to create a formula that will calculate per diem costs (mealsGrossTotal) depending on the travel departure date (DepartureDateReformat) and the number of days of travel (TotalDays). We finally got this to work:

=IF(DepartureDateReformat>DATE(2024,9,30),IF(TotalDays=1,SUB(mealsGrossTotal,17.00),IF(TotalDays=2,SUB(mealsGrossTotal,34.00),IF(TotalDays>2,SUB(mealsGrossTotal,34.00),0))),mealsGrossTotal)

We then realized that for two of the elements (TotalDays=1 and TotalDays=2) we needed to use a percentage rather than a flat rate. We tried to follow the pattern of the working formula but just can't get it right:

=IF(DepartureDateReformat>DATE(2024,9,30),IF(TotalDays=1,SUB(PRODUCT(mealsGrossTotal,0.75),IF(TotalDays=2,SUB(PRODUCT(mealsGrossTotal,0.75),IF(DepartureDateReformat>DATE(2024,9,30),IF(TotalDays>2,SUB(mealsGrossTotal,34.00),0)))))),mealsGrossTotal)

Each of the elements (TotalDays=1, TotalDays=2, TotalDays>2) work individually and it appears that we have the right number of parentheses when combined. Would appreciate any help as we were given a short deadline for this with the approach of the new fiscal year.

0 0

Answer

SELECTED ANSWER

replied on September 21, 2024

For anyone who might be interested, I figured it out. The problem was not having "false" conditions for segments beginning "(SUB(PRODUCT...".

=IF(DepartureDateReformat>DATE(2024,9,30),IF(TotalDays=1,SUB(PRODUCT(mealsGrossTotal,0.75),0),IF(TotalDays=2,SUB(PRODUCT(mealsGrossTotal,0.75),0),IF(TotalDays>2,SUB(mealsGrossTotal,PerDiem75),0))),mealsGrossTotal)

0 0

Replies

replied on September 19, 2024 • Show version history

It appears that you're using formulas incorrectly. I suggest reading Formulas in Laserfiche Forms.

# Your example
DepartureDateReformat>DATE(2024,9,30)

# Correct formula
GT(DepartureDateReformat,DATE(2024,9,30))

Perform the same replacements for any greater than, less than, or equal to formulas.

0 0

replied on September 20, 2024

Thank you - I do see a mistake I made - I have an extra instance of the date sequence. But I'm still getting the incorrect number of parameters error.

Works:

=IF(DepartureDateReformat>DATE(2024,9,30),IF(TotalDays=1,SUB(mealsGrossTotal,17.00),IF(TotalDays=2,SUB(mealsGrossTotal,34.00),IF(TotalDays>2,SUB(mealsGrossTotal,34.00),0))),mealsGrossTotal)

Doesn't work:

=IF(DepartureDateReformat>DATE(2024,9,30),IF(TotalDays=1,SUB(PRODUCT(mealsGrossTotal,0.75),IF(TotalDays=2,SUB(PRODUCT(mealsGrossTotal,0.75),IF(TotalDays>2,SUB(mealsGrossTotal,34.00),0))))),mealsGrossTotal)

The difference between the two is the "TotalDays>2" segment.

When I look at both formulas in Notepad++, the number of parentheses line up. I suspect I'm missing a ",0" somewhere, but I have tried different combinations and haven't landed on it yet.

0 0

replied on September 20, 2024

As I said in my previous response, the formula for greater than is GT(value1,value2). The formula for equals is EQ(value1,value2).

0 0

You are not allowed to follow up in this post.

Question

Question

Formula error - incorrect number of input parameters

Answer

Replies

Sign in to reply to this post.