Hi all,
I am having some issues with the following problem.
I have a range of coordinates x [m] and 5 items defined by the functions x1,y1, x2,y2, x3,y3 and x4,y4.
My objective is to find the y [mm2] value corresponding to my x-value by finding the value inside of my x1-x4,y1-y4 list. I need to do this for each one of the 10 items and then sum all the values into one column:
Anch_Summary_1:
The way I am currently doing this is by this linear interpolation :
Where C$14:C$17 is x1 through x4 and C$18:C$21 is y1 through y4 for Item 1 and then I drag this equation to get D, E , F, G for items 2 through 4.
=IFERROR(INDEX(C$18:C$21;MATCH(Anch_Summary_1[@[x '[m']]:[x '[m']]];C$14:C$17;0));IFERROR(INDEX(C$18:C$21;MATCH(SMALL(C$14:C$17;COUNTIF(C$14:C$17;"<="&Anch_Summary_1[@[x '[m']]:[x '[m']]]));C$14:C$17;0))-(SMALL(C$14:C$17;COUNTIF(C$14:C$17;"<="&Anch_Summary_1[@[x '[m']]:[x '[m']]]))-Anch_Summary_1[@[x '[m']]:[x '[m']]])*(INDEX(C$18:C$21;MATCH(SMALL(C$14:C$17;COUNTIF(C$14:C$17;"<="&Anch_Summary_1[@[x '[m']]:[x '[m']]]));C$14:C$17;0))-INDEX(C$18:C$21;MATCH(LARGE(C$14:C$17;COUNTIF(C$14:C$17;">="&Anch_Summary_1[@[x '[m']]:[x '[m']]]));C$14:C$17;0)))/(SMALL(C$14:C$17;COUNTIF(C$14:C$17;"<="&Anch_Summary_1[@[x '[m']]:[x '[m']]]))-LARGE(C$14:C$17;COUNTIF(C$14:C$17;">="&Anch_Summary_1[@[x '[m']]:[x '[m']]])));0))
At the end of my operation I do SUM(1,2,3,4,5) and it is working as it intended. My issue is that Whenever I add an additional item 6 and so on, my sum range is not dynamic and the table does not automatically expand to have more x coordinates. I would have like to do all these operations through array/spill functions in which all the intermediate steps are potentially condensed into one formula and I can have only x [m] and Total [mm2] as dynamic spill arrays.
My main issue is that whenever I try to convert the previous equation into an array logic, most of my functions like SMALL, LARGE, COUNTIF and so on do not work on spill ranges and the whole logic must be changed.
If you have any ideas on how to tackle this issue I am open to suggestions, I am familiar with VBA and PowerQuery, but would prefer to avoid them.
Thank you very much for you help
I am having some issues with the following problem.
I have a range of coordinates x [m] and 5 items defined by the functions x1,y1, x2,y2, x3,y3 and x4,y4.
My objective is to find the y [mm2] value corresponding to my x-value by finding the value inside of my x1-x4,y1-y4 list. I need to do this for each one of the 10 items and then sum all the values into one column:
| Bar | 1 | 2 | 3 | 4 | 5 |
| x1 [m] | -11.15 | -10.06 | -8.62 | -8.22 | -8.22 |
| x2 [m] | -11.15 | -9.13 | -8.62 | -7.53 | -7.53 |
| x3 [m] | -8.62 | -8.62 | -0.50 | -5.21 | -5.91 |
| x4 [m] | -8.62 | -8.62 | -0.50 | -4.52 | -5.22 |
| y1 [mm2] | 0 | 0 | 2454 | 0 | 0 |
| y2 [mm2] | 1571 | 2454 | 2454 | 785 | 785 |
| y3 [mm2] | 1571 | 2454 | 2454 | 785 | 785 |
| y4 [mm2] | 0 | 0 | 2454 | 0 | 0 |
Anch_Summary_1:
The way I am currently doing this is by this linear interpolation :
Where C$14:C$17 is x1 through x4 and C$18:C$21 is y1 through y4 for Item 1 and then I drag this equation to get D, E , F, G for items 2 through 4.
=IFERROR(INDEX(C$18:C$21;MATCH(Anch_Summary_1[@[x '[m']]:[x '[m']]];C$14:C$17;0));IFERROR(INDEX(C$18:C$21;MATCH(SMALL(C$14:C$17;COUNTIF(C$14:C$17;"<="&Anch_Summary_1[@[x '[m']]:[x '[m']]]));C$14:C$17;0))-(SMALL(C$14:C$17;COUNTIF(C$14:C$17;"<="&Anch_Summary_1[@[x '[m']]:[x '[m']]]))-Anch_Summary_1[@[x '[m']]:[x '[m']]])*(INDEX(C$18:C$21;MATCH(SMALL(C$14:C$17;COUNTIF(C$14:C$17;"<="&Anch_Summary_1[@[x '[m']]:[x '[m']]]));C$14:C$17;0))-INDEX(C$18:C$21;MATCH(LARGE(C$14:C$17;COUNTIF(C$14:C$17;">="&Anch_Summary_1[@[x '[m']]:[x '[m']]]));C$14:C$17;0)))/(SMALL(C$14:C$17;COUNTIF(C$14:C$17;"<="&Anch_Summary_1[@[x '[m']]:[x '[m']]]))-LARGE(C$14:C$17;COUNTIF(C$14:C$17;">="&Anch_Summary_1[@[x '[m']]:[x '[m']]])));0))
At the end of my operation I do SUM(1,2,3,4,5) and it is working as it intended. My issue is that Whenever I add an additional item 6 and so on, my sum range is not dynamic and the table does not automatically expand to have more x coordinates. I would have like to do all these operations through array/spill functions in which all the intermediate steps are potentially condensed into one formula and I can have only x [m] and Total [mm2] as dynamic spill arrays.
My main issue is that whenever I try to convert the previous equation into an array logic, most of my functions like SMALL, LARGE, COUNTIF and so on do not work on spill ranges and the whole logic must be changed.
If you have any ideas on how to tackle this issue I am open to suggestions, I am familiar with VBA and PowerQuery, but would prefer to avoid them.
Thank you very much for you help
