Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 147
Default Look-up a value within an interval

I would be very grateful if someone could provide me with a formula that will
look up the closest minimum and maximum based on a value and bring back the
contents of a cell to the right. In the example below, I want the formula to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,069
Default Look-up a value within an interval

I'm sure there are more elegant solutions, but try this...

Assuming your lookup data is A1:D10, the type of interval (ARC) to look up
is in A15, and the value to look up is in A16:

In the cell where you want to return the answer, enter

=INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16),--($C$2:$C$10=A16),ROW($A$2:$A$10)),4))

Adjust the cell references as needed.

Hope this helps,

Hutch

"Christine" wrote:

I would be very grateful if someone could provide me with a formula that will
look up the closest minimum and maximum based on a value and bring back the
contents of a cell to the right. In the example below, I want the formula to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Look-up a value within an interval

Hi,

The best way to do this is by having the minimum and maximum values in 2
different columns (say A1:A10 and B1:B10). Also in a third column (say
C1:C10) enter numbers starting with 1 I.e. 1,2,3....

Now use the SUMPRODUCT() formula

=sumproduct((A1:A10<=9923)*(B1:B10=9923),C1;C10)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 147
Default Look-up a value within an interval

Thank you, Tom. However, the result that comes back is Result 4 when it
should be Result 2. I've also tried it with different references, but the
result of the Address portion of the formula comes back with a reference a
few rows below what it should be. Would you have any idea why that would be
happening?

"Tom Hutchins" wrote:

I'm sure there are more elegant solutions, but try this...

Assuming your lookup data is A1:D10, the type of interval (ARC) to look up
is in A15, and the value to look up is in A16:

In the cell where you want to return the answer, enter

=INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16),--($C$2:$C$10=A16),ROW($A$2:$A$10)),4))

Adjust the cell references as needed.

Hope this helps,

Hutch

"Christine" wrote:

I would be very grateful if someone could provide me with a formula that will
look up the closest minimum and maximum based on a value and bring back the
contents of a cell to the right. In the example below, I want the formula to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 147
Default Look-up a value within an interval

Thank you, Ashish. However, this brings back a result of 0.

"Ashish Mathur" wrote:

Hi,

The best way to do this is by having the minimum and maximum values in 2
different columns (say A1:A10 and B1:B10). Also in a third column (say
C1:C10) enter numbers starting with 1 I.e. 1,2,3....

Now use the SUMPRODUCT() formula

=sumproduct((A1:A10<=9923)*(B1:B10=9923),C1;C10)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default Look-up a value within an interval

Hi Christine

Tom's formula returns the correct result for me.
The only way I can see it returning a value 2 rows below the value required,
is if your data starts in Row 4, not in row 2 as Tom assumed.
Change $2 to $4 throughout the formula, and see what that does.
--
Regards
Roger Govier

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 147
Default Look-up a value within an interval

I don't know where my formula is wrong, because I still get the wrong result.
Please see below, where the data is from cells A1:D10, "ARC" in cell A15 and
9923 in cell A16.

Minimum Maximum
ARC 0 10,000 Result 1
ARC 4,000 10,000 Result 2
ARC 10,000 9,999,999 Result 3
ARD 0 10,000 Result 4
ARD 10,000 9,999,999 Result 5
DCC 0 15,000 Result 6
DCC 4,000 9,999,999 Result 7
DCC 15,000 Result 8
DCD 0 9,999,999 Result 9


ARC
9923 Result 4

=INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16),
--($C$2:$C$10=A16),ROW($A$2:$A$10)),4))




"Roger Govier" wrote:

Hi Christine

Tom's formula returns the correct result for me.
The only way I can see it returning a value 2 rows below the value required,
is if your data starts in Row 4, not in row 2 as Tom assumed.
Change $2 to $4 throughout the formula, and see what that does.
--
Regards
Roger Govier

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 147
Default Look-up a value within an interval

Actually, it seems to work for some entries but not others. I think the
formula does work.

"Roger Govier" wrote:

Hi Christine

Tom's formula returns the correct result for me.
The only way I can see it returning a value 2 rows below the value required,
is if your data starts in Row 4, not in row 2 as Tom assumed.
Change $2 to $4 throughout the formula, and see what that does.
--
Regards
Roger Govier

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,069
Default Look-up a value within an interval

Your sample data is inconsistent. For example, ARC 9923 falls into your
Result 1 range and also into your Result 2 range. The DCC ranges don't make
sense. I adjusted the data to the following when I was creating the formula:

Minimum Maximum
ARC 0 4,000 Result 1
ARC 4,000 10,000 Result 2
ARC 10,000 9,999,999 Result 3
ARD 0 10,000 Result 4
ARD 10,000 9,999,999 Result 5
DCC 0 4,000 Result 6
DCC 4,000 15,000 Result 7
DCC 15,000 9,999,999 Result 8
DCD 0 9,999,999 Result 9

Could that be why you are getting erratic results?

Hutch

"Christine" wrote:

Actually, it seems to work for some entries but not others. I think the
formula does work.

"Roger Govier" wrote:

Hi Christine

Tom's formula returns the correct result for me.
The only way I can see it returning a value 2 rows below the value required,
is if your data starts in Row 4, not in row 2 as Tom assumed.
Change $2 to $4 throughout the formula, and see what that does.
--
Regards
Roger Govier

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 147
Default Look-up a value within an interval

Unfortunately, there are other factors involved, and the limits cannot be
changed. It's also a matter of tiering. For example, if the building is
less than 5 years' old and the amount falls in between 4000 and 10000, it
gets tiered at level A; if the building is less than 5 years' old but the
amount falls between 1000 and 10000, it gets tiered at level B, etc. with
additional factors. What I want to do is concatenate all the factors and
bring back the appropriate tiering, and that means finding the closest
minimum and maximum based on the amount and the subject (e.g. ARC, DCC).

"Tom Hutchins" wrote:

Your sample data is inconsistent. For example, ARC 9923 falls into your
Result 1 range and also into your Result 2 range. The DCC ranges don't make
sense. I adjusted the data to the following when I was creating the formula:

Minimum Maximum
ARC 0 4,000 Result 1
ARC 4,000 10,000 Result 2
ARC 10,000 9,999,999 Result 3
ARD 0 10,000 Result 4
ARD 10,000 9,999,999 Result 5
DCC 0 4,000 Result 6
DCC 4,000 15,000 Result 7
DCC 15,000 9,999,999 Result 8
DCD 0 9,999,999 Result 9

Could that be why you are getting erratic results?

Hutch

"Christine" wrote:

Actually, it seems to work for some entries but not others. I think the
formula does work.

"Roger Govier" wrote:

Hi Christine

Tom's formula returns the correct result for me.
The only way I can see it returning a value 2 rows below the value required,
is if your data starts in Row 4, not in row 2 as Tom assumed.
Change $2 to $4 throughout the formula, and see what that does.
--
Regards
Roger Govier

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Look-up a value within an interval

Hi,

Try this

=sumproduct((A1:A10<9923)*(B1:B109923),C1;C10)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Christine" wrote in message
...
Thank you, Ashish. However, this brings back a result of 0.

"Ashish Mathur" wrote:

Hi,

The best way to do this is by having the minimum and maximum values in 2
different columns (say A1:A10 and B1:B10). Also in a third column (say
C1:C10) enter numbers starting with 1 I.e. 1,2,3....

Now use the SUMPRODUCT() formula

=sumproduct((A1:A10<=9923)*(B1:B10=9923),C1;C10)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula
that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the
formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,069
Default Look-up a value within an interval

You are getting Result4 for ARC 9923 because it falls into the ranges for
both Result1 and Result2. The SUMPRODUCT part of the formula returns the row
number of the solution. Because your min/max ranges are inconsistent, it is
returning 2 and 3, then adding them to get 5. Row 5 has Return4.

Hutch

"Christine" wrote:

Unfortunately, there are other factors involved, and the limits cannot be
changed. It's also a matter of tiering. For example, if the building is
less than 5 years' old and the amount falls in between 4000 and 10000, it
gets tiered at level A; if the building is less than 5 years' old but the
amount falls between 1000 and 10000, it gets tiered at level B, etc. with
additional factors. What I want to do is concatenate all the factors and
bring back the appropriate tiering, and that means finding the closest
minimum and maximum based on the amount and the subject (e.g. ARC, DCC).

"Tom Hutchins" wrote:

Your sample data is inconsistent. For example, ARC 9923 falls into your
Result 1 range and also into your Result 2 range. The DCC ranges don't make
sense. I adjusted the data to the following when I was creating the formula:

Minimum Maximum
ARC 0 4,000 Result 1
ARC 4,000 10,000 Result 2
ARC 10,000 9,999,999 Result 3
ARD 0 10,000 Result 4
ARD 10,000 9,999,999 Result 5
DCC 0 4,000 Result 6
DCC 4,000 15,000 Result 7
DCC 15,000 9,999,999 Result 8
DCD 0 9,999,999 Result 9

Could that be why you are getting erratic results?

Hutch

"Christine" wrote:

Actually, it seems to work for some entries but not others. I think the
formula does work.

"Roger Govier" wrote:

Hi Christine

Tom's formula returns the correct result for me.
The only way I can see it returning a value 2 rows below the value required,
is if your data starts in Row 4, not in row 2 as Tom assumed.
Change $2 to $4 throughout the formula, and see what that does.
--
Regards
Roger Govier

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 147
Default Look-up a value within an interval

Thank you, Tom.

"Tom Hutchins" wrote:

You are getting Result4 for ARC 9923 because it falls into the ranges for
both Result1 and Result2. The SUMPRODUCT part of the formula returns the row
number of the solution. Because your min/max ranges are inconsistent, it is
returning 2 and 3, then adding them to get 5. Row 5 has Return4.

Hutch

"Christine" wrote:

Unfortunately, there are other factors involved, and the limits cannot be
changed. It's also a matter of tiering. For example, if the building is
less than 5 years' old and the amount falls in between 4000 and 10000, it
gets tiered at level A; if the building is less than 5 years' old but the
amount falls between 1000 and 10000, it gets tiered at level B, etc. with
additional factors. What I want to do is concatenate all the factors and
bring back the appropriate tiering, and that means finding the closest
minimum and maximum based on the amount and the subject (e.g. ARC, DCC).

"Tom Hutchins" wrote:

Your sample data is inconsistent. For example, ARC 9923 falls into your
Result 1 range and also into your Result 2 range. The DCC ranges don't make
sense. I adjusted the data to the following when I was creating the formula:

Minimum Maximum
ARC 0 4,000 Result 1
ARC 4,000 10,000 Result 2
ARC 10,000 9,999,999 Result 3
ARD 0 10,000 Result 4
ARD 10,000 9,999,999 Result 5
DCC 0 4,000 Result 6
DCC 4,000 15,000 Result 7
DCC 15,000 9,999,999 Result 8
DCD 0 9,999,999 Result 9

Could that be why you are getting erratic results?

Hutch

"Christine" wrote:

Actually, it seems to work for some entries but not others. I think the
formula does work.

"Roger Govier" wrote:

Hi Christine

Tom's formula returns the correct result for me.
The only way I can see it returning a value 2 rows below the value required,
is if your data starts in Row 4, not in row 2 as Tom assumed.
Change $2 to $4 throughout the formula, and see what that does.
--
Regards
Roger Govier

"Christine" wrote in message
...
I would be very grateful if someone could provide me with a formula that
will
look up the closest minimum and maximum based on a value and bring back
the
contents of a cell to the right. In the example below, I want the formula
to
look up 9,923, within the ARC intervals, and bring back Result 2.

ARC 9,923 Formula will bring back Result 2

A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9

Thank you in advance for your help.

Chris

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how i create a interval demetrio29 Excel Discussion (Misc queries) 3 September 23rd 08 10:50 PM
interval formula xposedphotography Excel Discussion (Misc queries) 1 April 6th 08 05:51 PM
Confidence Interval cwbecker Excel Worksheet Functions 1 June 29th 06 09:29 PM
How to find row in interval? [email protected] Excel Discussion (Misc queries) 2 May 24th 06 09:27 AM
interpolation in a particular interval (HELP) uriel78 Excel Discussion (Misc queries) 1 February 19th 05 06:16 PM


All times are GMT +1. The time now is 11:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"