Excel formula work in US not in French

[lotus22]

I have simple formula that works in US English excel but not in French.

Formula in English:

=(1+IRR(INDEX($E77:$EF77,5):INDEX($E77:$EF77,LOOKUP(2,1/$E77:$EF77,COLUMN($E77:$EF77)-COLUMN($E77)+1))))^12-1

Formula in french

=(1+TRI(INDEX($E77:$EF77,5):INDEX($E77:$EF77,RECHERCHE(2,1/$E77:$EF77,COLONNE($E77:$EF77)-COLONNE($E77)+1))))^12-1

Same formula shows error !NOMBRE in french

PLEASE HELP!!!

 

[cgcamal]

Hi lotus22,

Maybe is needed you to use the formula in same Excel version because the names varies from older
versions to new ones.

If both are the same versions and to be sure about the translation from English to French, within French
Excel version do this:

1-) In A1 put the formula in English with a "'" at the beginning as follow:

[B][COLOR=Red]'[/COLOR][/B]=(1+IRR(INDEX($E77:$EF77,5):INDEX($E77:$EF77,LOOKUP(2,1/$E77:$EF77,COLUMN($E77:$EF77)-COLUMN($E77)+1))))^12-1

2-) Run this code,

Sub Eng_To_AnyLang()
    Range("A2") = Range("A1").Formula
End Sub

Now in B2 you'll have the formula translated into French. This should be the functions names that your
French Excel version understands.

Hope this helps.

Regards

 

[Aladin Akyurek]

The French version uses semi-colons as list separator, not commas:

=(1+TRI(INDEX($E77:$EF77;5):INDEX($E77:$EF77;RECHERCHE(2;1/$E77:$EF77;COLONNE($E77:$EF77)-COLONNE($E77)+1))))^12-1

You are probably getting a !NUM! error on the English version too.