 Search:

# MS Office Forum

Ask Question   UnAnswered
RSS Feeds

# If nesting limits

Asked By: Conrad    Date: Jan 19    Category: MS Office    Views: 1735

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

Share:

### 11 Answers Found

Answer #1    Answered By: Jet Brown     Answered On: Jan 19

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.

Answer #2    Answered By: Ludkhannah Fischer     Answered On: Jan 19

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?
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.

Answer #3    Answered By: Sairish Kauser     Answered On: Jan 19

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?

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.

Answer #4    Answered By: Javairea Akram     Answered On: Jan 19

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

Answer #5    Answered By: Laura Rodriguez     Answered On: Jan 19

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

Answer #6    Answered By: Spiru Kelly     Answered On: Jan 19

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)

Answer #7    Answered By: Jenny Lopez     Answered On: Jan 19

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)

Answer #8    Answered By: Aiko Suzuki     Answered On: Jan 19

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)

Answer #9    Answered By: Ellen Simpson     Answered On: Jan 19

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 . . .

Answer #10    Answered By: Patricia Johnson     Answered On: Jan 19

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

Answer #11    Answered By: Calandre Bernard     Answered On: Jan 19

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.

Tagged: