Skip to main content

Hi

I would appreciate any assistance with my formula.

The end goal is that my Cost field should automatically calculate a cost based on a combination of options selected from the Frequency, Activity, and Complex fields.

The dropdown options for Frequency are:

  • Daily
  • 1 X Week
  • 2 X Week
  • 3 X Week
  • 1 X Month

"Daily" is equal to 22 days.

"1 X Week" is equal to 4 days.

"2 X Week" is equal to 8 days.

"3 X Week" is equal to 12 days.

"1 X Month" is equal to 1 day.

The dropdown options for Activity are:

  • 1
  • 2
  • 3
  • 4
  • 5

"1" is equal to 30 min.

"2" is equal to 1 hour.

"3" is equal to 2 hours.

"4" is equal to 3 hours.

"5" is equal to 4 hours.

The dropdown options for Complexity are:

  • 1 (Standard)
  • 2 (Medium)
  • 3 (Difficult)
  • 4 (Very Difficult)

"1 (Standard)" is equal to €15.

"2 (Medium)" is equal to €16.

"3 (Difficult)" is equal to €17.

"4 (Very Difficult)" is equal to €18.

Now in my Cost field, I want whatever I've selected in Frequency, Activity, and Complex to calculate as an amount.

For example, if I select "Daily" in the Frequency field, "2" in the Activity field, and "3 (Difficult)" in the Complexity field, the amount should be €374 (22*1*17).

I hope I make sense. 😎

Again, your help is appreciated.

Thanks

I would use a series of SWITCH() statements (which asks "If FIELD = A output 1, If FIELD = B output 2, etc.) and then multiply the result of those SWITCHs together

SWITCH({Frequency},
"Daily", 22,
"1 X Week", 4,
"2 X Week", 8,
"3 X Week", 12,
"1 X Month", 1
) *
SWITCH({Activity},
"1", .5,
"2", 1,
"3", 2,
"4", 3,
"5", 4
) *
SWITCH({Complexity},
"1(Standard), 15,
"2(Medium), 16,
"3(Difficult), 17,
"4(Very Difficult), 18
)

Once you have the Formula set, you can use the Formatting tab to specify Currency to correctly display the result, too.

I would use a series of SWITCH() statements (which asks "If FIELD = A output 1, If FIELD = B output 2, etc.) and then multiply the result of those SWITCHs together

SWITCH({Frequency},
"Daily", 22,
"1 X Week", 4,
"2 X Week", 8,
"3 X Week", 12,
"1 X Month", 1
) *
SWITCH({Activity},
"1", .5,
"2", 1,
"3", 2,
"4", 3,
"5", 4
) *
SWITCH({Complexity},
"1(Standard), 15,
"2(Medium), 16,
"3(Difficult), 17,
"4(Very Difficult), 18
)

Thank you so much for your help!

Reply


Terms & Conditions