If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|