Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an Equation
PLEASE HELP
Need an equation in D1 to find the number immediately greater then number in col C, then copy the number beside from col B to D. For example: for 500 in col C, immediate greater number in col A is 569.3 and the number beside in col B is 79, then the equation in D1 should give 79. (Column B has numbers that sometime increasing sometime decreasing) A B C D 71.162 100 500 142.325 98 100 213.487 95 200 284.65 90 300 355.813 88 400 426.975 85 500 498.138 81 600 569.3 79 700 640.462 76 800 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an Equation
PLEASE HELP
I did in your other post and have answered your follow up question "Raz" wrote: PLEASE HELP Need an equation in D1 to find the number immediately greater then number in col C, then copy the number beside from col B to D. For example: for 500 in col C, immediate greater number in col A is 569.3 and the number beside in col B is 79, then the equation in D1 should give 79. (Column B has numbers that sometime increasing sometime decreasing) A B C D 71.162 100 500 142.325 98 100 213.487 95 200 284.65 90 300 355.813 88 400 426.975 85 500 498.138 81 600 569.3 79 700 640.462 76 800 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an Equation
thanks Mike, I appriciate your help.
but there is a little problem. If col B has numbers in decending order, the I am not getting the correct results. (I should have mentioned earlier that col B may have decending or accending or numbers without order, bassically could be any values) Other than that your equation works fine. Please help to solve this little prob. thanks again. "Mike H" wrote: PLEASE HELP I did in your other post and have answered your follow up question "Raz" wrote: PLEASE HELP Need an equation in D1 to find the number immediately greater then number in col C, then copy the number beside from col B to D. For example: for 500 in col C, immediate greater number in col A is 569.3 and the number beside in col B is 79, then the equation in D1 should give 79. (Column B has numbers that sometime increasing sometime decreasing) A B C D 71.162 100 500 142.325 98 100 213.487 95 200 284.65 90 300 355.813 88 400 426.975 85 500 498.138 81 600 569.3 79 700 640.462 76 800 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an Equation
Hi,
Maybe this =LOOKUP(C1,A1:A9,OFFSET(A1:A9,1,1)) Mike "Raz" wrote: thanks Mike, I appriciate your help. but there is a little problem. If col B has numbers in decending order, the I am not getting the correct results. (I should have mentioned earlier that col B may have decending or accending or numbers without order, bassically could be any values) Other than that your equation works fine. Please help to solve this little prob. thanks again. "Mike H" wrote: PLEASE HELP I did in your other post and have answered your follow up question "Raz" wrote: PLEASE HELP Need an equation in D1 to find the number immediately greater then number in col C, then copy the number beside from col B to D. For example: for 500 in col C, immediate greater number in col A is 569.3 and the number beside in col B is 79, then the equation in D1 should give 79. (Column B has numbers that sometime increasing sometime decreasing) A B C D 71.162 100 500 142.325 98 100 213.487 95 200 284.65 90 300 355.813 88 400 426.975 85 500 498.138 81 600 569.3 79 700 640.462 76 800 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an Equation
Thanks Again
"Mike H" wrote: Hi, Maybe this =LOOKUP(C1,A1:A9,OFFSET(A1:A9,1,1)) Mike "Raz" wrote: thanks Mike, I appriciate your help. but there is a little problem. If col B has numbers in decending order, the I am not getting the correct results. (I should have mentioned earlier that col B may have decending or accending or numbers without order, bassically could be any values) Other than that your equation works fine. Please help to solve this little prob. thanks again. "Mike H" wrote: PLEASE HELP I did in your other post and have answered your follow up question "Raz" wrote: PLEASE HELP Need an equation in D1 to find the number immediately greater then number in col C, then copy the number beside from col B to D. For example: for 500 in col C, immediate greater number in col A is 569.3 and the number beside in col B is 79, then the equation in D1 should give 79. (Column B has numbers that sometime increasing sometime decreasing) A B C D 71.162 100 500 142.325 98 100 213.487 95 200 284.65 90 300 355.813 88 400 426.975 85 500 498.138 81 600 569.3 79 700 640.462 76 800 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an Equation
Hi,
You can try this array formula (Ctrl+Shift+Enter). I am assuming that the first 2 columns are in range D9:E17 INDEX(D9:E17,MATCH(MIN(IF(($D$9:$D$17-$D$200),$D$9:$D$17-$D$20)),$D$9:$D$17-$D$20,0),2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Raz" wrote in message ... PLEASE HELP Need an equation in D1 to find the number immediately greater then number in col C, then copy the number beside from col B to D. For example: for 500 in col C, immediate greater number in col A is 569.3 and the number beside in col B is 79, then the equation in D1 should give 79. (Column B has numbers that sometime increasing sometime decreasing) A B C D 71.162 100 500 142.325 98 100 213.487 95 200 284.65 90 300 355.813 88 400 426.975 85 500 498.138 81 600 569.3 79 700 640.462 76 800 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need an Equation
On Wed, 18 Feb 2009 13:49:01 -0800, Raz wrote:
PLEASE HELP Need an equation in D1 to find the number immediately greater then number in col C, then copy the number beside from col B to D. For example: for 500 in col C, immediate greater number in col A is 569.3 and the number beside in col B is 79, then the equation in D1 should give 79. (Column B has numbers that sometime increasing sometime decreasing) A B C D 71.162 100 500 142.325 98 100 213.487 95 200 284.65 90 300 355.813 88 400 426.975 85 500 498.138 81 600 569.3 79 700 640.462 76 800 =INDEX($B$2:$B$10,MATCH(C2,$A$2:$A$10,1)+1) Of course, since the highest number in column A is 640.462, there will be no matches for numbers 700 and 800 in Column C. As written, the formula will return an error. What do you want to do if there is no higher number? --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with an equation | Excel Discussion (Misc queries) | |||
Equation Help | Excel Discussion (Misc queries) | |||
Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) | |||
Equation | Excel Discussion (Misc queries) | |||
How do I peg 4 col in an equation so when i add one col it only l. | Excel Worksheet Functions |