xlfn.NUMBERVALUE error in excel 2010

[akash14]

Hello,

Is there a replacement for NUMBERVALUE function from Excel 2013 to be used in Excel 2010.

Whenever I open it in Excel 2010 it shows as _xlfn.NUMBERVALUE and gives #NAME error.

So I need a function which does the same job and is compatible in both EXCEL 2010 and 2013.

Thanks.

 

[Andrew Poulsom]

Welcome to MrExcel.

How are you using it exactly? Give some representative examples.

 

[akash14]

I want to compare two numbers from different column. But in one column the number is stored as text. So in excel 2013 I can use if(NUMBERVALUE(B7)=J7,"yes","no") and it functions properly. Since its a shared workbook others open it in excel 2010 and the formula shows up as if(_xlfn.NUMBERVALUE(B7)=J7,"yes","no").
So is there any formula I can use instead of NUMBERVALUE which is compatible in both versions.

I know I can change the column values saved as text to be stored as number manually, but it gets updated a lot of times and thats why I am looking for a compatible formula.

Thank you

 

[Andrew Poulsom]

For that scenario you can use:

=IF(B7+0=J7,"yes","no")

which will fail if B7 doesn't contain text that looks like a number.

 

[akash14]

Ok, I have found out I can just use VALUE function.

Thank you so much for the time and effort.

 

[ryerissi]

Re:=NUMBERVALUE error in excel 2010

Hello,

Is there a replacement for NUMBERVALUE function from Excel 2013 to be used in Excel 2010.

Whenever I open it in Excel 2010 it shows as =NUMBERVALUE() and gives #NAME? error.

So I need a function which does the same job and is compatible in both EXCEL 2010 and 2013.

Thanks.

 

[deetd]

Re: =NUMBERVALUE error in excel 2010

As a workaround for office 2010 and office 2003 users - one could do the following formula:

[COLOR=#333333][FONT=Verdana]SUBSTITUTE(text, old_text, new_text, [instance_num])[COLOR=#222222][FONT=Verdana]

[/FONT][/COLOR][/FONT][/COLOR]
EG:
9.046,49 ( example Spanish format with comma & period)
Then in same row and next column write this formula (cell is A1):

[COLOR=#333333][FONT=Verdana]=SUBSTITUTE(A1,".",",",1)[COLOR=#222222][FONT=Verdana]

[/FONT][/COLOR][/FONT][/COLOR]
9.046,49 becomes 9,046,49
Next, then in same row and 2nd next column write this formula (but cell is B1):

[COLOR=#333333][FONT=Verdana]=SUBSTITUTE(B1,",",".",2)[COLOR=#222222][FONT=Verdana]

[/FONT][/COLOR][/FONT][/COLOR]
9,046,49 becomes 9,046.49
et voila: number is your locale format
_________________________
this works well if the number were larger, but remember to adjust [instance_num].
and if the situation was in reverse
I'm sure one could improve this method into a 'chained' formula

then after convert the 'text' number to a 'number' with the following formula:

VALUE(text)

ie:

=VALUE(C1)

deetd