PDA

View Full Version : Excel Small Function Formula (Not using VBA Code)



JOEYSCLEE
07-19-2017, 08:03 AM
Hi, there
I tried to use SMALL Function to look up the Lowest Value and F Name (before Amount) without Zero & Percentage value. Meanwhile, I would like to know the 2nd & 3rd Smallest Value and F Name as well. Unfortunately, it did not not work as the Lowest value showed Percentage value (instead of Amount).

Fyi, there is same value with the different F Name for those styles. Hence, I would like to know the 2nd & 3rd occurrence of the F Name for each style. :help

Enclosed the attachment for your reviewing. Would you please help to advise the formula?



Style
F Name 1 ID
F Name 1
F Name 1 Amount
F Name 1 Amount %
F Name 2 ID
F Name 2
F Name 2 Amount
F Name 2 Amount %
F Name 3 ID
F Name 3
F Name 3 Amount
F Name 3 Amount %
F Name 4 ID
F Name 4
F Name 4 Amount
F Name 4 Amount %
 
Lowest Value without Zero & Percentage
Header Name with Lowest Value without Zero & Percentage
2nd smallest number without Zero & Percentage
Header Name with 2nd smallest number without Zero & Percentage


D123
12345
ABC
$ -
0.00%
13456
DEF
$ 3.25
0.30%
14567
GHI
$ 3.25
0.30%
15678
JKL
$ 3.25
0.30%
 
$ 3.25
DEF
$ 3.25
GHI


D124
12345
ABC
$ 2.05
0.00%
13456
DEF
$ 1.00
0.30%
14567
GHI
$ 0.95
0.80%
15678
JKL
$ 1.50
0.25%
 
$ 0.95
GHI
$ 1.00
DEF


D125
12345
ABC
$ 1.05
1.00%
13456
DEF
$ 1.30
0.20%
14567
GHI
$ 0.70
0.35%
15678
JKL
$ 4.00
1.50%
 
$ 0.70
GHI
$ 1.05
ABC


D126
12345
ABC
Nil
0.00%
13456
DEF
Nil
0.00%
14567
GHI
Nil
0.00%
15678
JKL
Nil
0.30%
 
$ -
ABC
$ -
DEF


D127
12345
ABC
$ -
0.00%
13456
DEF
$ -
0.00%
14567
GHI
$ -
0.30%
15678
JKL
$ 2.25
0.30%
 
$ 2.25
JKL
$ -
ABC


D128
12345
ABC
$ 2.30
0.90%
13456
DEF
$ 1.30
0.20%
14567
GHI
$ 2.30
4.00%
15678
JKL
$ 4.00
5.00%
 
$ 1.30
DEF
$ 1.30
ABC