what if problem to compute cost depending on time

[aj9409]

aj9409
Jan 25, 3:11 pm show options

Newsgroups: microsoft.public.fr.excel
From: "aj9409" <[email protected]> - Find messages by this author
Date: 25 Jan 2006 12:11:48 -0800
Local: Wed, Jan 25 2006 3:11 pm
Subject: help in writing an excel program
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I column A have a list of values. In column B, I have a cost, and in
column c I have the incrimental cost between lets say A6 and A7. What
I want to do is write a program that allows me to put in a value in a
cell lets say D1 is 137. I want to compare my D1 value to A3, A4 , A5
and so on until it does not exceed a value in the A column. When the D1

value is between, lets say A7 and A8, I want to use the value in B7,
and then multiply (D1 - A7) by C7 and get a resulting value in D2.


In short if D1 is greater than A3, then compare it to the next
successive A cell until it is not greater, when it is not greater, then

minus the A cell from D1 and multiply it by the corresponding C cell,
and at the corresponding B cell value.

 

[Guest]

=(D1-VLOOKUP(D1,A3:C7,1))*VLOOKUP(D1,A3:C7,3)

The information in column A must be in acending order for VLOOKUP to work.

Here is some sample data I used. You should be able to adjust the formula
to your table. In this example 137 is compared to the values in column A.
It is bigger than 100, but less than 150 so the two lookup values are 100 and
4. (137-100)*4=148.

A B C D
1 137
2 148
3 10 32
4 50 12
5 75 67
6 100 4
7 150 9