Using SMALL function/formula in excel will not sort value output from a formula

[Drezzilla]

My issue is that I have the function/formula below

=small($J$3:$J:9,1)

Which I have placed in column K this should iterate the Kth value 1,2,3,4 as the formula is dragged down till it gets to the K9.

the values in $J$3:$J$9 are calculated using the function/formula below.

=IF(AND(G3<>"",G3<>"End"),TRIM(RIGHT(G3,LEN(G3)-(1+FIND("SI",G3)))),IF(G3="End","",))

or

=IF(AND(G4<>$G$3:G3),I4,"")

In each case the small function will return the NUM! error. I believe that this is because there is a formula and not just a value outputting the cell value that needs to be returned. How can I use the formula to return the value and not have the NUM! error. It should end up looking like this assuming the second column has the small formula in it

Column with formula out put Column with small formula/function
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]138[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]138[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]222[/TD]
[/TR]
</tbody>[/TABLE]

 

[Redwolfx]

I haven't tested this, so I'm shooting in the dark, but I think the problem you are having is the trimming andd manipulating of the information. I think Excel is then reading it as a string. You could try surrounding it in Value()

 

[Drezzilla]

This worked Thank you very much good sir!

 

[Drezzilla]

This worked Thank you very much good sir!

and Just to be clear you must put the VALUE() around the returned item only so the small function/formula can recognize it, or else it might not work for example

=IF(AND(G4<>$G$3:G3),I4,"")

becomes

=IF(AND(G4<>$G$3:G3,G4<>"END",G4<>""),VALUE(I4),"")

again, thank you!