Oaktree, B3 is not referenced in the formula. I just included it because C3 is A3/B3. I guess I shouldn't have.
Pgc, you formula works great. Can you indulge me with an explanation? I see array and SIGN function, but don't understant the logic. Thank you as always.
This was the logic: The 2 last conditons of your formula are:
IF A3 < 0 and A3 - C3 < 0 then A3 - C3 else 0
IF A3 > 0 and A3 - C3 > 0 then A3 - C3 else 0
They are very similar. You can write them in a way that they are almost the same:
IF A3 < 0 and (-1)*(A3 - C3) > 0 then A3 - C3 else 0
IF A3 > 0 and (+1)*A3 - C3 > 0 then A3 - C3 else 0
The number multiplying (A3-C3) in the condition in both cases is the sign of A3. So the value of the expression is, for both A3<0 and A3 >0:
(SIGN(A3)*(A3-C3)>0) * (A3-C3)
As you know, excel converts true to 1 and false to 0 in an arithmetic expressions. So if the condition is met its value is 1 and you have the result (A3-C3) . If the condition is not met, its value is 0 and you have the result 0.
Now we look at the third condition: If A3 = 0 then result 0. Usually to include that condition you would just have to multiply by (A3<>0).
However in this case this would be redundant. In fact if A3=0 then SIGN(A3)=0 then the condition is false and you are multiplying 0*(A3-C3) which is already 0.
So the last expression encompasses all three conditions.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.