# Subtract equidistant values from predefined mid-point

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

## 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.

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