Sequential Numbering Positive/Negative Amounts

[neodjandre]

Hey all,

I have column A containing positive and negative numbers and I am looking for a formula in column B for sequential numbering as follows

col A col B
20 PS001
32 PS002
20 PS003
-20 NE001
10 PS004
20 PS005
-10 NE002
10 PS006

Could this be done without an array formula? Can't seem to figure it out although it could be easy enough for someone smarter than me?

many thanks,
Andrew

 

[theBardd]

Yes

=IF(A2>0,"POS"&TEXT(COUNTIF($B$1:$B1,"POS*")+1,"000"),"NEG"&TEXT(COUNTIF($B$1:$B1,"NEG*")+1,"000"))