ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Need an Equation (https://www.excelbanter.com/excel-worksheet-functions/221525-need-equation.html)

RAZ

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


Mike H

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


RAZ

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


Mike H

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


RAZ

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


Ashish Mathur[_2_]

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


Ron Rosenfeld

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


All times are GMT +1. The time now is 05:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com