Help

Re: Please help with a formula

Solved
Jump to Solution
3738 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Tiaan_Terblanch
6 - Interface Innovator
6 - Interface Innovator

Hi

I would appreciate any assistance with my formula.

Snip20221205_1.png

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

2 Solutions

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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
)

See Solution in Thread

autumn
6 - Interface Innovator
6 - Interface Innovator

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

Screen Shot 2022-12-05 at 2.35.05 PM.png

See Solution in Thread

3 Replies 3
Kamille_Parks
16 - Uranus
16 - Uranus

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
)

autumn
6 - Interface Innovator
6 - Interface Innovator

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

Screen Shot 2022-12-05 at 2.35.05 PM.png

Thank you so much for your help!