non linear scale applied to data

[leecavturbo]

in this example below i need to work out what the divsor
would be for values inbetween the data?
.

 

[mikerickson]

I came up with this that uses named formulas.

name: lowRow RefersTo: =MATCH(Sheet1!D1,Sheet1!$A:$A)
name: lowVal RefersTo: =INDEX(Sheet1!$A:$A,lowRow,1)
name: highVal RefersTo: =INDEX(Sheet1!$A:$A,lowRow+1,1)
name: lowResult RefersTo: =INDEX(Sheet1!$B:$B,lowRow,1)
name: highResult RefersTo: =INDEX(Sheet1!$B:$B,lowRow+1,1)

The spreadsheet formula

=lowResult+((D1-lowVal)/(highVal-lowVal)*(highResult-lowResult))

should return the afr for the number of Mv entered in D1.

Because of the relative referencing in lowRow, the cell containing the formula should be selected when defining the names.

 

[leecavturbo]

I came up with this that uses named formulas.

name: lowRow RefersTo: =MATCH(Sheet1!D1,Sheet1!$A:$A)
name: lowVal RefersTo: =INDEX(Sheet1!$A:$A,lowRow,1)
name: highVal RefersTo: =INDEX(Sheet1!$A:$A,lowRow+1,1)
name: lowResult RefersTo: =INDEX(Sheet1!$B:$B,lowRow,1)
name: highResult RefersTo: =INDEX(Sheet1!$B:$B,lowRow+1,1)

The spreadsheet formula

=lowResult+((D1-lowVal)/(highVal-lowVal)*(highResult-lowResult))

should return the afr for the number of Mv entered in D1.

Because of the relative referencing in lowRow, the cell containing the formula should be selected when defining the names.

really appreciate the help mike.

 

[mikerickson]

You're welcome.

 

[leecavturbo]

mike can you try your theory?
here.
tell me if the link doesn't work.
thx
http://www.keepandshare.com/doc/view.php?u=445664

 

[mikerickson]

The value Mv=1 is lower than the lowest value in the chart. No AFR for MV=1 can be calculated from the given data.

For the others, I selected cell B31 and defined these names

Name: mvRow RefersTo: =MATCH(Sheet1!$A31,Sheet1!$A$3:$A$22)
Name: lowMV RefersTo: =INDEX(Sheet1!$A$3:$A$22,mvRow,1)
Name: highMV RefersTo: =INDEX(Sheet1!$A$3:$A$22,mvRow+1,1)
Name: lowAFR RefersTo: =INDEX(Sheet1!$B$3:$B$22,mvRow,1)
Name: highAFR RefersTo: =INDEX(Sheet1!$B$3:$B$22,mvRow+1,1)

and then entered this formula in B31

=lowAFR+((A31-lowMV)/(highMV-lowMV)*(highAFR-lowAFR))

Then fill down.

 

[leecavturbo]

The value Mv=1 is lower than the lowest value in the chart. No AFR for MV=1 can be calculated from the given data.

For the others, I selected cell B31 and defined these names

Name: mvRow RefersTo: =MATCH(Sheet1!$A31,Sheet1!$A$3:$A$22)
Name: lowMV RefersTo: =INDEX(Sheet1!$A$3:$A$22,mvRow,1)
Name: highMV RefersTo: =INDEX(Sheet1!$A$3:$A$22,mvRow+1,1)
Name: lowAFR RefersTo: =INDEX(Sheet1!$B$3:$B$22,mvRow,1)
Name: highAFR RefersTo: =INDEX(Sheet1!$B$3:$B$22,mvRow+1,1)

and then entered this formula in B31

=lowAFR+((A31-lowMV)/(highMV-lowMV)*(highAFR-lowAFR))

Then fill down.

the rogue figures like 1 or 100 etc can be ignored.

 

[leecavturbo]

when i enter that formula i just get NAME? in the cell?
do i need to define something?

 

[mikerickson]

You need to define the names.

If you PM me an e-mail address, I'll send you what I did.

(Linear interpolation will not make the graph any smoother, since the values derived fall on the line segments between the existing points.)

 

[leecavturbo]

(Linear interpolation will not make the graph any smoother, since the values derived fall on the line segments between the existing points.)

those values are fixed so yeah i guessed as much.
great work mike.

 

[leecavturbo]

how do i apply this formula and definitions to other work sheets?
obviously i know how to copy and paste the formula,
but the formula does not work on its own in another wookbook!
thx