Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Estimates | Excel Worksheet Functions | |||
average that includes estimates if data not available | Excel Worksheet Functions | |||
estimates | New Users to Excel | |||
Labor costs for estimates | Excel Worksheet Functions | |||
Weekly estimates | Excel Discussion (Misc queries) |