How do i get this index/match to not show people with 0 value?

[aedctalk]

Hi . I'm having an issue with this formula

I have a top 25. i'm using the following

={SMALL(IF($AP$5:$AP$142=0,0,$AP$5:$AP$142),1)}

The problem is that when i use index match.. and if one person has a value of zero.. then their name shows up in all 25 squares

I'd like any people with a value of zero to not even be included in my small.

How could i do that? :/ Thank you!!

 

[Aladin Akyurek]

Hi . I'm having an issue with this formula

I have a top 25. i'm using the following

={SMALL(IF($AP$5:$AP$142=0,0,$AP$5:$AP$142),1)}

The problem is that when i use index match.. and if one person has a value of zero.. then their name shows up in all 25 squares

I'd like any people with a value of zero to not even be included in my small.

How could i do that? :/ Thank you!!

Are you sure you have the right formula sytem for the 25 performers?

If you want to include only positive scores...

=SMAL(IF(ISNUMBER($AP$5:$AP$142),IF($AP$5:$AP$142>0,N)))

which requires control+shift+enter, not just enter. N must vary, otherwise you get the same value in all cells.

 

[aedctalk]

Thanks aladin. When i tried your code it said it was to few arguments.

I looked around more and found an old code i had that worked for finding everything under 0

=IF(SUM($AP$5:$AP$142),IF(ABS(SMALL($AP$5:$AP$142,1))<0,0,SMALL($AP$5:$AP$142,1)),0)


But that leads me to another question. When i tried to change the smalls to LARGE and then reverse the Less than sign to a greater than sign.

=IF(SUM($AS$5:$AS$142),IF(ABS(LARGE($AS$5:$AS$142,1))>0,0,LARGE($AS$5:$AS$142,1)),0)


I end up not getting a result. but clearly have someone +54

any ideas on what i'm doing wrong? Thank you!

 

[T. Valko]

Are you sure you have the right formula sytem for the 25 performers?

If you want to include only positive scores...

=SMAL(IF(ISNUMBER($AP$5:$AP$142),IF($AP$5:$AP$142>0,N)))

which requires control+shift+enter, not just enter. N must vary, otherwise you get the same value in all cells.

Are you sure about that formula? You sure do make a lot of mistakes.

Try it like this:

=SMALL(IF(ISNUMBER(AP$5:AP$142),IF(AP$5:AP$142>0,AP$5:AP$142)),N)

Testing for ISNUMBER is probably not needed. The test for >0 will account for empty cells.

=SMALL(IF(AP$5:AP$142>0,AP$5:AP$142),N)

 

[Aladin Akyurek]

Thanks aladin. When i tried your code it said it was to few arguments. [/code]

No wonder. Instead of submitting a formula as a way of describing a problem is less productive providing a sample along with the desired results. Back to that "corrective" formula... It was meant to be:

=SMALL(IF(ISNUMBER($AP$5:$AP$142),IF($AP$5:$AP$142>0,$AP$5:$AP$142)),N)

I looked around more and found an old code i had that worked for finding everything under 0

=IF(SUM($AP$5:$AP$142),IF(ABS(SMALL($AP$5:$AP$142,1))<0,0,SMALL($AP$5:$AP$142,1)),0)

I assume that 1, the position specifier, is not hard-coded.

But that leads me to another question. When i tried to change the smalls to LARGE and then reverse the Less than sign to a greater than sign.

=IF(SUM($AS$5:$AS$142),IF(ABS(LARGE($AS$5:$AS$142,1))>0,0,LARGE($AS$5:$AS$142,1)),0)

I end up not getting a result. but clearly have someone +54

any ideas on what i'm doing wrong? Thank you!

The location of the first 0 must change...

=IF(SUM($AS$5:$AS$142),IF(ABS(LARGE($AS$5:$AS$142,1))>0,LARGE($AS$5:$AS$142,1),0),0)