Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help w indirect and index,match
I'm working on a user input worksheet where the formulas I create will calculate from user input in the highlighted blue fields and return correct output based on user entry. its attached in a 2010 excel workbook
Anway the output formulas require the following (i'll skip the car make/descrp): 1) Estimated annual cost of gas based on selected type of driving,weight class,annual miles traveled, gas price and engine type 2) Est. insurance cost based on owner's region of residence, driving record, and residential status (hint: include an intermediate calc that lookups up the region number based on the state...blah blah.....have this part below: VLOOKUP('Estimate Form'!$F$5,Statestbl,4,FALSE) Lastly, 3) est. avaerage annual maintainence costs for 1st 3 years of operation based on the selected car model and annual miles driven (total 3 yr maintainence costs divide by 3 plus cost of yr of oil changes); to the base maintainence cost, add $29 per oil change for each 5k miles driven I've tried using vlookup and match alone but that doesn't work. Not sure how to use index and indirect well. Basically I'm looking for at least one formula that fulfills the requirement and could help me out on either 1 2 or 3. If I can get that should be able to finish the rest. We're supposed to use the index and indirect functions for these. However, if there's another way to solve it that works, I'd appreciate seeing it. Thanks Last edited by excelhelp_84 : October 29th 12 at 07:22 PM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help w indirect and index,match
On Sunday, October 28, 2012 2:38:56 PM UTC-5, excelhelp_84 wrote:
I'm working on a user input worksheet where the formulas I create will calculate from user input in the highlighted blue fields and return correct output based on user entry. its attached in a 2010 excel workbook Anway the output formulas require the following (i'll skip the car make/descrp): 1) Estimated annual cost of gas based on selected type of driving,weight class,annual miles traveled, gas price and engine type 2) Est. insurance cost based on owner's region of residence, driving record, and residential status (hint: include an intermediate calc that lookups up the region number based on the state...blah blah.....have this part below: VLOOKUP('Estimate Form'!$F$5,Statestbl,4,FALSE) Lastly, 3) est. avaerage annual maintainence costs for 1st 3 years of operation based on the selected car model and annual miles driven (total 3 yr maintainence costs divide by 3 plus cost of yr of oil changes); to the base maintainence cost, add $29 per oil change for each 5k miles driven I've tried using vlookup and match alone but that doesn't work. Not sure how to use index and indirect well. Basically I'm looking for at least one formula that fulfills the requirement and could help me out on either 1 2 or 3. If I can get that should be able to finish the rest. We're supposed to use the index and indirect functions for these. However, if there's another way to solve it that works, I'd appreciate seeing it. Thanks +-------------------------------------------------------------------+ |Filename: ckg%20operating%20costs_matt_hickman.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=646| +-------------------------------------------------------------------+ -- excelhelp_84 And just what is this for We're supposed to use the index and indirect functions for these |
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
I'm sorry but I'm sure there are lots of ppl that come on here for "homework help" they may be less subtle than me but still... What's the deal? is that against the forum rules?
If I can get pointed in the right direction, that can help me as well. The format of the formula should be something along the lines of: INDEX(INDIRECT(VLOOKUP("Disc",TablDef,MATCH("Tabl_ Def",TablCol,0),FALSE),TRUE),MATCH($E$15,INDIRECT( VLOOKUP("Disc",TablDef,MATCH("row_num",TablCol,0), FALSE),TRUE),1),MATCH("Discount",INDIRECT(VLOOKUP( "Disc",TablDef,MATCH("col_num",TablCol,0),FALSE),T RUE),0)) |
#5
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Indirect with an Index Match Function | Excel Worksheet Functions | |||
Indirect with index and match | Excel Worksheet Functions | |||
Indirect function in Index/Match Array | Excel Discussion (Misc queries) | |||
Match, Index, Indirect ? | Excel Worksheet Functions | |||
Index,Indirect, and Match | Excel Worksheet Functions |