Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula...function...how to?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula...function...how to?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula...function...how to?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula...function...how to?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula...function...how to?
Thanks for the help, I'll try both methods today. Appreciated greatly! -- asevie ------------------------------------------------------------------------ asevie's Profile: http://www.excelforum.com/member.php...o&userid=36939 View this thread: http://www.excelforum.com/showthread...hreadid=566503 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula...function...how to?
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula...function...how to?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
?IF function for preventing a cell being filled by formula | Excel Worksheet Functions | |||
Last Saved Date Formula / Function | Excel Discussion (Misc queries) | |||
numerical integration | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |