give to the top 30% 3 point, to the midle 30% 2 points and to the lowest 40% 1 point while ignoring Zeros in the account

[myduki]

I have a table of about 435 rows (and growing a bit). I have values in the column AJ and some are "0" which affect the percentile (even as nested...with the {})
This is my best guess, but as said, the percentile is still not correct, because of the "zeros":


=IFS(AJ4>=PERCENTILE(AJ:AJ;0,7);3;AJ4>PERCENTILE(AJ:AJ;0,4);2;AJ4=0;0;AJ4<PERCENTILE(AJ:AJ;0,4);1)

Any ideas?

 

[DRSteele]

I think these two methods might help.

Copy C7 and D7 downwards.

Book1
	A	B	C	D	E	F	G	H	I
1			percentile	Points				percentile	Points
2	upper	0.7	12.582	3		lower		0	1
3	middle	0.4	11.848	2		middle	0.4	11.848	2
4	lower		0	1		upper	0.7	12.582	3
5
6		Value	Points	Points2
7		10.759	1	1
8		10.76	1	1
9		11	1	1
10		11.5	1	1
11		11.935	2	2
12		11.94	2	2
13		11.95	2	2
14		12.55	2	2
15		12.63	3	3
16		12.95	3	3
17		13	3	3
18		0	zero	zero
19		0	zero	zero
20		0	zero	zero
21		0	zero	zero
22		0	zero	zero
Sheet59

Cell Formulas
Range	Formula
C7	=IF(B7=0,"zero",IF(B7>=$C$2,$D$2,IF(AND(B7>=$C$3,B7<$C$2),$D$3,$D$4)))
C2	{=PERCENTILE.EXC(IF($B$7:$B$22<>0,$B$7:$B$22,""),B2)}
C3	{=PERCENTILE.EXC(IF($B$7:$B$22<>0,$B$7:$B$22,""),B3)}
D7	=IF(B7=0,"zero",VLOOKUP(B7,$H$2:$I$4,2))
H3	{=PERCENTILE.EXC(IF($B$7:$B$22<>0,$B$7:$B$22,""),G3)}
H4	{=PERCENTILE.EXC(IF($B$7:$B$22<>0,$B$7:$B$22,""),G4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[myduki]

I think these two methods might help.

Copy C7 and D7 downwards.

	A	B	C	D	E	F	G	H	I
1			percentile	Points				percentile	Points
2	upper	0.7	12.582	3		lower		0	1
3	middle	0.4	11.848	2		middle	0.4	11.848	2
4	lower		0	1		upper	0.7	12.582	3
5
6		Value	Points	Points2
7		10.759	1	1
8		10.76	1	1
9		11	1	1
10		11.5	1	1
11		11.935	2	2
12		11.94	2	2
13		11.95	2	2
14		12.55	2	2
15		12.63	3	3
16		12.95	3	3
17		13	3	3
18		0	zero	zero
19		0	zero	zero
20		0	zero	zero
21		0	zero	zero
22		0	zero	zero

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet59​

Worksheet Formulas Cell Formula C7 =IF(B7=0,"zero",IF(B7>=$C$2,$D$2,IF(AND(B7>=$C$3,B7<$C$2),$D$3,$D$4))) D7 =IF(B7=0,"zero",VLOOKUP(B7,$H$2:$I$4,2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C2 {=PERCENTILE.EXC(IF($B$7:$B$22<>0,$B$7:$B$22,""),B2)} C3 {=PERCENTILE.EXC(IF($B$7:$B$22<>0,$B$7:$B$22,""),B3)} H3 {=PERCENTILE.EXC(IF($B$7:$B$22<>0,$B$7:$B$22,""),G3)} H4 {=PERCENTILE.EXC(IF($B$7:$B$22<>0,$B$7:$B$22,""),G4)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

Almost, the problem with this is (and I am sorry I didn't place it before) is that I have negative values and those get 1 point (because they are the lowest 40%); the zero values should get zero (or be excluded somehow from the percentile)

 

[myduki]

well, the work around I got was creating another column to eliminate the zeros
=IF(Y2=0;"";Y2)

and since percentile dont take empty spaces into the account, I did this:

=IFS(Z2="";0;Z2>=PERCENTILE(Z:Z;0,7);3;Z2>=PERCENTILE(Z:Z;0,4);2;Z2<PERCENTILE(Z:Z;0,4);1)
(is with the shift crtl enter to get the {})

I didnt wat to create another columm, but begers can't be chooser..
Thanks anyways!