G
Guest
I am trying to find a formula to automatically return the x/y intersection on
a chart to a cell.
a chart to a cell.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
To provide some more background to what I'm trying to do. I need to
intersect a WT then match the CG to produce the KIAS where the WT and CG are
variables defined in a separate cell. I also need the formula to round...for
the sake of simplicity.
Ie: WT=2359, CG=39.8, KIAS= ?
WT CG KIAS
2300 40.9 44
2300 39 46
2300 37 47
2300 35 48
2300 33 49
2350 40.9 44
2350 39 45
2350 37 47
2350 35 48
2350 33 49
2400 43.5 44
2400 40.9 45
2400 39 46
2400 37 47
2400 35 48
2400 33 49
2450 43.5 44
2450 40.9 45
2450 39 47
2450 37 48
2450 35 49
2450 33 49
2500 43.5 45
2500 40.9 46
2500 39 47
2500 37 48
2500 35 49
2500 33 49
2550 43.5 45
2550 40.9 47
2550 39 48
2550 37 49
2550 35 49
2550 33 50
2600 43.5 46
2600 40.9 48
2600 39 48
2600 37 49
2600 35 49
2600 33 50
2650 43.5 47
2650 40.9 48
2650 39 49
2650 37 49
2650 35 49
2650 33 50
2700 43.5 48
2700 40.9 49
2700 39 49
2700 37 49
2700 35 50
2700 33 50
2750 43.5 48
2750 40.9 49
2750 39 49
2750 37 49
2750 35 50
2750 33 50
2800 43.5 49
2800 40.9 49
2800 39 49
2800 37 50
2800 35 50
2800 33 50
2850 43.5 49
2850 40.9 50
2850 39 50
2850 37 50
2850 35 50
2850 33 50
2900 43.5 49
2900 40.9 50
2900 39 50
2900 37 50
2900 35 50
2950 33 50
3000 46 49
3000 43.5 50
3000 40.9 51
3000 39 51
3000 37 51
3000 35 51
3000 33 51
3100 46 50
3100 43.5 50
3100 40.9 51
3100 39 51
3100 37 51
3100 35 51
3100 33 51
intersect a WT then match the CG to produce the KIAS where the WT and CG are
variables defined in a separate cell. I also need the formula to round...for
the sake of simplicity.
Ie: WT=2359, CG=39.8, KIAS= ?
WT CG KIAS
2300 40.9 44
2300 39 46
2300 37 47
2300 35 48
2300 33 49
2350 40.9 44
2350 39 45
2350 37 47
2350 35 48
2350 33 49
2400 43.5 44
2400 40.9 45
2400 39 46
2400 37 47
2400 35 48
2400 33 49
2450 43.5 44
2450 40.9 45
2450 39 47
2450 37 48
2450 35 49
2450 33 49
2500 43.5 45
2500 40.9 46
2500 39 47
2500 37 48
2500 35 49
2500 33 49
2550 43.5 45
2550 40.9 47
2550 39 48
2550 37 49
2550 35 49
2550 33 50
2600 43.5 46
2600 40.9 48
2600 39 48
2600 37 49
2600 35 49
2600 33 50
2650 43.5 47
2650 40.9 48
2650 39 49
2650 37 49
2650 35 49
2650 33 50
2700 43.5 48
2700 40.9 49
2700 39 49
2700 37 49
2700 35 50
2700 33 50
2750 43.5 48
2750 40.9 49
2750 39 49
2750 37 49
2750 35 50
2750 33 50
2800 43.5 49
2800 40.9 49
2800 39 49
2800 37 50
2800 35 50
2800 33 50
2850 43.5 49
2850 40.9 50
2850 39 50
2850 37 50
2850 35 50
2850 33 50
2900 43.5 49
2900 40.9 50
2900 39 50
2900 37 50
2900 35 50
2950 33 50
3000 46 49
3000 43.5 50
3000 40.9 51
3000 39 51
3000 37 51
3000 35 51
3000 33 51
3100 46 50
3100 43.5 50
3100 40.9 51
3100 39 51
3100 37 51
3100 35 51
3100 33 51
G
Guest
I don't think you want to get it from the chart.
Check out regression in help"
Check out regression in help"
D
David Biddulph
That doesn't sound like a chart in the Excel sense of the word. It sounds
more like a lookup table problem. You say you need the formula to round;
do you mean that on each of the lookups you want to round down, or round up,
or round to the nearest? ... or do you want to interpolate and round the
results?
You'll find most of the relevant functions at
http://office.microsoft.com/en-us/excel/HP052042111033.aspx#Lookup and Reference functions
more like a lookup table problem. You say you need the formula to round;
do you mean that on each of the lookups you want to round down, or round up,
or round to the nearest? ... or do you want to interpolate and round the
results?
You'll find most of the relevant functions at
http://office.microsoft.com/en-us/excel/HP052042111033.aspx#Lookup and Reference functions
G
Guest
You're correct. I was originally trying to use the chart data to extract the
answer. With Regression...considering I'm an excel newbie here, are you
referring to multiple regression? I've used VLOOKUP when I only had one
variable...with two I'm stumped...
answer. With Regression...considering I'm an excel newbie here, are you
referring to multiple regression? I've used VLOOKUP when I only had one
variable...with two I'm stumped...
G
Guest
David, Rounding to the nearest would work. What I need the formula to do is
look up a cell value A12 (for example), reference that value in the WT
column, then match a cell value in A13 (example), reference that value in CG
column, then find the match KIAS value.
Forgive my ignorance as I'm learning Excel on the fly here...thanks for the
help!
2479.0 (A12) 40.2 (A13)
STALL SPEEDS
WT CG KIAS
2300 40.9 44
2300 39 46
2300 37 47
2300 35 48
2300 33 49
2350 40.9 44
2350 39 45
2350 37 47
2350 35 48
2350 33 49
2400 43.5 44
2400 40.9 45
2400 39 46
2400 37 47
2400 35 48
2400 33 49
2450 43.5 44
2450 40.9 45
2450 39 47
2450 37 48
2450 35 49
2450 33 49
2500 43.5 45
2500 40.9 46
2500 39 47
2500 37 48
2500 35 49
2500 33 49
2550 43.5 45
2550 40.9 47
2550 39 48
2550 37 49
2550 35 49
2550 33 50
2600 43.5 46
2600 40.9 48
2600 39 48
2600 37 49
2600 35 49
2600 33 50
2650 43.5 47
2650 40.9 48
2650 39 49
2650 37 49
2650 35 49
2650 33 50
2700 43.5 48
2700 40.9 49
2700 39 49
2700 37 49
2700 35 50
2700 33 50
2750 43.5 48
2750 40.9 49
look up a cell value A12 (for example), reference that value in the WT
column, then match a cell value in A13 (example), reference that value in CG
column, then find the match KIAS value.
Forgive my ignorance as I'm learning Excel on the fly here...thanks for the
help!
2479.0 (A12) 40.2 (A13)
STALL SPEEDS
WT CG KIAS
2300 40.9 44
2300 39 46
2300 37 47
2300 35 48
2300 33 49
2350 40.9 44
2350 39 45
2350 37 47
2350 35 48
2350 33 49
2400 43.5 44
2400 40.9 45
2400 39 46
2400 37 47
2400 35 48
2400 33 49
2450 43.5 44
2450 40.9 45
2450 39 47
2450 37 48
2450 35 49
2450 33 49
2500 43.5 45
2500 40.9 46
2500 39 47
2500 37 48
2500 35 49
2500 33 49
2550 43.5 45
2550 40.9 47
2550 39 48
2550 37 49
2550 35 49
2550 33 50
2600 43.5 46
2600 40.9 48
2600 39 48
2600 37 49
2600 35 49
2600 33 50
2650 43.5 47
2650 40.9 48
2650 39 49
2650 37 49
2650 35 49
2650 33 50
2700 43.5 48
2700 40.9 49
2700 39 49
2700 37 49
2700 35 50
2700 33 50
2750 43.5 48
2750 40.9 49
G
Guest
If you are willing to reorganize your data into a 2-way table (WT down a
column and CG across a row, or the other way around with the corresponding
KIAS values filling the grid), you can use the method documented at
Interpolation
http://www.tushar-mehta.com/excel/newsgroups/interpolation/index.html
To quickly reorganize your data, create a PivotTable with WT as the row
field, CG as the column field and KIAS as the data field.
I did the above with the PT in J3:R21 and used the Interpolate2DArray
function with your example data point. The result was KIAS=44.75894737
The formula was =Interpolate2DArray(K5:Q20,J5:J20,K4:Q4,F25,H25) where F25
and H25 contained the specified WT and CG values.
Do note that because you have some holes (prominently, only one value for
WT=2950 and only 2 values for CG=46, your results will be very poor around
those data points.
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu
column and CG across a row, or the other way around with the corresponding
KIAS values filling the grid), you can use the method documented at
Interpolation
http://www.tushar-mehta.com/excel/newsgroups/interpolation/index.html
To quickly reorganize your data, create a PivotTable with WT as the row
field, CG as the column field and KIAS as the data field.
I did the above with the PT in J3:R21 and used the Interpolate2DArray
function with your example data point. The result was KIAS=44.75894737
The formula was =Interpolate2DArray(K5:Q20,J5:J20,K4:Q4,F25,H25) where F25
and H25 contained the specified WT and CG values.
Do note that because you have some holes (prominently, only one value for
WT=2950 and only 2 values for CG=46, your results will be very poor around
those data points.
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu
G
Guest
Well, you definetly have figured it out...I on the other hand am trying to
catch up with you...
I've rearranged the data the way you suggested and I still get the #NA with
the formula. Not sure what I'm missing...Does the DATA field need to be in
Ascending order? thanks again!
43.5 40.9 39 37 35 33
2300 0 44 46 47 48 49
2350 0 44 45 47 48 49
2400 44 45 46 47 48 49
2450 44 45 47 48 49 49
2500 43.5 46 47 48 49 49
2550 45 47 48 49 49 50
2600 46 48 48 49 49 50
2650 47 48 49 49 49 50
2700 48 49 49 49 50 50
2750 48 49 49 49 50 50
2800 49 49 49 50 50 50
2850 49 50 50 50 50 50
2900 49 50 50 50 50 50
2950 50 50 50 51 51 51
3000 50 51 51 51 51 51
3050 50 51 51 51 51 51
3100 50 51 51 51 51 51
catch up with you...
I've rearranged the data the way you suggested and I still get the #NA with
the formula. Not sure what I'm missing...Does the DATA field need to be in
Ascending order? thanks again!
43.5 40.9 39 37 35 33
2300 0 44 46 47 48 49
2350 0 44 45 47 48 49
2400 44 45 46 47 48 49
2450 44 45 47 48 49 49
2500 43.5 46 47 48 49 49
2550 45 47 48 49 49 50
2600 46 48 48 49 49 50
2650 47 48 49 49 49 50
2700 48 49 49 49 50 50
2750 48 49 49 49 50 50
2800 49 49 49 50 50 50
2850 49 50 50 50 50 50
2900 49 50 50 50 50 50
2950 50 50 50 51 51 51
3000 50 51 51 51 51 51
3050 50 51 51 51 51 51
3100 50 51 51 51 51 51
G
Guest
You did add the VBA functions on the page I referenced, didn't you?
Also, you would have to adapt my example use of the UDF to suit the ranges
in your worksheet.
Beyond that I don't know what to tell you since I tested and verified that
my suggestion worked before I posted it.
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu
Also, you would have to adapt my example use of the UDF to suit the ranges
in your worksheet.
Beyond that I don't know what to tell you since I tested and verified that
my suggestion worked before I posted it.
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu