Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Multiple Variables.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Multiple Variables.
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 -- Cheers macropod [MVP - Microsoft Word] "Sri Harsha" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula with multiple variables | Excel Worksheet Functions | |||
If,Then for multiple variables. | Excel Discussion (Misc queries) | |||
Multiple variables-SOS | Excel Worksheet Functions | |||
look for a value with multiple variables | Excel Discussion (Misc queries) | |||
Formula for counting multiple variables in a spreadsheet | Excel Worksheet Functions |