View original (French)

ConvNumberLetter() error #VALUE!

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

  • Votes
  • Date
  • Descending date
  1. gbinforme Contributor
    Hello

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

    Always zen
    1. Matotea Author
      Alright,

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

      Thank you very much...

      Matotea.
    2. larlen
      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
    3. tontong
      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.
  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.