Referencing Tabs

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I am trying to complete a spreadsheet that will allow me
to insert the value of an asset and calculate the total
cost to purchase it. On the next tab, I have a sheet
showing the cost of insuring the asset.

Now then, here is my dilemma. I am trying to get the
total cost of the asset together. Insurance is one of the
main costs. The insurance is not a straight formula and
on the tab representing insurance costs, each row is an
amount that is dictated by regular interval brackets.
$1,800, $1,850, $1,900, $1,950, $2,000 etc. In the Next
colum over is the insurance cost. For instance, if the
asset costs of $1,800 is posted in C16, then the
Insurance cost of the asset is posted in D16 let us say
$70. If the cost of the item is above an interval price,
for instance 1,810 then it must go to the next higher
cost bracket of 1,850.

How do I set up a logic test to immediately calculate the
cost of the insurance on the first page by referencing
the second?

For Example:

One cell will always have the asset price, let's say B5
(Which I can change according to circumstances) and below
that is the cell box in B6 (that doesn't change) that
represents the cost of insurance. I need the test to see
the value in B5, go to the appropriate value according
the asset value and then immediately post the appropriate
insurance cost.

If the asset has a value of $1,810, I need it to go to
the value of $1850 and insert $75 for the cost of that
insurance.

I apologize for the detail. This is a bit tricky and
wanted it to be clear. The answer to this will help me
with numerous applications.

Michael
 
One possible way

=INDEX(Sheet2!$D$16:$D$40,MATCH(SMALL(Sheet2!$C$16:$C$40,COUNTIF(Sheet2!$C$1
6:$C$40,"<"&$B$5)+1),Sheet2!$C$16:$C$40,0))


where B5 is the asset price, Sheet2 C16:C40 is the asset range and Sheet2
D16:D40 the insurance cost.
Make a table with the different brackets I used C16:C40 adapt to fit your
real numbers where
for instance 1800 is in C16 and 1850 in C17, 1700 in C18 and 70 in D16, 75
in D17 and so on.
Then the lookup value is for instance 1855 it will lookup 1900 and return
the adjacent cell from D18
lets say 80.
 
Back
Top