M
M&M
The Min column is the calculation of MIN(ABS(A1:C1)) by row. Now I need to
have a count by each column that the value is equal to the Min column. I
tried to use the sumproduct as below but it doesn't work:
=sumproduct(--(A1:A9<>"?"),ABS(A1:A9)=(D19))
My data is randomly to have "?"; therefore I need to make sure the formula
do not count the "?" and also treat all valuse as "ABS". The results should
be 3 in col A, 4 in col B, and 5 in col C.
Any hlep is greatly appreciated?
A B C MIN
1 -14% -14% -11% 11%
2 -12% -9% 9% 9%
3 -16% -16% -19% 16%
4 -13% -12% 12% 12%
5 -16% -17% -16% 16%
6 -16% -14% -10% 10%
7 ? ? ? ?
8 -17% -17% -18% 17%
9 -19% -17% -18% 17%
have a count by each column that the value is equal to the Min column. I
tried to use the sumproduct as below but it doesn't work:
=sumproduct(--(A1:A9<>"?"),ABS(A1:A9)=(D19))
My data is randomly to have "?"; therefore I need to make sure the formula
do not count the "?" and also treat all valuse as "ABS". The results should
be 3 in col A, 4 in col B, and 5 in col C.
Any hlep is greatly appreciated?
A B C MIN
1 -14% -14% -11% 11%
2 -12% -9% 9% 9%
3 -16% -16% -19% 16%
4 -13% -12% 12% 12%
5 -16% -17% -16% 16%
6 -16% -14% -10% 10%
7 ? ? ? ?
8 -17% -17% -18% 17%
9 -19% -17% -18% 17%