Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
Good morning all,
I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
Where arre you placing the macro? Do you have to lookup test (11-13) and
Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
Joel,
I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
I broke the calculation up into 3 cells to make it easier to understand
in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
Hey Joel,
I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
I checked and there is no limits on the number of cells in the Match
function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
I am using a test number of 289 and the Upper Quality limit is 12.5 and it is
giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
th eformulas I gave you should work if they were adjusted properly for the
range of data you have. either 1) try using the evaluate formula to find out which value in the formula is producing the N/A 2) Post the formula and let me know the ranges of where your tables are a) the Header Row b) the header column c) Range of the data You aren't finding the number 289 on the Header column or the code is not finding 12.5 in the Header Row. Make sure the Range of the data in the formula like =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) A14:CX514 the size of your table including header row and header column A15:A514 is the header row in column A B14:CX14 is the header column in row 14 A B C D E F Row 14 100 98 97 96 95 Row 15 11 0.9 0.8 0.6 0.3 0.1 Row 16 12 1.8 1.7 1.5 1.3 1.0 Row 17 13 2.9 2.8 2.4 2.2 2.1 "Eric" wrote: I am using a test number of 289 and the Upper Quality limit is 12.5 and it is giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
I am sorry i am making this so difficult but Here is another example:
ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 If I use test #12 and look for number 1.8 all is good and 100% is returned. If I use test #13 and look for 2.2 a 97 is returned. This is a good thing. If I use test #12 and look for number 5, I get the error. Because the number 5 is greater than 1.8. What I want to happen is use the 1.8 if the number you are looking for is greater than the number under 100%. Here is the formula that I am using: =INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1)) B4:CX4 ~~ % row A5:A504~~ test numbers C409 ~~ #of Tests run (the data goes up to 500 tests but I need it to use 500 if tests are greater than 500) C411 ~~ Is the number I need to find in the range I hope this makes sense.....Again I am sorry Eric "Joel" wrote: th eformulas I gave you should work if they were adjusted properly for the range of data you have. either 1) try using the evaluate formula to find out which value in the formula is producing the N/A 2) Post the formula and let me know the ranges of where your tables are a) the Header Row b) the header column c) Range of the data You aren't finding the number 289 on the Header column or the code is not finding 12.5 in the Header Row. Make sure the Range of the data in the formula like =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) A14:CX514 the size of your table including header row and header column A15:A514 is the header row in column A B14:CX14 is the header column in row 14 A B C D E F Row 14 100 98 97 96 95 Row 15 11 0.9 0.8 0.6 0.3 0.1 Row 16 12 1.8 1.7 1.5 1.3 1.0 Row 17 13 2.9 2.8 2.4 2.2 2.1 "Eric" wrote: I am using a test number of 289 and the Upper Quality limit is 12.5 and it is giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
I think the answer is to Cap the number at 100% if the number is larger than
the number i the first column using an If statement. The 1st part of the if checks the value in column 1 of the appropriate row with Sheet5!C411 I also think you should change from:C409 to:Sheet5!C409 in the formula below. =IF(Sheet5!C411=INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),1),100,INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1))) "Eric" wrote: I am sorry i am making this so difficult but Here is another example: ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 If I use test #12 and look for number 1.8 all is good and 100% is returned. If I use test #13 and look for 2.2 a 97 is returned. This is a good thing. If I use test #12 and look for number 5, I get the error. Because the number 5 is greater than 1.8. What I want to happen is use the 1.8 if the number you are looking for is greater than the number under 100%. Here is the formula that I am using: =INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1)) B4:CX4 ~~ % row A5:A504~~ test numbers C409 ~~ #of Tests run (the data goes up to 500 tests but I need it to use 500 if tests are greater than 500) C411 ~~ Is the number I need to find in the range I hope this makes sense.....Again I am sorry Eric "Joel" wrote: th eformulas I gave you should work if they were adjusted properly for the range of data you have. either 1) try using the evaluate formula to find out which value in the formula is producing the N/A 2) Post the formula and let me know the ranges of where your tables are a) the Header Row b) the header column c) Range of the data You aren't finding the number 289 on the Header column or the code is not finding 12.5 in the Header Row. Make sure the Range of the data in the formula like =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) A14:CX514 the size of your table including header row and header column A15:A514 is the header row in column A B14:CX14 is the header column in row 14 A B C D E F Row 14 100 98 97 96 95 Row 15 11 0.9 0.8 0.6 0.3 0.1 Row 16 12 1.8 1.7 1.5 1.3 1.0 Row 17 13 2.9 2.8 2.4 2.2 2.1 "Eric" wrote: I am using a test number of 289 and the Upper Quality limit is 12.5 and it is giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
Joel,
I'm not sure if you saw the post from 2 days ago so I decided to repost it as a reply. Sorry if this is a double post. The last post didn't work. I am trying to figure this out so can you help me on breaking down the formula =Index(offset('PWL Number'!B4:CX4,Match(c409,'PWL Number'!A5:A504,0), Does this formula find which row the test number is in and mark it as a reference row? Match(C406,Offset('PWL Number'!B4:CX4,Match(C409,'PWL Number'!A5:A504,0),),-1) Does this find the number that I need in the row from the first part of the formula? If so what does the ending mean, ),)-1)? If my thought process is correct then the second part of the formula is where I need it to read "if the number is greater than the largest number in that line then use the largest number in that line or else use the number matching or close to the number found in cell C406". I hope this makes sense.....Refere to the last posting for the rows and numbers Eric "Joel" wrote: I think the answer is to Cap the number at 100% if the number is larger than the number i the first column using an If statement. The 1st part of the if checks the value in column 1 of the appropriate row with Sheet5!C411 I also think you should change from:C409 to:Sheet5!C409 in the formula below. =IF(Sheet5!C411=INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),1),100,INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1))) "Eric" wrote: I am sorry i am making this so difficult but Here is another example: ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 If I use test #12 and look for number 1.8 all is good and 100% is returned. If I use test #13 and look for 2.2 a 97 is returned. This is a good thing. If I use test #12 and look for number 5, I get the error. Because the number 5 is greater than 1.8. What I want to happen is use the 1.8 if the number you are looking for is greater than the number under 100%. Here is the formula that I am using: =INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1)) B4:CX4 ~~ % row A5:A504~~ test numbers C409 ~~ #of Tests run (the data goes up to 500 tests but I need it to use 500 if tests are greater than 500) C411 ~~ Is the number I need to find in the range I hope this makes sense.....Again I am sorry Eric "Joel" wrote: th eformulas I gave you should work if they were adjusted properly for the range of data you have. either 1) try using the evaluate formula to find out which value in the formula is producing the N/A 2) Post the formula and let me know the ranges of where your tables are a) the Header Row b) the header column c) Range of the data You aren't finding the number 289 on the Header column or the code is not finding 12.5 in the Header Row. Make sure the Range of the data in the formula like =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) A14:CX514 the size of your table including header row and header column A15:A514 is the header row in column A B14:CX14 is the header column in row 14 A B C D E F Row 14 100 98 97 96 95 Row 15 11 0.9 0.8 0.6 0.3 0.1 Row 16 12 1.8 1.7 1.5 1.3 1.0 Row 17 13 2.9 2.8 2.4 2.2 2.1 "Eric" wrote: I am using a test number of 289 and the Upper Quality limit is 12.5 and it is giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
Index needs an array, a Row, and, a Column like this
=index('PWL Number'!B4:CX4, 1,10) Row 1 columnn 10 the above formula just uses the First Row of the table. We use Offset to get the correct row of the table so Match(c409,'PWL Number'!A5:A504,0) searches column A to find the item that matches c409 and returns the index number of the item in the table. row 4 is the header Cell A5 = 11 Cell A6 = 12 Cell A7 = 13 Looking for 11 will return 1, looking for 12 will return 2, looking for 13 will return 3 So if you were loking for 12 the offset would give you a row offset of 2 and a column offset of zero These three statement are the substituions excel will make OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0) OFFSET('PWL Number'!$B$4:$CX$4,2,0) 'PWL Number'!$B$6:$CX$6 Now you want to find the correct column so you do another match to get the index match(C411,'PWL Number'!$B$6:$CX$6) - 1 Then do an offset with the value returned from the formula above to get the value in the header row 100 down to 1 OFFSET('PWL Number'!$B$4:$CX$4,0,2) Notice the row offset of 0. The minus 1 above is to get the previous column. This is where your problem was if your number was greater the 100%. Subtracting one got you outside the table. that is why I added a test to see if the value was greater than the value in the 1st column. When it was larger than the 1st column. I think the problem is with your formula. What do you want returned when the value is greater than 100? Do you want to calculate some ratio rather than return 100? This can be easily fixed. "Eric" wrote: Joel, I'm not sure if you saw the post from 2 days ago so I decided to repost it as a reply. Sorry if this is a double post. The last post didn't work. I am trying to figure this out so can you help me on breaking down the formula =Index(offset('PWL Number'!B4:CX4,Match(c409,'PWL Number'!A5:A504,0), Does this formula find which row the test number is in and mark it as a reference row? Match(C406,Offset('PWL Number'!B4:CX4,Match(C409,'PWL Number'!A5:A504,0),),-1) Does this find the number that I need in the row from the first part of the formula? If so what does the ending mean, ),)-1)? If my thought process is correct then the second part of the formula is where I need it to read "if the number is greater than the largest number in that line then use the largest number in that line or else use the number matching or close to the number found in cell C406". I hope this makes sense.....Refere to the last posting for the rows and numbers Eric "Joel" wrote: I think the answer is to Cap the number at 100% if the number is larger than the number i the first column using an If statement. The 1st part of the if checks the value in column 1 of the appropriate row with Sheet5!C411 I also think you should change from:C409 to:Sheet5!C409 in the formula below. =IF(Sheet5!C411=INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),1),100,INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1))) "Eric" wrote: I am sorry i am making this so difficult but Here is another example: ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 If I use test #12 and look for number 1.8 all is good and 100% is returned. If I use test #13 and look for 2.2 a 97 is returned. This is a good thing. If I use test #12 and look for number 5, I get the error. Because the number 5 is greater than 1.8. What I want to happen is use the 1.8 if the number you are looking for is greater than the number under 100%. Here is the formula that I am using: =INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1)) B4:CX4 ~~ % row A5:A504~~ test numbers C409 ~~ #of Tests run (the data goes up to 500 tests but I need it to use 500 if tests are greater than 500) C411 ~~ Is the number I need to find in the range I hope this makes sense.....Again I am sorry Eric "Joel" wrote: th eformulas I gave you should work if they were adjusted properly for the range of data you have. either 1) try using the evaluate formula to find out which value in the formula is producing the N/A 2) Post the formula and let me know the ranges of where your tables are a) the Header Row b) the header column c) Range of the data You aren't finding the number 289 on the Header column or the code is not finding 12.5 in the Header Row. Make sure the Range of the data in the formula like =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) A14:CX514 the size of your table including header row and header column A15:A514 is the header row in column A B14:CX14 is the header column in row 14 A B C D E F Row 14 100 98 97 96 95 Row 15 11 0.9 0.8 0.6 0.3 0.1 Row 16 12 1.8 1.7 1.5 1.3 1.0 Row 17 13 2.9 2.8 2.4 2.2 2.1 "Eric" wrote: I am using a test number of 289 and the Upper Quality limit is 12.5 and it is giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
First let me say thank you for the explanations. Now you asked what number I
wanted to retreave or use ...... Lets use the example from 12/13/07 ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Try this example........ test #12 find number 3.0~~ I would have to use the number 1.8 in the equation because it is the largest number found. Another...... test #12 find number (negative) -3.0~~ not there so I need to use 1.0 in in the equation. Same would go for the Percentages. Does this shine light on it? Eric "Joel" wrote: Index needs an array, a Row, and, a Column like this =index('PWL Number'!B4:CX4, 1,10) Row 1 columnn 10 the above formula just uses the First Row of the table. We use Offset to get the correct row of the table so Match(c409,'PWL Number'!A5:A504,0) searches column A to find the item that matches c409 and returns the index number of the item in the table. row 4 is the header Cell A5 = 11 Cell A6 = 12 Cell A7 = 13 Looking for 11 will return 1, looking for 12 will return 2, looking for 13 will return 3 So if you were loking for 12 the offset would give you a row offset of 2 and a column offset of zero These three statement are the substituions excel will make OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0) OFFSET('PWL Number'!$B$4:$CX$4,2,0) 'PWL Number'!$B$6:$CX$6 Now you want to find the correct column so you do another match to get the index match(C411,'PWL Number'!$B$6:$CX$6) - 1 Then do an offset with the value returned from the formula above to get the value in the header row 100 down to 1 OFFSET('PWL Number'!$B$4:$CX$4,0,2) Notice the row offset of 0. The minus 1 above is to get the previous column. This is where your problem was if your number was greater the 100%. Subtracting one got you outside the table. that is why I added a test to see if the value was greater than the value in the 1st column. When it was larger than the 1st column. I think the problem is with your formula. What do you want returned when the value is greater than 100? Do you want to calculate some ratio rather than return 100? This can be easily fixed. "Eric" wrote: Joel, I'm not sure if you saw the post from 2 days ago so I decided to repost it as a reply. Sorry if this is a double post. The last post didn't work. I am trying to figure this out so can you help me on breaking down the formula =Index(offset('PWL Number'!B4:CX4,Match(c409,'PWL Number'!A5:A504,0), Does this formula find which row the test number is in and mark it as a reference row? Match(C406,Offset('PWL Number'!B4:CX4,Match(C409,'PWL Number'!A5:A504,0),),-1) Does this find the number that I need in the row from the first part of the formula? If so what does the ending mean, ),)-1)? If my thought process is correct then the second part of the formula is where I need it to read "if the number is greater than the largest number in that line then use the largest number in that line or else use the number matching or close to the number found in cell C406". I hope this makes sense.....Refere to the last posting for the rows and numbers Eric "Joel" wrote: I think the answer is to Cap the number at 100% if the number is larger than the number i the first column using an If statement. The 1st part of the if checks the value in column 1 of the appropriate row with Sheet5!C411 I also think you should change from:C409 to:Sheet5!C409 in the formula below. =IF(Sheet5!C411=INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),1),100,INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1))) "Eric" wrote: I am sorry i am making this so difficult but Here is another example: ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 If I use test #12 and look for number 1.8 all is good and 100% is returned. If I use test #13 and look for 2.2 a 97 is returned. This is a good thing. If I use test #12 and look for number 5, I get the error. Because the number 5 is greater than 1.8. What I want to happen is use the 1.8 if the number you are looking for is greater than the number under 100%. Here is the formula that I am using: =INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1)) B4:CX4 ~~ % row A5:A504~~ test numbers C409 ~~ #of Tests run (the data goes up to 500 tests but I need it to use 500 if tests are greater than 500) C411 ~~ Is the number I need to find in the range I hope this makes sense.....Again I am sorry Eric "Joel" wrote: th eformulas I gave you should work if they were adjusted properly for the range of data you have. either 1) try using the evaluate formula to find out which value in the formula is producing the N/A 2) Post the formula and let me know the ranges of where your tables are a) the Header Row b) the header column c) Range of the data You aren't finding the number 289 on the Header column or the code is not finding 12.5 in the Header Row. Make sure the Range of the data in the formula like =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) A14:CX514 the size of your table including header row and header column A15:A514 is the header row in column A B14:CX14 is the header column in row 14 A B C D E F Row 14 100 98 97 96 95 Row 15 11 0.9 0.8 0.6 0.3 0.1 Row 16 12 1.8 1.7 1.5 1.3 1.0 Row 17 13 2.9 2.8 2.4 2.2 2.1 "Eric" wrote: I am using a test number of 289 and the Upper Quality limit is 12.5 and it is giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
This is your formula
(((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 If I sustitue the new number I get (((X-100)/(3.0-1.8)) * ((Y-1.8)))+100=? What do you use for X & Y? I'm thinking either 100 * ((3.0 - 1.8)/1.8) or 100 * (3.0/1.8) "Eric" wrote: First let me say thank you for the explanations. Now you asked what number I wanted to retreave or use ...... Lets use the example from 12/13/07 ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Try this example........ test #12 find number 3.0~~ I would have to use the number 1.8 in the equation because it is the largest number found. Another...... test #12 find number (negative) -3.0~~ not there so I need to use 1.0 in in the equation. Same would go for the Percentages. Does this shine light on it? Eric "Joel" wrote: Index needs an array, a Row, and, a Column like this =index('PWL Number'!B4:CX4, 1,10) Row 1 columnn 10 the above formula just uses the First Row of the table. We use Offset to get the correct row of the table so Match(c409,'PWL Number'!A5:A504,0) searches column A to find the item that matches c409 and returns the index number of the item in the table. row 4 is the header Cell A5 = 11 Cell A6 = 12 Cell A7 = 13 Looking for 11 will return 1, looking for 12 will return 2, looking for 13 will return 3 So if you were loking for 12 the offset would give you a row offset of 2 and a column offset of zero These three statement are the substituions excel will make OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0) OFFSET('PWL Number'!$B$4:$CX$4,2,0) 'PWL Number'!$B$6:$CX$6 Now you want to find the correct column so you do another match to get the index match(C411,'PWL Number'!$B$6:$CX$6) - 1 Then do an offset with the value returned from the formula above to get the value in the header row 100 down to 1 OFFSET('PWL Number'!$B$4:$CX$4,0,2) Notice the row offset of 0. The minus 1 above is to get the previous column. This is where your problem was if your number was greater the 100%. Subtracting one got you outside the table. that is why I added a test to see if the value was greater than the value in the 1st column. When it was larger than the 1st column. I think the problem is with your formula. What do you want returned when the value is greater than 100? Do you want to calculate some ratio rather than return 100? This can be easily fixed. "Eric" wrote: Joel, I'm not sure if you saw the post from 2 days ago so I decided to repost it as a reply. Sorry if this is a double post. The last post didn't work. I am trying to figure this out so can you help me on breaking down the formula =Index(offset('PWL Number'!B4:CX4,Match(c409,'PWL Number'!A5:A504,0), Does this formula find which row the test number is in and mark it as a reference row? Match(C406,Offset('PWL Number'!B4:CX4,Match(C409,'PWL Number'!A5:A504,0),),-1) Does this find the number that I need in the row from the first part of the formula? If so what does the ending mean, ),)-1)? If my thought process is correct then the second part of the formula is where I need it to read "if the number is greater than the largest number in that line then use the largest number in that line or else use the number matching or close to the number found in cell C406". I hope this makes sense.....Refere to the last posting for the rows and numbers Eric "Joel" wrote: I think the answer is to Cap the number at 100% if the number is larger than the number i the first column using an If statement. The 1st part of the if checks the value in column 1 of the appropriate row with Sheet5!C411 I also think you should change from:C409 to:Sheet5!C409 in the formula below. =IF(Sheet5!C411=INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),1),100,INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1))) "Eric" wrote: I am sorry i am making this so difficult but Here is another example: ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 If I use test #12 and look for number 1.8 all is good and 100% is returned. If I use test #13 and look for 2.2 a 97 is returned. This is a good thing. If I use test #12 and look for number 5, I get the error. Because the number 5 is greater than 1.8. What I want to happen is use the 1.8 if the number you are looking for is greater than the number under 100%. Here is the formula that I am using: =INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1)) B4:CX4 ~~ % row A5:A504~~ test numbers C409 ~~ #of Tests run (the data goes up to 500 tests but I need it to use 500 if tests are greater than 500) C411 ~~ Is the number I need to find in the range I hope this makes sense.....Again I am sorry Eric "Joel" wrote: th eformulas I gave you should work if they were adjusted properly for the range of data you have. either 1) try using the evaluate formula to find out which value in the formula is producing the N/A 2) Post the formula and let me know the ranges of where your tables are a) the Header Row b) the header column c) Range of the data You aren't finding the number 289 on the Header column or the code is not finding 12.5 in the Header Row. Make sure the Range of the data in the formula like =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) A14:CX514 the size of your table including header row and header column A15:A514 is the header row in column A B14:CX14 is the header column in row 14 A B C D E F Row 14 100 98 97 96 95 Row 15 11 0.9 0.8 0.6 0.3 0.1 Row 16 12 1.8 1.7 1.5 1.3 1.0 Row 17 13 2.9 2.8 2.4 2.2 2.1 "Eric" wrote: I am using a test number of 289 and the Upper Quality limit is 12.5 and it is giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am stumped on how to make a formula for it. Here is a sample ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Can anyone please help me? Eric |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
Hello Joel,
Are you getting tired of me yet? Here is what I have. so far everything that you have given me has been very helpful. With a few tweeks here and there I have been able to get most everything working. I only have one (1) issue now. Here goes; When the number is </= the smallest number on the chart under the column 0% I am getting a -1% which obviously can't be, I need it to be 0% If you remember, whatever number I am looking for I need to find that number is the array and then subtract one (1) row but it can't do that if the number is less than or equal to the lowest number in the chart for that test. IE: Number I am looking for is -3.15 100% 99%...........1% 0% test #12 3.0 2.95 -2.45 -3.0 If the number I am looking for is (-3.15) it is less than -3.0 and the % returned is a -1%. I need returned a 0%. Below is the formula I am using.... =IF(K$406=INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C$409,'PWL Number'!$A$5:$A$504,0),),1),100,INDEX('PWL Number'!$A$4:$CX$4,MATCH(K$406,OFFSET('PWL Number'!$A$4:$CX$4,MATCH(C$409,'PWL Number'!$A$5:$A$504,0),),-1))-1) K$406~~Location of number to look up C$409~~Location of test number "Joel" wrote: This is your formula (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 If I sustitue the new number I get (((X-100)/(3.0-1.8)) * ((Y-1.8)))+100=? What do you use for X & Y? I'm thinking either 100 * ((3.0 - 1.8)/1.8) or 100 * (3.0/1.8) "Eric" wrote: First let me say thank you for the explanations. Now you asked what number I wanted to retreave or use ...... Lets use the example from 12/13/07 ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Try this example........ test #12 find number 3.0~~ I would have to use the number 1.8 in the equation because it is the largest number found. Another...... test #12 find number (negative) -3.0~~ not there so I need to use 1.0 in in the equation. Same would go for the Percentages. Does this shine light on it? Eric "Joel" wrote: Index needs an array, a Row, and, a Column like this =index('PWL Number'!B4:CX4, 1,10) Row 1 columnn 10 the above formula just uses the First Row of the table. We use Offset to get the correct row of the table so Match(c409,'PWL Number'!A5:A504,0) searches column A to find the item that matches c409 and returns the index number of the item in the table. row 4 is the header Cell A5 = 11 Cell A6 = 12 Cell A7 = 13 Looking for 11 will return 1, looking for 12 will return 2, looking for 13 will return 3 So if you were loking for 12 the offset would give you a row offset of 2 and a column offset of zero These three statement are the substituions excel will make OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0) OFFSET('PWL Number'!$B$4:$CX$4,2,0) 'PWL Number'!$B$6:$CX$6 Now you want to find the correct column so you do another match to get the index match(C411,'PWL Number'!$B$6:$CX$6) - 1 Then do an offset with the value returned from the formula above to get the value in the header row 100 down to 1 OFFSET('PWL Number'!$B$4:$CX$4,0,2) Notice the row offset of 0. The minus 1 above is to get the previous column. This is where your problem was if your number was greater the 100%. Subtracting one got you outside the table. that is why I added a test to see if the value was greater than the value in the 1st column. When it was larger than the 1st column. I think the problem is with your formula. What do you want returned when the value is greater than 100? Do you want to calculate some ratio rather than return 100? This can be easily fixed. "Eric" wrote: Joel, I'm not sure if you saw the post from 2 days ago so I decided to repost it as a reply. Sorry if this is a double post. The last post didn't work. I am trying to figure this out so can you help me on breaking down the formula =Index(offset('PWL Number'!B4:CX4,Match(c409,'PWL Number'!A5:A504,0), Does this formula find which row the test number is in and mark it as a reference row? Match(C406,Offset('PWL Number'!B4:CX4,Match(C409,'PWL Number'!A5:A504,0),),-1) Does this find the number that I need in the row from the first part of the formula? If so what does the ending mean, ),)-1)? If my thought process is correct then the second part of the formula is where I need it to read "if the number is greater than the largest number in that line then use the largest number in that line or else use the number matching or close to the number found in cell C406". I hope this makes sense.....Refere to the last posting for the rows and numbers Eric "Joel" wrote: I think the answer is to Cap the number at 100% if the number is larger than the number i the first column using an If statement. The 1st part of the if checks the value in column 1 of the appropriate row with Sheet5!C411 I also think you should change from:C409 to:Sheet5!C409 in the formula below. =IF(Sheet5!C411=INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),1),100,INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1))) "Eric" wrote: I am sorry i am making this so difficult but Here is another example: ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 If I use test #12 and look for number 1.8 all is good and 100% is returned. If I use test #13 and look for 2.2 a 97 is returned. This is a good thing. If I use test #12 and look for number 5, I get the error. Because the number 5 is greater than 1.8. What I want to happen is use the 1.8 if the number you are looking for is greater than the number under 100%. Here is the formula that I am using: =INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1)) B4:CX4 ~~ % row A5:A504~~ test numbers C409 ~~ #of Tests run (the data goes up to 500 tests but I need it to use 500 if tests are greater than 500) C411 ~~ Is the number I need to find in the range I hope this makes sense.....Again I am sorry Eric "Joel" wrote: th eformulas I gave you should work if they were adjusted properly for the range of data you have. either 1) try using the evaluate formula to find out which value in the formula is producing the N/A 2) Post the formula and let me know the ranges of where your tables are a) the Header Row b) the header column c) Range of the data You aren't finding the number 289 on the Header column or the code is not finding 12.5 in the Header Row. Make sure the Range of the data in the formula like =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) A14:CX514 the size of your table including header row and header column A15:A514 is the header row in column A B14:CX14 is the header column in row 14 A B C D E F Row 14 100 98 97 96 95 Row 15 11 0.9 0.8 0.6 0.3 0.1 Row 16 12 1.8 1.7 1.5 1.3 1.0 Row 17 13 2.9 2.8 2.4 2.2 2.1 "Eric" wrote: I am using a test number of 289 and the Upper Quality limit is 12.5 and it is giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: Joel, I will try to answer your questions: 1. The formula is placed on cell C9. 2. I will need to look up tests numbering from 1 - 500 and limits within the row that corisponse to the test number. It all depends on what test number I am running at that time. 3. The 1.33 is coming from a number I have made up. It would actually be known as the Upper Quality Limit and that is mathmatically calculated in cell (C6). I have that done already. 1.33 is cell C6 test number is cell A1 Table too look up numbers are from A14:CX514 I hope this answers all your questions. Any more let me know. THanks ERic "Joel" wrote: Where arre you placing the macro? Do you have to lookup test (11-13) and Limits? If formula is on same row as 11-12 then you just havve to lookup limits. Same applies if formual is on the same column. Also where is the 1.33 coming from? Is it just a cell reference? "Eric" wrote: Good morning all, I am trying to make a Percent with in Limits (PWL) calculation. I am |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup
I don't think you are getting negative numbers because you are less than the
smallest number. It is because some of your data is netaive and some is positive. You may want to put ABS (absolute) formula around you formula to remove the neagive sign. You may want to have negaive results. I would use the "Tools - Formula Auditing - Evalute Formula" to understand why you are getting negative numbers. "Eric" wrote: Hello Joel, Are you getting tired of me yet? Here is what I have. so far everything that you have given me has been very helpful. With a few tweeks here and there I have been able to get most everything working. I only have one (1) issue now. Here goes; When the number is </= the smallest number on the chart under the column 0% I am getting a -1% which obviously can't be, I need it to be 0% If you remember, whatever number I am looking for I need to find that number is the array and then subtract one (1) row but it can't do that if the number is less than or equal to the lowest number in the chart for that test. IE: Number I am looking for is -3.15 100% 99%...........1% 0% test #12 3.0 2.95 -2.45 -3.0 If the number I am looking for is (-3.15) it is less than -3.0 and the % returned is a -1%. I need returned a 0%. Below is the formula I am using.... =IF(K$406=INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C$409,'PWL Number'!$A$5:$A$504,0),),1),100,INDEX('PWL Number'!$A$4:$CX$4,MATCH(K$406,OFFSET('PWL Number'!$A$4:$CX$4,MATCH(C$409,'PWL Number'!$A$5:$A$504,0),),-1))-1) K$406~~Location of number to look up C$409~~Location of test number "Joel" wrote: This is your formula (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 If I sustitue the new number I get (((X-100)/(3.0-1.8)) * ((Y-1.8)))+100=? What do you use for X & Y? I'm thinking either 100 * ((3.0 - 1.8)/1.8) or 100 * (3.0/1.8) "Eric" wrote: First let me say thank you for the explanations. Now you asked what number I wanted to retreave or use ...... Lets use the example from 12/13/07 ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 Test #12 find number 1.33 Return value of: 97.15 Manual Calculation is: (((98-97)/(1.5-1.3)) *((1.33-1.3)))+97=97.15 Try this example........ test #12 find number 3.0~~ I would have to use the number 1.8 in the equation because it is the largest number found. Another...... test #12 find number (negative) -3.0~~ not there so I need to use 1.0 in in the equation. Same would go for the Percentages. Does this shine light on it? Eric "Joel" wrote: Index needs an array, a Row, and, a Column like this =index('PWL Number'!B4:CX4, 1,10) Row 1 columnn 10 the above formula just uses the First Row of the table. We use Offset to get the correct row of the table so Match(c409,'PWL Number'!A5:A504,0) searches column A to find the item that matches c409 and returns the index number of the item in the table. row 4 is the header Cell A5 = 11 Cell A6 = 12 Cell A7 = 13 Looking for 11 will return 1, looking for 12 will return 2, looking for 13 will return 3 So if you were loking for 12 the offset would give you a row offset of 2 and a column offset of zero These three statement are the substituions excel will make OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0) OFFSET('PWL Number'!$B$4:$CX$4,2,0) 'PWL Number'!$B$6:$CX$6 Now you want to find the correct column so you do another match to get the index match(C411,'PWL Number'!$B$6:$CX$6) - 1 Then do an offset with the value returned from the formula above to get the value in the header row 100 down to 1 OFFSET('PWL Number'!$B$4:$CX$4,0,2) Notice the row offset of 0. The minus 1 above is to get the previous column. This is where your problem was if your number was greater the 100%. Subtracting one got you outside the table. that is why I added a test to see if the value was greater than the value in the 1st column. When it was larger than the 1st column. I think the problem is with your formula. What do you want returned when the value is greater than 100? Do you want to calculate some ratio rather than return 100? This can be easily fixed. "Eric" wrote: Joel, I'm not sure if you saw the post from 2 days ago so I decided to repost it as a reply. Sorry if this is a double post. The last post didn't work. I am trying to figure this out so can you help me on breaking down the formula =Index(offset('PWL Number'!B4:CX4,Match(c409,'PWL Number'!A5:A504,0), Does this formula find which row the test number is in and mark it as a reference row? Match(C406,Offset('PWL Number'!B4:CX4,Match(C409,'PWL Number'!A5:A504,0),),-1) Does this find the number that I need in the row from the first part of the formula? If so what does the ending mean, ),)-1)? If my thought process is correct then the second part of the formula is where I need it to read "if the number is greater than the largest number in that line then use the largest number in that line or else use the number matching or close to the number found in cell C406". I hope this makes sense.....Refere to the last posting for the rows and numbers Eric "Joel" wrote: I think the answer is to Cap the number at 100% if the number is larger than the number i the first column using an If statement. The 1st part of the if checks the value in column 1 of the appropriate row with Sheet5!C411 I also think you should change from:C409 to:Sheet5!C409 in the formula below. =IF(Sheet5!C411=INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),1),100,INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1))) "Eric" wrote: I am sorry i am making this so difficult but Here is another example: ie: % With in Limits test 100 99 98 97 96 ....... 11 0.9 0.8 0.6 0.3 0.1 12 1.8 1.7 1.5 1.3 1.0 13 2.9 2.8 2.4 2.2 2.1 If I use test #12 and look for number 1.8 all is good and 100% is returned. If I use test #13 and look for 2.2 a 97 is returned. This is a good thing. If I use test #12 and look for number 5, I get the error. Because the number 5 is greater than 1.8. What I want to happen is use the 1.8 if the number you are looking for is greater than the number under 100%. Here is the formula that I am using: =INDEX(OFFSET('PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),MATCH(Sheet5!C411,OFFSET( 'PWL Number'!$B$4:$CX$4,MATCH(C409,'PWL Number'!$A$5:$A$504,0),),-1)) B4:CX4 ~~ % row A5:A504~~ test numbers C409 ~~ #of Tests run (the data goes up to 500 tests but I need it to use 500 if tests are greater than 500) C411 ~~ Is the number I need to find in the range I hope this makes sense.....Again I am sorry Eric "Joel" wrote: th eformulas I gave you should work if they were adjusted properly for the range of data you have. either 1) try using the evaluate formula to find out which value in the formula is producing the N/A 2) Post the formula and let me know the ranges of where your tables are a) the Header Row b) the header column c) Range of the data You aren't finding the number 289 on the Header column or the code is not finding 12.5 in the Header Row. Make sure the Range of the data in the formula like =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) A14:CX514 the size of your table including header row and header column A15:A514 is the header row in column A B14:CX14 is the header column in row 14 A B C D E F Row 14 100 98 97 96 95 Row 15 11 0.9 0.8 0.6 0.3 0.1 Row 16 12 1.8 1.7 1.5 1.3 1.0 Row 17 13 2.9 2.8 2.4 2.2 2.1 "Eric" wrote: I am using a test number of 289 and the Upper Quality limit is 12.5 and it is giving me a #N/A error. If I lower the Upper Quality Limit to <3.0 everything works well. If you look back at the other posts you will see what I am talking about. "Joel" wrote: I checked and there is no limits on the number of cells in the Match function. If yu are getting errors I recommend that you use Evaluate formula to help isolate the Problem Tools - Formula Auditing - Evaluate Formula. See if this helps isolate your problems. "Eric" wrote: Hey Joel, I know this is from almost a year ago but I have run into a snag. In the formula you use the word "Match", what happens if the number of tests is greater than 500 or the PWL is greater than 3.0? If you have any questions please let me know. Sorry about this new or latests problem ..... "Joel" wrote: I broke the calculation up into 3 cells to make it easier to understand in C9 =Index(Range,Row,Column) I added 1 to the row and column to move off the header row (100,99,98,...)and header column(11,12,13,...) =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+1) In D9 Same as C9 but I move over one column by adding 2 instead of 1 =INDEX(A14:CX514,MATCH(A1,A15:A514,0)+1,MATCH(98,B 14:CX14,0)+2) In e9 Your formula taking in the data values you posted =(((98-97)/(C9-D9)) *((1.33-D9)))+97 "Eric" wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions |