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