Lookup

[Eric]

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]

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]

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]

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,B14: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,B14:CX14,0)+2)

In e9
Your formula taking in the data values you posted
=(((98-97)/(C9-D9)) *((1.33-D9)))+97

 

[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]

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]

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]

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,B14: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]

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]

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]

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]

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]

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]

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]

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]

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.