| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested If Loop Limitation for Excel 2003
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(an d(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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If Loop Limitation for Excel 2003
Use
=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" wrote: 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(an d(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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If Loop Limitation for Excel 2003
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 "Sheeloo" wrote: Use =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" wrote: 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(an d(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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If Loop Limitation for Excel 2003
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 "raj74" wrote: 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 "Sheeloo" wrote: Use =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" wrote: 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(an d(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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If Loop Limitation for Excel 2003
In that case, use this
=LOOKUP(A14,{0,8.1,10.1,12.1,14.1,16.1,18.1,20.1,2 5.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" wrote: 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 "Sheeloo" wrote: Use =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" wrote: 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(an d(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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If Loop Limitation for Excel 2003
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. "francis" wrote: 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 "raj74" wrote: 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 "Sheeloo" wrote: Use =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" wrote: 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(an d(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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If Loop Limitation for Excel 2003
I was thinking on that line. Thanks very much. Have a Good Day.
"Sheeloo" wrote: In that case, use this =LOOKUP(A14,{0,8.1,10.1,12.1,14.1,16.1,18.1,20.1,2 5.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" wrote: 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 "Sheeloo" wrote: Use =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" wrote: 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(an d(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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If Loop Limitation for Excel 2003
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. "raj74" wrote: 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. "francis" wrote: 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 "raj74" wrote: 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 "Sheeloo" wrote: Use =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" wrote: 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(an d(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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If Loop Limitation for Excel 2003
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)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "raj74" wrote: 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. "francis" wrote: 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 "raj74" wrote: 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 "Sheeloo" wrote: Use =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" wrote: 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(an d(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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If Loop Limitation for Excel 2003
Hi,
Here is a completely different solution based on the fact that each result is an even number 2,4,6... =EVEN(A1) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "raj74" wrote: 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(an d(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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| nested function limitation | Excel Discussion (Misc queries) | |||
| Nested loop | Excel Worksheet Functions | |||
| The 7 nested If() function Limitation. Is there anouther way? | Excel Worksheet Functions | |||
| Nested Loop Link | Excel Worksheet Functions | |||
| Nested Subtotals in Excel 2003 -Solution | Excel Discussion (Misc queries) | |||
Linear Mode
