Nested If Loop Limitation for Excel 2003

[raj74]

I have to express the following logic.

Available Plate Thickness 8, 10, 12, 14, 16, 18, 20, 25, 28, 30, 32

The logic is if my calculated thickness is, THKcal < 8, Thk Provided,
THKprov = 8

8< THKcal <10
THKprov = 10

10< THKcal <12
THKprov = 12


........................... ................


......................... ....................

28< THKcal <30
THKprov = 30

30< THKcal
THKprov = 32

Now this logic I have tried as
=if(THKcal<8,8,if(and(8<THKcal,THKcal<10),10,if(and(10<THKcal,THKcal<12),12,........................if(and(28<THKcal,THKcal<30),30,32))...)
But problem is I can no go beyond certain amount of limit for the nested loop.
What is the solution. Any easier way to express it. Anyway thanks.

Regards

 

[Sheeloo]

Us
=LOOKUP(A1,{0,8,10,12,14,16,18,20,25,28,30,32},{8,10,12,14,16,18,20,25,28,30,32,"Error"})

where A1 contains the THKcal value... It will give show ERROR if A1 has the
value 32 or above

 

[raj74]

Thanks, but probably I wrote little bit wrong in expressing the algorithm.
Corrected will be
THKcal (lesss or equal) =< 8, Thk Provided, THKprov = 8
8< THKcal =<10 THKprov
= 10

And So on.

In your case if thk calculated is 8mm, thickness is coming 10mm which should
be 8 mm.

Regards

 

[francis]

Create a table with the columns on these value and
place a Lookup formula...Vlookup or Index Match will be
able to solve this easily.

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[Sheeloo]

In that case, use this
=LOOKUP(A14,{0,8.1,10.1,12.1,14.1,16.1,18.1,20.1,25.1,28.1,30.1,32.1},{8,10,12,14,16,18,20,25,28,30,32,"Error"})

you may change 8.1 to 8.001 if you have values like 8.05...

 

[raj74]

but whatever i understand vlookup only search for a particular value which is
listed in the table. Then how it can find a inbetween value.
Like for example if i have a table say col A={8,10,12,14...............),
Now for calculated value 9, how vlookup will find, 9 is not available in the
table. And what will be col B.
But answer will be for calculated value of say 8.5,8.8 or may be 9,10 it
will be 10, when it exceeded 10, it will have the next higher value
available, i.e. 12.
Hope I explain correctly.



Sheelo has everything right except when it is equal to the value provided.

 

[raj74]

I was thinking on that line. Thanks very much. Have a Good Day.

 

[Sheeloo]

From Lookup HELP

"If the LOOKUP function can't find the lookup_value, the function matches
the largest value in lookup_vector that is less than or equal to
lookup_value.
If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP
returns the #N/A error value. "

That is why values have to be ascending order...

Pl. mark the question as answered.

 

[Shane Devenshire]

Hi,

VLOOKUP can be an exact match (last argument FALSE) or approximate match
(last argument TRUE. However, you are trying to round up so you can use
MATCH as follows:

So with a table like

10
8
6
4
2
....

Just a list of the desired return values (THKprov) in descending order, say
these are in H1:H100. And suppose your Thk number is in A1, then the formula
is

=INDEX(H1:H100,MATCH(A1,H1:H100,-1))

 

[Shane Devenshire]

Hi,

Here is a completely different solution based on the fact that each result
is an even number 2,4,6...

=EVEN(A1)