Formula...function...how to?

[asevie]

Fairly simple problem as far as logic is concerned, but I've never trie
to replicate this type of forumula in Excel. Perhaps some advice...

I'm duplicating a weight and balance slide rule used for aircraf
center of gravity calculations. In this particular aircraft, as fuel i
burned, the center of gravity changes exponentially. For example
between 0 and 5000 pounds of fuel burn has a linear change on th
index, for each 1000 pounds there is a change of approximately 0.17 o
the index scale, between 5000 and 10000, the number goes to 0.2, etc
I've got all of these plotted in a simple 2 column spreadsheet. What
would like to do is fill in the total fuel capacity, say 7800 pounds
and have the spreadsheet look to the 2 closest values and extrapolat
the number. So, for the above example 7800, the function or formul
would look to my nearest values, -2.4 for 7000 and -2.6 for 8000, an
arrive at the the correct -2.56.

Any ideas on how to accomplish this? Thank you

 

[WLMPilot]

I think I figured out the formula. First, here is my set up that I used

A B C
1 0 5000 0.17
2 5001 10000 0.2
3 5001 -2.0
4 6000 -2.2
5 7000 -2.4
8 8000 -2.6

Rows 1 & 2 just separate the factor needed based on fuel weight. I used
rows 3 - 8 to check the formula. Cell A20 (in my formula) represents total
fuel (ie 7800 in your example) and the formula was placed in cell B20: Here
it is:

=IF(A20<=5000,(((((A20/1000)-(INT(A20/1000)))*C1)*-1)+LOOKUP(A20,A3:A5,B3:B5)),(((((A20/1000)-(INT(A20/1000)))*C2)*-1)+LOOKUP(A20,A3:A5,B3:B5)))

Here is an explanation of the formula:
1) Determine which factor to use. If TRUE (A20<=5000), use C1 (0.17). IF
FALSE use C2 (0.2)

2) Since the fuel increments are in 1000's, then I need to get 0.8 (ie 80%)
out of 7800 since 800 is 80% of 1000. Thus I divide 7800 by 1000 -
INT(7800/1000) or 7.8 - 7 = 0.8.

3) Then multiply 0.8 times the factor (either C1 or C2) (.8 * .2) = 0.16

4) Change 0.16 to a negative number by multiply by -1 = -0.16

5) Add the respective value for the largest amount of fuel that is less than
the total fuel by doing a LOOKUP. In this case, LOOKUP(A20,A3:A8,B3:B8)
Therefore you have -0.16 + -2.4 = -2.56

 

[JMB]

Assuming your table is in A1:B6 and D1 has the fuel capacity you want to look
up

=IF(ISNA(MATCH(D1,A1:A6,0)),TREND(OFFSET(B1,MATCH(D1,A1:A6)-1,0,2,1),OFFSET(A1,MATCH(D1,A1:A6)-1,0,2,1),D1),VLOOKUP(D1,A1:B6,2,FALSE))

Change ranges to suit and test it out. The assumption I got from your
example is the data is linear between each point. Does it give the results
you want?

 

[JMB]

A suggestion if you don't mind- you could re-arrange the IF statement to
shorten a bit. I also rounded to 3 decimals due to binary fraction rounding
issues. I did not, however, test it.

ROUND((((A20/1000)-INT(A20/1000))*IF(A20<=5000,C1,C2)*-1),
3)+LOOKUP(A20,A3:A5,B3:B5)

 

[asevie]

Thanks for the help, I'll try both methods today. Appreciated greatly

 

[asevie]

Assuming your table is in A1:B6 and D1 has the fuel capacity you want to
look
up

=IF(ISNA(MATCH(D1,A1:A6,0)),TREND(OFFSET(B1,MATCH(D1,A1:A6)-1,0,2,1),OFFSET(A1,MATCH(D1,A1:A6)-1,0,2,1),D1),VLOOKUP(D1,A1:B6,2,FALSE))

Change ranges to suit and test it out. The assumption I got from your
example is the data is linear between each point. Does it give the
results
you want?

Both solutions work well, I think this one is a little more flexible in
that it requires fewer data points and thus a smaller file.

One question however, I'm trying to use this on Pocket Excel and all of
the functions are available except for Trend and Offset. Doable without
those?

 

[JMB]

You might take another look at the other suggestion posted. I think anything
else I can come up with will not be much different. And, I'm not familiar
w/pocket excel.

Although I would probably add something to check if the fuel capacity has an
exact match in the table. Assuming D1 is the fuel capacity to look up and
the table is A1:B11:

=IF(ISNA(MATCH(D1,A1:A11,0)),ROUND((((D1/1000)-INT(D1/1000))*IF(D1<=5000,-0.17,-0.2)), 3)+LOOKUP(D1,A1:A11,B1:B11),VLOOKUP(D1,A1:B11,2,0))