Subtract equidistant values from predefined mid-point

[Sai]

I need to calculate difference between figures in “Column D” and “Column C” based on their location from a predefined value in A1 which appears in “Column B”.

With some effort I arrived at a formula which gives me Undesired /wrong result in a “Column F”.

The desired result which manually solved is presented in “Column G”. Basically, the difference between “Column D” and “Column C” need to be equidistant in opposite directions from the row in which “value A1” appears in “Column B”
I request anyone for a formula which can do the same automatically.

The problem file is attached.

CalcHelp_15062021.ods

[keme]

Solution

Try this in F3:

=IF(B3<$A$1;D3-OFFSET(C3;(MATCH($A$1;B3:$B$27;1)-1)*2;0);"")

Copy down.

With the exact dataset in your sample file, it returns the results you calculated manually, and performs the calculation as close to your description as I can manage.

disclaimer

Not enough context provided to ensure that the suggested solution will work in a real-world case.

• With an arbitrary “midpoint value” (A1 value not necessarily existent in column B) you may need a more elaborate construct.
• With values unevenly stepped, you may also need a more elaborate construct.
• With unsorted values in column B, the MATCH() will fail randomly.

[Lupp]

@Sai:
You may consider to also study my alternative/additional suggestions and explanations contained in the attachment.
ask314100alternativeSuggestions0.ods

[Sai]

thank you @keme. your solution works for whatever I presented in the problem file. But, one more small request for modification of the formula, which can do the same calculation for values in “Column B” >$A$1. I know this will show the already obtained values in reverse order, but I need them with reference to values in “Column B” > $A$1 in “Column F” itself for further analysis and interpretation purposes.

[Sai]

Last two days I was trying to get the desired result independently on my own and also by modifying @Keme 's solution for values in “Column B” > $A$1.

Based on @Lupp 's suggestions / ideas, I got the desired result with few helper cells & columns.

I do not know if I have to tick mark this question as solved or leave it as it is, as solution provided by @keme to the original file works perfectly, but only for “Column B” < $A$1.

Thank you all nice people for your help. God bless you.