Calculation based on multiple conditions

Hello,

Attached is a spreadsheet to further illustrate my description of the problem.

Sample2.xlsx


There are 4 categories (a,b,c,d) and each category has its own set of theoretical values. I want to find the difference between what is picked and what the actual value is. However, if two values are picked, the difference is the actual minus the average of those two picked values.

Trial #1 : b and c are picked. therefore, the difference is actual minus the average of b and c ==> 12 - (12+15)/2 = -1.5
Trial #2: only b is picked therefore the difference is simply actual minus the value of b at that point ==> 13 - 13 = 0
Trial #3: c and c are picked. this is not possible in the experiment thus I wrote "Error" into the formula if this occurs.
Trial #4: c and b are picked. this is the swap of b and c but should still work ==> 15 - (15+21)/2 = -3
Trial #5: the first pick is skipped and the second pick is b. this is not possible in the experiment thus I wrote "Error" into the formula if this occurs.

Each trial # corresponds to a certain condition with a specific outcome that I desire. The formula WORKS! However, it is not very elegant and is difficult to modify if necessary (add categories e,f,g,etc). Is there a way to simplify the formula and also make it flexible?

Basically I'm trying to achieve the following:

1. If a pick 1 exists and a pick 2 exists and they are different, calculate difference equivalent to actual minus the average of those picks.
2. If pick 1 exists but pick 2 does not, calculate difference equivalent to actual minus the value of pick 1.
3. For all else, return "ERROR"


Let me know if you need any further elaboration and thanks in advance!

Hi jorjacman,

What do you mean by 'picked'?

Do you mean someone has 'selected' one Cell value & then another?

So they are manually entering a letter into the Columns 'F' & 'G'?

This should do what you want. I also think that there are a few errors in the sample given

Thank you for the feedback on your problem.