I have a report in which I wanna show the following operation: substract the two same major numbers minus the total of the three values.
For example:
D9 = 10
E9 = 10
F9 = 5
G9 = -5 << this one is the difference.
formula on G9 is:
=IF(MATCH(D9,E9),(E9*3)-(D9+E9+F9),IF(MATCH(D9,F9),(D9*3)-(D9+E9+F9),IF(MATCH(E9,F9),(E9*3)-(D9+E9+F9))))
My formula works only when D9, E9 MATCH.
So lets talk algebra, maybe its easier for everyone.
IF A=B AND B=C THEN "NO DIFFERENCE)
IF A=B AND A≠C THEN
A*3 - (A+B+C)
AND IF A≠B AND A=C THEN
A*3 - (A+B+C)
AND IF B = C AND B ≠ A THEN
B*3 - (A+B+C)
AND IF A≠B≠C THEN "ALL ARE DIFFERENT"
Now translating this to numbers here it is:
A=10, B=10, C=10 : RESULT= ALL THE SAME
A=5, B=10, C=10 : RESULT= -5 (30-25)
A=10, B=5, C=10 : RESULT = -5
A=10, B=10, C=5 : RESULT = -5
A=10, B=5, C=0 : RESULT = ALL ARE DIFFERENT
The question remains: How can I make this work in excel?
update, this formula shows if they are different or not, but does calculate the difference.
IF(NOT(ISERROR(MATCH(D9,E9,0)&MATCH(E9,F9,0))),"TRUE THEY ARE THE SAME","FALSE THEY ARE DIFFERENT")
Okay so after doing an algorithm diagram flow I was able to solve this. Here is the formula in case someone needs it =)
=IF(NOT(ISERROR(MATCH(D9,E9,0)&MATCH(E9,F9,0))),"NO DIFFERENCE",IF(NOT(ISERROR(MATCH(F9,E9,0)))*AND(ISERROR((MATCH(D9,E9,0)))),MAX(D9:F9)-MIN(D9:F9),IF(NOT(ISERROR(MATCH(D9,E9,0)))*AND(ISERROR((MATCH(F9,E9,0)))),MAX(D9:F9)-MIN(D9:F9),IF(NOT(ISERROR(MATCH(D9,F9,0)))*AND(ISERROR((MATCH(F9,E9,0)))),MAX(D9:F9)-MIN(D9:F9),""))))