Formula for Multiple Variables.

[Sri Harsha]

Hi,
I am preparing an estimation template. I have a source tables which have the
estimated hours of effort and a table which gives options to select the
variables which gives the output.

I have given below examples of both the tables. I have similar source tables
for other products as well.

Please assist me in finding a forumla for multiple variables. The last
column of Estimates table will have the formula. The table has all the
variables possible.

I have tried using IF condition for product and Index and Match for other
variables. It does not seem to be working.

Ex: If i have Affinity product, Release notes as deliverable, and of low
complexity with 5 pages, the result should be 5*4 = 20 Hrs.

Estimates table:

S.No Product Deliverable Complexity of FD No. of Pages
1 Affinity Release Notes Low 5
2 QES User Guides Medium 4
3 Quantim Dictionary Medium 5
4 Interlink File Layouts High 6


Source Table for Estimates for Affinity:

FD Complexity RN UG Dictionary File Layout
Low 4 3 3 8
Medium 6 6 5 8
High 8 8 8 8

 

[macropod]

Hi Sri,

If you define the names 'FD', 'RN', 'UG',' DF' and 'Layout' for the corresponding columns in your Source Table, and 'Complexity' and
'Pages' for the corresponding columns in your Estimates Table, the following formulae will return the hours for each item (S.No) -
provided the formulae are on the corresponding rows in your Estimates Table:
=INDEX(RN,MATCH(Complexity,FD,0))*Pages
=INDEX(UG,MATCH(Complexity,FD,0))*Pages
=INDEX(DF,MATCH(Complexity,FD,0))*Pages
=INDEX(Layout,MATCH(Complexity,FD,0))*Pages