ConvNumberLetter() error #VALUE!
Matotea
Posted messages
2
Status
Membre
-
Matotea -
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.
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 réponses
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.
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.
I asked the author the question... I'm waiting for their response...
Thank you very much...
Matotea.
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
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.