Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I am looking for a solution to this problem.

I Input a # in Cell C5, I need to have excel find the number on a

chart that is the next number larger than C5.

There are 49 variables in the chart. The numbers in the chart do not

have a pattern or are a whole number.

I have tried an If function and it works until I have trouble with the

nesting problem.

=IF(C5<=1,0)+IF(C5<=6,6)+IF(C5<=11.75,11.75)+IF(C5<=23.25,23.25)+IF(C5<=29,29)+I\

F(C5<=35,35)+IF(C5<=40.75,40.75)

Here is the chart.

6

11.75

17.5

23.25

29

35

40.75

46.5

52.26

58

64

69.75

75.5

81.5

87

93

98

104.75

110.5

116.26

122

128

133.75

139.5

145.25

151

157

162.75

168.5

174.6

180

186

192

197.75

203.5

209.25

215

221

226.75

232.5

238.25

244

250

255.75

I have tried Vlookup but have had no luck.

I would appreciate some direction on this

Here is a custom function that gives the desired output. I only tested with the

data you provided (haven't tested it with negative numbers in the range, etc.):

Public Function NextBiggest(ValRng As Range, ChkRng As Range) As Double

Dim c As Range, tmp As Double

tmp = Application.WorksheetFunction.Max(ChkRng)

For Each c In ChkRng

If c.Value > ValRng.Value Then

If c.Value < tmp Then

tmp = c.Value

End If

End If

Next c

NextBiggest = tmp

End Function

You call the function like this:

=NextBiggest(C5,C10:C53)

In this example, the chart numbers are in cells C10:C53.

I think I need a bit more information.

(first of all, these are "values", not "variables")

You said that the values are in a "chart".

Do you really mean that they are in a list?

A chart is an image that shows a graphical representation of values.

It looks like you have a column of numbers (you gave 44 of them)

and you want to find the next largest number from a given number.

You said "the numbers do not have a pattern or are a whole number"

I guess this means that they are basically a set of numbers, either

whole or decimal, not text...

OK...

Let's say, the number you supply is "55".

Vlookup returns 52.26 instead of 58.

Are you set on using excel functions, not VBA?

What kind of flexibility do you have with the sheet?

Can you ensure that the values are sorted in ascending order?

Can you add row numbers?

I did this for A2:B45 ::

A B

6.002

11.753

17.504

23.255

29.006

35.007

40.758

46.509

52.2610

58.0011

64.0012

69.7513

75.5014

81.5015

87.0016

93.0017

98.0018

104.7519

110.5020

116.2621

122.0022

128.0023

133.7524

139.5025

145.2526

151.0027

157.0028

162.7529

168.5030

174.6031

180.0032

186.0033

192.0034

197.7535

203.5036

209.2537

215.0038

221.0039

226.7540

232.5041

238.2542

244.0043

250.0044

255.7545

(I used =ROW(A2) in column B so that it was more flexible)

Next, in D5, I put:

=OFFSET(INDIRECT(ADDRESS(VLOOKUP(C5,A2:V45,2,TRUE),1)),1,0)

This uses

VLookup to find the next LOWER number ("TRUE" enables this)

I used ADDRESS to return the cell address.

I used INDIRECT to ensure that it used the ADDRESS instead of the VALUE at the

address

I used Offset to move to the next row (1 row, 0 columns) and returned the value

there.

So, in C5, I put the number 55.

In D5, it returns 58.

is this even close to what you were looking for?

If the numbers are not in order, I can write a function that can loop through

the values and return the next greater value, but it requires the use of VBA

rather then Excel functions.

There is an IF nesting limit that is something like seven for excel up to

2003. I believe 2007 has relaxed that significantly.

I am having severe problems with your description:

- "Chart" is a graphic item in Excel, can I assume you mean "column" on a

worksheet?

- "Variable" is a programming term - do you mean "value"?

- There are not 49 values in your message - there are only 44.

- You say you want to find the number that is the next larger, but your IF

statement will return a value that is the same as C5. Which is correct?

- Your first IF clause is IF(C5<=1,0) which is not consistent with your

stated requirement or with the rest of your IF statement. Should it be

IF(C5<=1,1)?

You say you've tried VLookUp but have had no luck. What VLookUp formulae

have you tried? What did they do wrong?

A solution to your stated requirement "I need to have excel find the number

on a chart that is the next number larger than C5" is

=INDEX($A$1:$A$44,IF(C5<$A$1,0,MATCH(C5,$A$1:$A$44,TRUE))+1,1)

This will grab the first number from the range A1:A44 that is greater than

the number in C5.

So C5 with 1-5.9999 will get the first one (6); C5 with 6-11.749999 will get

the second one (11.75), etc.

The limit is exactly seven........

I heard it has been seven for a while.............

Another solution using the SumIf & Offset functions.

By Chart; I am guessing you actually mean a 'list' or 'column' of continuous

vertical cells; I'll call it the 'LookUpList'. I placed this LookUpList in cell

range D1 to D44. The LookUpList appears to be in ascending order even though

they are random 'floating' & 'integer' values. This is good! Now I can ask; How

many numbers in the LookUpList are 'less than' or 'equal to' the value of

interest (In your case C5); which I will call the 'SearchCount'.

With that I have the following expression:

SearchCount=COUNTIF(D1:D44,"<=" & VALUE(C5))

SearchCount +1 will be the next value in the list that you are really attempting

to acquire; I'll call this 'TheNumberYouWant' !

Now the Offset function:

TheNumberYouWant=OFFSET(TopCell of the LookUpList , SearchCount , 0)

If you want this value to reside in cell C6, the function translates to:

C6=OFFSET(D1,COUNTIF(D1:D44,"<=" & VALUE(C5)) ,0)

A couple of small points.

1. If Nick's original formula was written as I guess he was intending

(since there is no nesting in what he wrote) it might beome:

=IF(C5<=1,0,IF(C5<=6,6,IF(C5<=11.75,11.75,IF(C5<=23.25,23.25,IF(C5<=29,29,IF(C5<\

=35,35,IF(C5<=40.75,40.75)))))))

(he seems to have missed out the 17.5). Entering 22 into C5 results in

23.25. Fine. Entering 23.25 in C5 also returns 23.25. Assuming he

intends this, then to get the same result with AJ's formula it should

lose the = sign from the middle (I know this goes against the textual

description of what Nick said he wants).

2. To cope with values in any order, another small (oh dear..) tweak

to AJ's formula might be:

=SMALL(D1:D44,COUNTIF(D1:D44,"<" & VALUE(C5))+1)

When C6 contains the value 64, and you a looking for 64; use:

C6=OFFSET(D1, COUNTIF(D1: D44,"<" & VALUE(C5)) ,0)

When C6 contains the value 64, and you a looking for 69.75; use:

C6=OFFSET(D1, COUNTIF(D1: D44,"<=" & VALUE(C5)) ,0)

This is making the assumption your LookUpList IS in ascending order as you

present in your example.

One observation: When C5 contains 3, it is less than the smallest value in your

LookUpList. I would add additional logic to advise your number in C5 does not

'enter' the LookUpList. I'll assign this value as zero "0".

The logic expression looks like:

IsC5InTheLookUpList?=COUNTIF(D1:D44,"<=" & VALUE(C5))>0

This returns "True" when the CountIf( ) expression is greater than zero.

This returns "False" when the CountIf( ) expression is <= to zero.

A "True" result indicates there is a value C5 can be compared against in the

LookUpList. A "False" result indicates you are below the LookUpList smallest

value. Does that make sense? If so, make a change to the 'corrected' expression

from above:

C6=(OFFSET(D1, COUNTIF(D1: D44,"<" & VALUE(C5)) ,0)) * (IsC5InTheLookUpList?)

for a final expression:

C6=(OFFSET(D1, COUNTIF(D1: D44,"<" & VALUE(C5)) ,0)) * (COUNTIF(D1:D44,"<=" &

VALUE(C5))>0)

For what it's worth . .. Chip Pearson has an entire article how to

go past the seven limit . . .

http://www.cpearson.com/excel/nested.htm

That might have been mentioned in this thread, but I didn't

notice it . . .

I was happy with my solution until I saw yours. Very

clean!!

Thank You for the feed back.

I am sorry for the mislabling and the wrong amount on the list.

I will work at the right term next time.

Some times thing are some much simpler than I make them out to be.

Didn't find what you were looking for?
Find more on If nesting limits
Or get search suggestion and latest updates.

Related Topics:

- Limited with IF Then Else Nesting
- Help with "Nested Do While Loop"
- creating a figure using nested loops
- Dynamic userform - create nested frames?
- Nested For Loop Plz Help
- Problem in nested Custom Tags
- Help with Objects and Nested Collections
- Help with nested if statement - with and & or
- Nesting a repeater in a datalist
- Dropdown nested in repeater
- Dropdown nested in repeater
- please help nested exception
- nested class
- Limit size of swing frame & or components
- ASP.NET file upload and size limit?
- Is it possible to have limited number of rows and columns in a sheet
- VBA Max Line Limit
- Variable scope & Lifetime limits causing trouble
- Limit in number of patterns in MessageFormat
- Outlook Express shell string limit?
- Access Time-out period.... Or session variable time limit?
- Access Time-out period.... Or session variable time limit?