Formula for Multiple Variables.

S

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top