View original (French)

ConvNumberLetter() error #VALUE!

Matotea Posted messages 2 Status Member -  
 Matotea -
Hello,

I'm using EXCEL 2007, the function ConvNumberLetter() returns "#VALUE!" on a reference cell calculated with another cell formatted as %, when the value of the latter is 35, or 69, or 70...

For example:

A1 in percentage format, 2 decimal places...
B1 contains a number, it doesn't matter if it is in Standard, Number, or Financial format....
C1 =A1*B1

A3 =ConvNumberLetter(C1)

The function works for all values except when A1 reaches 35, or 69, or 70...

If I type in A1: 35.00001... It works... The problem is that with large numbers in B1 the result is incorrect... And it's not practical...

I have repeated the experiment several times, in a blank sheet and in a blank workbook, the issue persists...

If A1 is not in % format, it works perfectly regardless of the value... In fact, that's how I circumvented the problem...

But, to shed some light on the matter, if anyone has an idea of what's going on and possibly a solution...

Thank you very much!

Matotea.
Configuration: Windows XP Firefox 3.5.5

2 answers

  1.
    gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello

    ConvNumberLetter is a custom function, so you should ask the author.
    --

    Always zen
    0
    1. Matotea Posted messages 2 Status Member
       
      Alright,

      I asked the author the question... I'm waiting for their response...

      Thank you very much...

      Matotea.
      0
    2. larlen Posted messages 1 Status Member
       
      Hello,

      I have quite a few errors with this function as well

      e.g.: A1 = 22000
      B1 = 40% or (0.4)
      C1 = A1*(1+B1)
      D1= convnumberletter(C1) = #VALUE!

      the same with convnumberletter(22000*1.4)

      (I have other examples of values that don't work)

      Do you have any information from your contact with the author of the formula?

      Thanks in advance

      larlen
      0
    3. tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
       
      Hello,
      The same error occurs with Excel 2003. The strangest thing is that the macro rounds to two decimal places, but the formula works with C1 =INT(A1*(1+B1)*1000)/1000.
      0
  2. Matotea
     
    Ia orana,

    Sorry, the issue remains. Here is the author's response:

    << Hello,

    I’m sorry but I will leave your lantern in the dark.

    Indeed, ConvNumberLetter works perfectly with previous versions, including for the cases you mentioned.

    The error returned “#VALUE!” implies that a number value has been associated with a text value in the calculation.
    But then the error should appear in all cases since it is related to the format of cells A1 and B1.

    So I fear that we are faced with a new Microsoft riddle...

    Best regards.     >>

    As of today, I don't know more.

    Regards.

    Sincerely.
    0