Formulas with multiple inputs in smartsheets. Help needed!!

JEL7

This has been a challenge for me to figure out using the smartsheet platform. I am developing what is essentially a big calculator.

The formula that I am trying to implement is: [($26,000 * DTH) + (Risk R * Repair Cost) + (Risk A * repair cost)] * frequency).

In this database, each row represents an additional input. The user needs to select the equipment type, select one risk R, and one risk A. Then there is to be a calculated column that takes all the inputs and other variables and performs the formula calculation. I'd like for the calculation column to be a column formula type so it is automated.

For each equipment type (1 of 20), there must be a selected risk A (1 of 4) and Risk B (1 of 4). The rest are multiplier values. I will greatly appreciate help on this.

Lets say that each Equipment variable is any ONE of the following:

a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t.

The next variable is "DTH." Each DTH value is associate with a specific Equipment variable.

a = 24

b = 8

c = 16

d = 36

e = 36

f = 0

g = 36

h = 36

i = 72

j = 36

k = 72

l = 0

m = 8

n = 24

o = 72

p = 72

q = 36

r = 36

s = 8

t = 8

The next variable, is another column associated with "Risk A." Risk A input can be only one of: 0,1,2,3.

Risk A

0 = $7,500,000

1 = $5,025,000

2 = $2,475,000

3 = $7,500.

The frequency is associated with each equipment type:

a = 0.01

b = 0.02

c = 0.03

d = 0.04

e = 0.05

f = 0.06

g = 0.07

h = 0.08

i = 0.09

j = 0.1

k = 0.11

l = 0.12

m = 0.13

n = 0.14

o = 0.15

p = 0.16

q = 0.17

r = 0.18

s = 0.19

t = 0.2

The next variable, Repair cost, is associated with each equipment input. For example:

a = 1

b = 2

c = 3

d = 4

e = 5

f = 6

g = 7

h = 8

i = 9

j = 10

k = 11

l = 12

m = 13

n = 14

o = 15

p = 16

q = 17

r = 18

s = 19

t = 20

The next variable is another column associated with "Risk R." Risk R input can only be one of the following: 0,1,2,3. Risk R is a multiplier variable.

Risk R

0 = 100% (1)

1 = 60% (.60)

2 = 30% (.30)

3 = 7.5% (.075)

What is the best way to accomplish this?? This is stumping me! Thanks in advance

Paul Newcome

I would suggest creating a reference table on another sheet. Then you can use an INDEX/MATCH to pull in the appropriate number for each variable and drop them into your equation.

=(26000 * INDEX({Reference Tables DTH Column}, MATCH(DTH@row, {Reference Tables Letters Column}, 0))) + (INDEX({Reference Tables Risk R Column}, MATCH([Risk R]@row, {Reference Tables Numbers Column}, 0)) * INDEX({Reference Tables Repair Cost Column}, MATCH([Repair Cost]@row, {Reference Tables Letters Column}, 0)))

+ .............................................