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

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

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

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

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

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