Thread: Formula...function...how to?

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

I'm duplicating a weight and balance slide rule used for aircraft center of gravity calculations. In this particular aircraft, as fuel is burned, the center of gravity changes exponentially. For example, between 0 and 5000 pounds of fuel burn has a linear change on the index, for each 1000 pounds there is a change of approximately 0.17 on 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 I 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 extrapolate the number. So, for the above example 7800, the function or formula would look to my nearest values, -2.4 for 7000 and -2.6 for 8000, and arrive at the the correct -2.56.

Any ideas on how to accomplish this? Thank you.

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

"asevie" wrote:

>
> Fairly simple problem as far as logic is concerned, but I've never tried
> to replicate this type of forumula in Excel. Perhaps some advice...
>
> I'm duplicating a weight and balance slide rule used for aircraft
> center of gravity calculations. In this particular aircraft, as fuel is
> burned, the center of gravity changes exponentially. For example,
> between 0 and 5000 pounds of fuel burn has a linear change on the
> index, for each 1000 pounds there is a change of approximately 0.17 on
> 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 I
> 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 extrapolate
> the number. So, for the above example 7800, the function or formula
> would look to my nearest values, -2.4 for 7000 and -2.6 for 8000, and
> arrive at the the correct -2.56.
>
> Any ideas on how to accomplish this? Thank you.
>
>
> --
> asevie
> ------------------------------------------------------------------------
> asevie's Profile: http://www.excelforum.com/member.php...o&userid=36939
> View this thread: http://www.excelforum.com/showthread...hreadid=566503
>
>

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?


"asevie" wrote:

>
> Fairly simple problem as far as logic is concerned, but I've never tried
> to replicate this type of forumula in Excel. Perhaps some advice...
>
> I'm duplicating a weight and balance slide rule used for aircraft
> center of gravity calculations. In this particular aircraft, as fuel is
> burned, the center of gravity changes exponentially. For example,
> between 0 and 5000 pounds of fuel burn has a linear change on the
> index, for each 1000 pounds there is a change of approximately 0.17 on
> 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 I
> 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 extrapolate
> the number. So, for the above example 7800, the function or formula
> would look to my nearest values, -2.4 for 7000 and -2.6 for 8000, and
> arrive at the the correct -2.56.
>
> Any ideas on how to accomplish this? Thank you.
>
>
> --
> asevie
> ------------------------------------------------------------------------
> asevie's Profile: http://www.excelforum.com/member.php...o&userid=36939
> View this thread: http://www.excelforum.com/showthread...hreadid=566503
>
>

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)



"WLMPilot" wrote:

> 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
>
> "asevie" wrote:
>
> >
> > Fairly simple problem as far as logic is concerned, but I've never tried
> > to replicate this type of forumula in Excel. Perhaps some advice...
> >
> > I'm duplicating a weight and balance slide rule used for aircraft
> > center of gravity calculations. In this particular aircraft, as fuel is
> > burned, the center of gravity changes exponentially. For example,
> > between 0 and 5000 pounds of fuel burn has a linear change on the
> > index, for each 1000 pounds there is a change of approximately 0.17 on
> > 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 I
> > 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 extrapolate
> > the number. So, for the above example 7800, the function or formula
> > would look to my nearest values, -2.4 for 7000 and -2.6 for 8000, and
> > arrive at the the correct -2.56.
> >
> > Any ideas on how to accomplish this? Thank you.
> >
> >
> > --
> > asevie
> > ------------------------------------------------------------------------
> > asevie's Profile: http://www.excelforum.com/member.php...o&userid=36939
> > View this thread: http://www.excelforum.com/showthread...hreadid=566503
> >
> >

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

Originally Posted by 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?

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?

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))

"asevie" wrote:

>
> JMB Wrote:
> > 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?
>
>
> --
> asevie
> ------------------------------------------------------------------------
> asevie's Profile: http://www.excelforum.com/member.php...o&userid=36939
> View this thread: http://www.excelforum.com/showthread...hreadid=566503
>
>