Getting error # value

[mukeshbaviskar]

Hi,
I'm getting error '# value' in the following formula. Please suggest me a correction.

Please Login or Register  to view this content.

Thanking you in anticipation.

Regards,

Mukesh

[ragulduy]

Hi - the substitute function takes three arguements I think, I'm assuming you mean:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(BE8,"P all","0"),"F6","6"),"F5","5"),"F4","4"),"F3","3"),"F2","2"),"F1","1")

[mukeshbaviskar]

Hi ragulduy,
Ok! It's working fine but after inserting it in my file I'm not getting correct output . The formula in column 'BH' is giving error 'n/a' which is based on the output of 'BG'. The substitute formula is working fine.

Please see the attachment. Refer last 2 columns 'BG, BH'

Thank you.

Regards,

Mukesh

[martindwilson]

try just
=SUBSTITUTE(SUBSTITUTE(BE8,"F",""),"P all",0)+0

[mukeshbaviskar]

Hi martindwilson,
Thank you. It's working fine but not giving correct output in rank column. I have created a output column for your reference. It is taking same number for 'F6' which should be as per their occurance in the result sheet.

Please see the attachment and give me a solution. If it is not possible then I will create a macro for it using multi sort function.

Thank you.

Regards,

Mukesh

[martindwilson]

that is a different question your original problem has been solved pleas start a new thread with the question about ranking