Estimates with multiple look ups
 

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

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.

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

Estimates table:
S.No Deliverable Complexity of FD No. of Pages
1 Release Notes Low 5
2 User Guides Medium 4
3 Dictionary Medium 5
4 File Layouts High 6


Source Table for Estimates.

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

Estimates with multiple look ups
 

Use MATCH function to get the row and column.
Get the right value using the INDEX function

A1:D5 is the estimation table

In Cell E2 enter the below formula. D2 is 5

=D2*INDEX($A$9:$E$12,MATCH(C2,$A$9:$A$12,1),MATCH( B2,$A$9:$E$9,1))

$A$9:$E$12 is the Source of Estimates
$A$9:$A$12 is the FD Complexity
$A$9:$E$9 is the source header (Please make sure to use the same wordings)

If this post helps click Yes
---------------
Jacob Skaria


"Sri Harsha" wrote:

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

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.

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

Estimates table:
S.No Deliverable Complexity of FD No. of Pages
1 Release Notes Low 5
2 User Guides Medium 4
3 Dictionary Medium 5
4 File Layouts High 6


Source Table for Estimates.

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