You are viewing limited content. For full access, please sign in.

Question

Question

Formula for use in a table

Forms
Updated June 21, 2021
asked on June 17, 2021 • Show version history

I have the following formula working in separate fields. How do I adapt it for use in a table?

=SUM(StartDate,IF(Duration="90days",90,IF(Duration="180days",180,IF(Duration="365days",365,0))))

The purpose is to calculate a new date based on a selection of 3 months, 6 months, or 1 year.

Addendum: Would there be a way to calculate the actual months rather than by days, so for example, that the calculated date from today would be 9/17/2021 (3 months) rather than 9/19/2021 (90 days)?

Thank you.

 

0 0

Answer

SELECTED ANSWER
replied on June 21, 2021 • Show version history

You still don't have IF(condition, true, false) formatting.

It sounds like you're trying to have multiple IF conditions, but you can't do that with a single statement. The IF only allows one condition with two possible results.

The OR isn't going to allow you to pair values, it is only going to tell your if to return true if anything inside of it is true.

What you need is nested IF statements to evaluate each of the possible conditions

IF(3months,3,IF(6months,6,12))

This is basically like

IF 3months

     Then 3

     Else

          If 6months

               Then 6

               Else 12

 

The reason your other one works is because your OR is properly set as the Condition (notice the end parentheses after "CompTimeSTR" to end the OR statement) and you then have a single true value and a single false value.

0 0

Replies

replied on June 17, 2021

If you're running the calculation for each row, then you new to wrap each table variable in the INDEX function, and use the ROW() function as the index value.

For example, INDEX(StartDate,ROW()) instead of StartDate

Variables in a table or collection are an array/set of values. The INDEX function tells it you want a specific value from the set, and the ROW() function says you want the value from the "current" row.

0 0
View 2 previous replies
replied on June 21, 2021

Thanks for your reply. I changed this up a bit, based on another formula I have that is working. But, I'm getting a calculation error (incorrect number of input parameters) and I can't figure out why:

=IF(OR(
INDEX(RWA_Recurring_Expected_Duration,ROW())="3months",
INDEX(RWA_Recurring_Expected_Duration,ROW())="6months",
INDEX(RWA_Recurring_Expected_Duration,ROW())="1year"),
ADD_MONTHS(INDEX(RWA_Recurring_End_Date,ROW()), 3),
ADD_MONTHS(INDEX(RWA_Recurring_End_Date,ROW()), 6),
ADD_MONTHS(INDEX(RWA_Recurring_End_Date,ROW()), 12))

0 0
replied on June 21, 2021

It's hard to say what it "should" look like without knowing the intended logic, but it looks like you're missing the end of the IF formula.

IF should look like =IF(condition, value or true, value for false)

Currently, you seem to have =IF(OR() without the closing parentheses or the values it is meant to use when true or false.

The OR will return "True" if any of the contained statements is true, and "False"

When dealing with long conditions like that it can be easy to miss things, so I'd recommend building it in pieces.

0 0
replied on June 21, 2021

Updated syntax due to an incorrect variable - duh!

=IF(OR(
INDEX(RWA_Recurring_Expected_Duration,ROW())="3months",
INDEX(RWA_Recurring_Expected_Duration,ROW())="6months",
INDEX(RWA_Recurring_Expected_Duration,ROW())="1year"),
ADD_MONTHS(INDEX(RWA_Recurring_Start_Date,ROW()), 3),
ADD_MONTHS(INDEX(RWA_Recurring_Start_Date,ROW()), 6),
ADD_MONTHS(INDEX(RWA_Recurring_Start_Date,ROW()), 12))

The intention is to add 3, 6, or 12 months to the start date based on a selection of said months from a dropdown list. I've tried building with various combinations of open and closed parens and each time I either get invalid syntax or incorrect number of parameters.

Not to muddy the waters, but this is built modeling the formula below, which calculates overtime and works perfectly:

=IF(OR(
INDEX(OT_Details.OT_Type,ROW())="OvertimeSTR",
INDEX(OT_Details.OT_Type,ROW())="CompTimeSTR"),
MULT(INDEX(OT_Details.OT_Hours_Worked,ROW()), 1),
MULT(INDEX(OT_Details.OT_Hours_Worked,ROW()), 1.5))

So I am just really stumped.

0 0
SELECTED ANSWER
replied on June 21, 2021 • Show version history

You still don't have IF(condition, true, false) formatting.

It sounds like you're trying to have multiple IF conditions, but you can't do that with a single statement. The IF only allows one condition with two possible results.

The OR isn't going to allow you to pair values, it is only going to tell your if to return true if anything inside of it is true.

What you need is nested IF statements to evaluate each of the possible conditions

IF(3months,3,IF(6months,6,12))

This is basically like

IF 3months

     Then 3

     Else

          If 6months

               Then 6

               Else 12

 

The reason your other one works is because your OR is properly set as the Condition (notice the end parentheses after "CompTimeSTR" to end the OR statement) and you then have a single true value and a single false value.

0 0
replied on June 21, 2021

I got it to work! Thanks so much for your time and effort.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...