Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
what if problem to compute cost depending on time
aj9409
Jan 25, 3:11 pm show options Newsgroups: microsoft.public.fr.excel From: "aj9409" - 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
what if problem to compute cost depending on time
=(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 "aj9409" wrote: aj9409 Jan 25, 3:11 pm show options Newsgroups: microsoft.public.fr.excel From: "aj9409" - 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ugh..another time and date problem...HELP | Excel Worksheet Functions | |||
Problem in using Vlookup (1st time user) | Excel Worksheet Functions | |||
time subtraction problem | Excel Worksheet Functions | |||
Problem with time calc | Excel Worksheet Functions | |||
Major cost of time. | Excel Worksheet Functions |