Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
The output of the Fourier Transform fills most cells as numbers (all those
that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: Can the FT routine be set to enter results directly as numbers? If not, how do I convert numbers-as-text to numbers in a Macro? - Neal |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
On May 5, 5:53*pm, Neal Carron
wrote: The output of the Fourier Transform fills most cells as numbers (all those that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: * Can the FT routine be set to enter results directly as numbers? * If not, how do I convert numbers-as-text to numbers in a Macro? *- Neal Neal, Have you tried =VALUE(A1), =A1+1-1, or =A1*1? Best, Matthew Herbert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
Hi. The output is a complex number represented in Excel as a string.
=Complex(5,0) will show up as 5, but as a string, much like you are seeing. =ImReal(B1) will return the Real part. Due to 'rounding issues', you may prefer to use IMABS(B1) is you believe the Complex part is suppose to be zero. The advantage also is that it will convert those cells that do have very small imaginary parts. HTH Dana DeLouis Neal Carron wrote: The output of the Fourier Transform fills most cells as numbers (all those that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: Can the FT routine be set to enter results directly as numbers? If not, how do I convert numbers-as-text to numbers in a Macro? - Neal |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
Matthew,
I don't see how to carry out your operations on a column that has already been filled with numbers-as-text. You mean insert a temporary column A for the FT, and carry out one of your suggestions to define the numeric value in the final column B? Like B1=A1*1. " wrote: On May 5, 5:53 pm, Neal Carron wrote: The output of the Fourier Transform fills most cells as numbers (all those that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: Can the FT routine be set to enter results directly as numbers? If not, how do I convert numbers-as-text to numbers in a Macro? - Neal Neal, Have you tried =VALUE(A1), =A1+1-1, or =A1*1? Best, Matthew Herbert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
Dana,
All true, but I don't see how to apply your method to a column. It seems I'd have to create a temporary column B, evaluate the FT in it, and then set another column =ImReal(B1) as the final answer. Is that what you meant? - Neal "Dana DeLouis" wrote: Hi. The output is a complex number represented in Excel as a string. =Complex(5,0) will show up as 5, but as a string, much like you are seeing. =ImReal(B1) will return the Real part. Due to 'rounding issues', you may prefer to use IMABS(B1) is you believe the Complex part is suppose to be zero. The advantage also is that it will convert those cells that do have very small imaginary parts. HTH Dana DeLouis Neal Carron wrote: The output of the Fourier Transform fills most cells as numbers (all those that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: Can the FT routine be set to enter results directly as numbers? If not, how do I convert numbers-as-text to numbers in a Macro? - Neal |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
Hi. This is a shorter / modified version of something I use.
Maybe you can get some ideas here. Take the inverse of the data, and output it beginning at E1. Note that you are assuming the data will be integers. This may not be the case. You may be able to adjust it to your situation. (with integer output, are you doing convolution, or high-precision multiplication ?? ) Sub YourRoutine() Call FFTInverse([C1:C16], [E1]) End Sub Sub FFTInverse(InData, OutCell) Dim N Dim Cell N = InData.Cells.Count OutCell.Resize(N).Clear Run "ATPVBAEN.XLAM!Fourier", InData, OutCell, True, False For Each Cell In OutCell.Resize(N).Cells Cell.Value = CDbl(WorksheetFunction.ImAbs(Cell)) Next Cell End Sub = = = = = HTH Dana DeLouis Neal Carron wrote: Dana, All true, but I don't see how to apply your method to a column. It seems I'd have to create a temporary column B, evaluate the FT in it, and then set another column =ImReal(B1) as the final answer. Is that what you meant? - Neal "Dana DeLouis" wrote: Hi. The output is a complex number represented in Excel as a string. =Complex(5,0) will show up as 5, but as a string, much like you are seeing. =ImReal(B1) will return the Real part. Due to 'rounding issues', you may prefer to use IMABS(B1) is you believe the Complex part is suppose to be zero. The advantage also is that it will convert those cells that do have very small imaginary parts. HTH Dana DeLouis Neal Carron wrote: The output of the Fourier Transform fills most cells as numbers (all those that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: Can the FT routine be set to enter results directly as numbers? If not, how do I convert numbers-as-text to numbers in a Macro? - Neal |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
Dana,
OK, I've adapted most of your suggestions to my situation. My original function is real. None of the values is integer. Its FFT is complex. The inverse FFT is real, and it comes out of the Fourier routine as numbers-as-text. I've got your routine working for it [replacing your .ImAbs(Cell) by ..ImReal(Cell)] The complex FFT remains a problem. Those values that are complex appear in their cells as numbers. But those that have zero imaginary part are text. I can't identify ahead of time which ones will be text. I need a function applying to all cells, that will convert text to real numbers, and leave complex numbers alone. How do I do that? - Neal "Dana DeLouis" wrote: Hi. This is a shorter / modified version of something I use. Maybe you can get some ideas here. Take the inverse of the data, and output it beginning at E1. Note that you are assuming the data will be integers. This may not be the case. You may be able to adjust it to your situation. (with integer output, are you doing convolution, or high-precision multiplication ?? ) Sub YourRoutine() Call FFTInverse([C1:C16], [E1]) End Sub Sub FFTInverse(InData, OutCell) Dim N Dim Cell N = InData.Cells.Count OutCell.Resize(N).Clear Run "ATPVBAEN.XLAM!Fourier", InData, OutCell, True, False For Each Cell In OutCell.Resize(N).Cells Cell.Value = CDbl(WorksheetFunction.ImAbs(Cell)) Next Cell End Sub = = = = = HTH Dana DeLouis Neal Carron wrote: Dana, All true, but I don't see how to apply your method to a column. It seems I'd have to create a temporary column B, evaluate the FT in it, and then set another column =ImReal(B1) as the final answer. Is that what you meant? - Neal "Dana DeLouis" wrote: Hi. The output is a complex number represented in Excel as a string. =Complex(5,0) will show up as 5, but as a string, much like you are seeing. =ImReal(B1) will return the Real part. Due to 'rounding issues', you may prefer to use IMABS(B1) is you believe the Complex part is suppose to be zero. The advantage also is that it will convert those cells that do have very small imaginary parts. HTH Dana DeLouis Neal Carron wrote: The output of the Fourier Transform fills most cells as numbers (all those that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: Can the FT routine be set to enter results directly as numbers? If not, how do I convert numbers-as-text to numbers in a Macro? - Neal |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
Hi. I don't think I understand the question.
I think you want if a cell has a 0 imaginary part, convert the real part to a number, and not string. If the cell does have an imaginary part, then leave it alone. Sub YourRoutine() Call FFTInverse([C1:C4], [E1]) End Sub Sub FFTInverse(InData, OutCell) Dim N Dim Cell Const k As Double = 0.0000000001 N = InData.Cells.Count OutCell.Resize(N).Clear Run "ATPVBAEN.XLAM!Fourier", InData, OutCell, True, False With WorksheetFunction For Each Cell In OutCell.Resize(N).Cells If Abs(.Imaginary(Cell)) < k Then Cell.Value = CDbl(.ImAbs(Cell)) End If Next Cell End With End Sub Suppose your calculation was suppose to return a real number "5". Due to rounding, the vector could be rotated slightly. Your vector of '5 might now be "4.9999975+.0049999999i" Your use of ImReal will return 4.9999975. We can get rid of small rotation errors via Abs. ?worksheetfunction.Imabs("4.9999975+.0049999999i") 5.00000000000053 Again, just something to consider. = = = = HTH :) Dana DeLouis Neal Carron wrote: Dana, OK, I've adapted most of your suggestions to my situation. My original function is real. None of the values is integer. Its FFT is complex. The inverse FFT is real, and it comes out of the Fourier routine as numbers-as-text. I've got your routine working for it [replacing your .ImAbs(Cell) by .ImReal(Cell)] The complex FFT remains a problem. Those values that are complex appear in their cells as numbers. But those that have zero imaginary part are text. I can't identify ahead of time which ones will be text. I need a function applying to all cells, that will convert text to real numbers, and leave complex numbers alone. How do I do that? - Neal "Dana DeLouis" wrote: Hi. This is a shorter / modified version of something I use. Maybe you can get some ideas here. Take the inverse of the data, and output it beginning at E1. Note that you are assuming the data will be integers. This may not be the case. You may be able to adjust it to your situation. (with integer output, are you doing convolution, or high-precision multiplication ?? ) Sub YourRoutine() Call FFTInverse([C1:C16], [E1]) End Sub Sub FFTInverse(InData, OutCell) Dim N Dim Cell N = InData.Cells.Count OutCell.Resize(N).Clear Run "ATPVBAEN.XLAM!Fourier", InData, OutCell, True, False For Each Cell In OutCell.Resize(N).Cells Cell.Value = CDbl(WorksheetFunction.ImAbs(Cell)) Next Cell End Sub = = = = = HTH Dana DeLouis Neal Carron wrote: Dana, All true, but I don't see how to apply your method to a column. It seems I'd have to create a temporary column B, evaluate the FT in it, and then set another column =ImReal(B1) as the final answer. Is that what you meant? - Neal "Dana DeLouis" wrote: Hi. The output is a complex number represented in Excel as a string. =Complex(5,0) will show up as 5, but as a string, much like you are seeing. =ImReal(B1) will return the Real part. Due to 'rounding issues', you may prefer to use IMABS(B1) is you believe the Complex part is suppose to be zero. The advantage also is that it will convert those cells that do have very small imaginary parts. HTH Dana DeLouis Neal Carron wrote: The output of the Fourier Transform fills most cells as numbers (all those that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: Can the FT routine be set to enter results directly as numbers? If not, how do I convert numbers-as-text to numbers in a Macro? - Neal |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
Dana,
Thank you. That worked. [In your last Cell.Value = CDbl(.ImAbs(Cell)), I changed it to ..ImReal(Cell)] This seems like an awfully laborious way just to change a number-as-text to a number. Any idea why the Fourier routine doesn't just put out bonafide numbers? - Neal "Dana DeLouis" wrote: Hi. I don't think I understand the question. I think you want if a cell has a 0 imaginary part, convert the real part to a number, and not string. If the cell does have an imaginary part, then leave it alone. Sub YourRoutine() Call FFTInverse([C1:C4], [E1]) End Sub Sub FFTInverse(InData, OutCell) Dim N Dim Cell Const k As Double = 0.0000000001 N = InData.Cells.Count OutCell.Resize(N).Clear Run "ATPVBAEN.XLAM!Fourier", InData, OutCell, True, False With WorksheetFunction For Each Cell In OutCell.Resize(N).Cells If Abs(.Imaginary(Cell)) < k Then Cell.Value = CDbl(.ImAbs(Cell)) End If Next Cell End With End Sub Suppose your calculation was suppose to return a real number "5". Due to rounding, the vector could be rotated slightly. Your vector of '5 might now be "4.9999975+.0049999999i" Your use of ImReal will return 4.9999975. We can get rid of small rotation errors via Abs. ?worksheetfunction.Imabs("4.9999975+.0049999999i") 5.00000000000053 Again, just something to consider. = = = = HTH :) Dana DeLouis Neal Carron wrote: Dana, OK, I've adapted most of your suggestions to my situation. My original function is real. None of the values is integer. Its FFT is complex. The inverse FFT is real, and it comes out of the Fourier routine as numbers-as-text. I've got your routine working for it [replacing your .ImAbs(Cell) by .ImReal(Cell)] The complex FFT remains a problem. Those values that are complex appear in their cells as numbers. But those that have zero imaginary part are text. I can't identify ahead of time which ones will be text. I need a function applying to all cells, that will convert text to real numbers, and leave complex numbers alone. How do I do that? - Neal "Dana DeLouis" wrote: Hi. This is a shorter / modified version of something I use. Maybe you can get some ideas here. Take the inverse of the data, and output it beginning at E1. Note that you are assuming the data will be integers. This may not be the case. You may be able to adjust it to your situation. (with integer output, are you doing convolution, or high-precision multiplication ?? ) Sub YourRoutine() Call FFTInverse([C1:C16], [E1]) End Sub Sub FFTInverse(InData, OutCell) Dim N Dim Cell N = InData.Cells.Count OutCell.Resize(N).Clear Run "ATPVBAEN.XLAM!Fourier", InData, OutCell, True, False For Each Cell In OutCell.Resize(N).Cells Cell.Value = CDbl(WorksheetFunction.ImAbs(Cell)) Next Cell End Sub = = = = = HTH Dana DeLouis Neal Carron wrote: Dana, All true, but I don't see how to apply your method to a column. It seems I'd have to create a temporary column B, evaluate the FT in it, and then set another column =ImReal(B1) as the final answer. Is that what you meant? - Neal "Dana DeLouis" wrote: Hi. The output is a complex number represented in Excel as a string. =Complex(5,0) will show up as 5, but as a string, much like you are seeing. =ImReal(B1) will return the Real part. Due to 'rounding issues', you may prefer to use IMABS(B1) is you believe the Complex part is suppose to be zero. The advantage also is that it will convert those cells that do have very small imaginary parts. HTH Dana DeLouis Neal Carron wrote: The output of the Fourier Transform fills most cells as numbers (all those that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: Can the FT routine be set to enter results directly as numbers? If not, how do I convert numbers-as-text to numbers in a Macro? - Neal |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fourier, numbers-as-text, and macros.
Any idea why the Fourier routine doesn't just put out bonafide numbers?
Hi. Excel represents Complex numbers as strings, so I guess this is the only way to properly output the data. Even though a number like =Complex(3,0) has only a real part of '3, Excel represents this as the string '3. One may think that "Val" would be a possible solution. Just note that when you take the Val of a complex number with a zero real, you are getting the complex part, and is probably not what you want. Sub Demo() Dim x As Double Dim c As String With WorksheetFunction c = .Complex(0, 5.001) x = Val(c) End With End Sub 'returns: 5.001 As a side note: Custom vba Fourier routines usually do not use strings. Instead, they keep the Real, and Imaginary numbers in separate arrays. Maybe something to look at in the future ?? = = = = HTH :) Dana DeLouis Neal Carron wrote: Dana, Thank you. That worked. [In your last Cell.Value = CDbl(.ImAbs(Cell)), I changed it to .ImReal(Cell)] This seems like an awfully laborious way just to change a number-as-text to a number. Any idea why the Fourier routine doesn't just put out bonafide numbers? - Neal "Dana DeLouis" wrote: Hi. I don't think I understand the question. I think you want if a cell has a 0 imaginary part, convert the real part to a number, and not string. If the cell does have an imaginary part, then leave it alone. Sub YourRoutine() Call FFTInverse([C1:C4], [E1]) End Sub Sub FFTInverse(InData, OutCell) Dim N Dim Cell Const k As Double = 0.0000000001 N = InData.Cells.Count OutCell.Resize(N).Clear Run "ATPVBAEN.XLAM!Fourier", InData, OutCell, True, False With WorksheetFunction For Each Cell In OutCell.Resize(N).Cells If Abs(.Imaginary(Cell)) < k Then Cell.Value = CDbl(.ImAbs(Cell)) End If Next Cell End With End Sub Suppose your calculation was suppose to return a real number "5". Due to rounding, the vector could be rotated slightly. Your vector of '5 might now be "4.9999975+.0049999999i" Your use of ImReal will return 4.9999975. We can get rid of small rotation errors via Abs. ?worksheetfunction.Imabs("4.9999975+.0049999999i") 5.00000000000053 Again, just something to consider. = = = = HTH :) Dana DeLouis Neal Carron wrote: Dana, OK, I've adapted most of your suggestions to my situation. My original function is real. None of the values is integer. Its FFT is complex. The inverse FFT is real, and it comes out of the Fourier routine as numbers-as-text. I've got your routine working for it [replacing your .ImAbs(Cell) by .ImReal(Cell)] The complex FFT remains a problem. Those values that are complex appear in their cells as numbers. But those that have zero imaginary part are text. I can't identify ahead of time which ones will be text. I need a function applying to all cells, that will convert text to real numbers, and leave complex numbers alone. How do I do that? - Neal "Dana DeLouis" wrote: Hi. This is a shorter / modified version of something I use. Maybe you can get some ideas here. Take the inverse of the data, and output it beginning at E1. Note that you are assuming the data will be integers. This may not be the case. You may be able to adjust it to your situation. (with integer output, are you doing convolution, or high-precision multiplication ?? ) Sub YourRoutine() Call FFTInverse([C1:C16], [E1]) End Sub Sub FFTInverse(InData, OutCell) Dim N Dim Cell N = InData.Cells.Count OutCell.Resize(N).Clear Run "ATPVBAEN.XLAM!Fourier", InData, OutCell, True, False For Each Cell In OutCell.Resize(N).Cells Cell.Value = CDbl(WorksheetFunction.ImAbs(Cell)) Next Cell End Sub = = = = = HTH Dana DeLouis Neal Carron wrote: Dana, All true, but I don't see how to apply your method to a column. It seems I'd have to create a temporary column B, evaluate the FT in it, and then set another column =ImReal(B1) as the final answer. Is that what you meant? - Neal "Dana DeLouis" wrote: Hi. The output is a complex number represented in Excel as a string. =Complex(5,0) will show up as 5, but as a string, much like you are seeing. =ImReal(B1) will return the Real part. Due to 'rounding issues', you may prefer to use IMABS(B1) is you believe the Complex part is suppose to be zero. The advantage also is that it will convert those cells that do have very small imaginary parts. HTH Dana DeLouis Neal Carron wrote: The output of the Fourier Transform fills most cells as numbers (all those that are complex numbers), but all those that are real it fills as text(!) The output of the inverse Fourier Transform fills all cells with numbers-as-text(!) My output column is formatted to Number, General. But the inverse FT result is still written as text (they are all real numbers). I need them as bonafide numbers (to be plotted). Converting them to numbers is done by selecting the output cells, opening the error icon on the upper left, and selecting "convert to number". But this last action is not accepted when recording the strokes in a macro. The steps suggested in HELP on "Convert numbers stored as text to numbers" don't work. So: Can the FT routine be set to enter results directly as numbers? If not, how do I convert numbers-as-text to numbers in a Macro? - Neal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Add-in = Fourier Analysis | Excel Worksheet Functions | |||
inverse fourier | Excel Programming | |||
Fourier Series | Excel Worksheet Functions | |||
Fourier Analys | Excel Programming | |||
Fourier Analysis | Excel Worksheet Functions |