klyles95
asked on
Translation of an excel formula to Oracle SQL
I am trying to convert an excel formula into oracle sql. The excel formula is straight forward for the most part.
However, there is a portion of the formula I can not understand and so can not interpret.
Assume:
C3 = 2
D3 = 32
E3 = 28
F3 = 24
G3 = 18
H3 = 0
L3 contains the following formula: {=C$3^(D$3:G$3)}
What is this formula doing? I thought it was 2 to the power of the sum of D3-G3, i.e. 2^102
Therefore I wrote POWER(2,102). But based on the expected result for the entire formula, it is not.
Help will be appreciated.
However, there is a portion of the formula I can not understand and so can not interpret.
Assume:
C3 = 2
D3 = 32
E3 = 28
F3 = 24
G3 = 18
H3 = 0
L3 contains the following formula: {=C$3^(D$3:G$3)}
What is this formula doing? I thought it was 2 to the power of the sum of D3-G3, i.e. 2^102
Therefore I wrote POWER(2,102). But based on the expected result for the entire formula, it is not.
Help will be appreciated.
I'm getting an error with Excel 2010
Ditto for Excel 2003.
If I change the formula to
=C$3^SUM(D$3:H$3)
then I get 5,070,602,400,912,920,000, 000,000,00 0,000.00
which is close, but rounded to:
select power(2,102) from dual
50706024009129176059868128 21504 which checks out by my math
=C$3^SUM(D$3:H$3)
then I get 5,070,602,400,912,920,000,
which is close, but rounded to:
select power(2,102) from dual
50706024009129176059868128
ASKER
What I have researched so far is that this is an array formula, i.e. the curly braces. To enter array formulas you enter into the cell the following exactly:
=C$3^(D$3:G$3)
and then press CTRL+SHIFT+ENTER
=C$3^(D$3:G$3)
and then press CTRL+SHIFT+ENTER
>>and then press CTRL+SHIFT+ENTER
OK, I get a number. Now to figure out what that number actually is...
OK, I get a number. Now to figure out what that number actually is...
doing that I get
4294967296
which is 2^32 or , with respect to your spreadsheet - 2^D3
4294967296
which is 2^32 or , with respect to your spreadsheet - 2^D3
>>with respect to your spreadsheet - 2^D3
Looks like sdstuber has it. The array formula with power seems to only take the first value in the range.
If you play around with any of the other numbers except D3, it doesn't change the value.
Looks like sdstuber has it. The array formula with power seems to only take the first value in the range.
If you play around with any of the other numbers except D3, it doesn't change the value.
building the array formula like this with ctrl-shift-enter
{=C$3^SUM(D$3:G$3)}
yields 5,070,602,400,912,920,000, 000,000,00 0,000.00
again, as expected but rounded off
so, if you want the exact value in oracle use power(2,102)
if you want oracle to round the same way then use
round(power(2,102),-16)
{=C$3^SUM(D$3:G$3)}
yields 5,070,602,400,912,920,000,
again, as expected but rounded off
so, if you want the exact value in oracle use power(2,102)
if you want oracle to round the same way then use
round(power(2,102),-16)
ASKER
OK...so the array formula I am translating is this :
=TEXT(SUM(INT((27471187-IN T(27471187 /2^(D$3:G$ 3))))),"00 00000000")
If input = 27471187 (I have hard coded it in above)
output = 0109884643
How do I write this in oracle SQL?
=TEXT(SUM(INT((27471187-IN
If input = 27471187 (I have hard coded it in above)
output = 0109884643
How do I write this in oracle SQL?
I'm not convinced your Excel formula is correct in the first instance.
using CTRL+SHIFT+ENTER so that I get the array formula, the computed result is:
4294967296
which is the same as `
=C3^D3
In other words the range
(D$3:G$3)
is only returning the first cell, D3
using CTRL+SHIFT+ENTER so that I get the array formula, the computed result is:
4294967296
which is the same as `
=C3^D3
In other words the range
(D$3:G$3)
is only returning the first cell, D3
what I observe is that your Excel formula isn't working as expected... so you want some SQL that doesn't work as expected too?
This is not working as expected:
=TEXT(SUM(INT((27471187-IN T(27471187 /2^(D$3:G$ 3))))),"00 00000000")
what are you expecting from this bit????
2^(D$3:G$3)
This is not working as expected:
=TEXT(SUM(INT((27471187-IN
what are you expecting from this bit????
2^(D$3:G$3)
ASKER
REPEAT:
the array formula I am translating is this :
{=TEXT(SUM(INT((27471187-I NT(2747118 7/C$3^(D$3:G$3))))),"0000000000")}
If input = 27471187 (I have hard coded it in above)
output = 0109884643
How do I write this in oracle SQL?
the array formula I am translating is this :
{=TEXT(SUM(INT((27471187-I
If input = 27471187 (I have hard coded it in above)
output = 0109884643
How do I write this in oracle SQL?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>"REPEAT"
I'd like to stress that you are not listening to us. The formula in use is not good.
I'd like to stress that you are not listening to us. The formula in use is not good.
>>The formula makes an array with the 4 numbers
Learned something new about Excel today!!!
>>"REPEAT"
Let's try this approach:
Since you want to migrate from Excel to Oracle, please provide the Oracle specifics you want to replicate?
You currently have provided us with 6 cells of data and one cell with a formula.
Will this equate to 6 columns in an Oracle table and a 7th column to hold some value?
Since we now know the formula produces an array in Excel, do you want a database object column to hold the same type of array?
Learned something new about Excel today!!!
>>"REPEAT"
Let's try this approach:
Since you want to migrate from Excel to Oracle, please provide the Oracle specifics you want to replicate?
You currently have provided us with 6 cells of data and one cell with a formula.
Will this equate to 6 columns in an Oracle table and a 7th column to hold some value?
Since we now know the formula produces an array in Excel, do you want a database object column to hold the same type of array?
ASKER
PortletPaul - you state the formula in use is not good but this is what is in operation in the spreadsheet so I do not know how to answer you. A number is supplied and a new number is created.
I want to create a user defined function. The function will take a number as a parameter (example above was 27471187) and return a number
The static values in the cell (i.e. C3-H3) and the formula (L3) is the rule by which the input value will be converted into the new number.
So in the existing spreadsheet I have:
C3 = 2
D3 = 32
E3 = 28
F3 = 24
G3 = 18
H3 = 0
INPUT PARAMETER = J3 = 27471187
OUTPUT PARAMETER = L3 (contains the array formula) = {=TEXT(SUM(INT((J3-INT(J3/ C$3^(D$3:G $3))))),"0 000000000" )}
L3 displays = 0109884643
hgholt - thank you for your response!!! I didnt know about F9, Pressing F9 has given further clarity to what the formula is doing and now I will be able to proceed with the function.
FYI:
C$3^(D$3:G$3) == 2 to the power of the 4 numbers in the cell, e.g. 2^32, 2^28 etc. (POWER function)
= 4294967296,268435456,16777 216,262144
J3/C$3^(D$3:G$3) == divide input number 27471187 by each of the 4 numbers above
= 0.00639613415114582,0.1023 3814641833 3,1.637410 34269332,1 04.7942619 32373
INT(J3/C$3^(D$3:G$3)) == round down the numbers to an integer (FLOOR function)
= 0,0,1,104
J3-INT(J3/C$3^(D$3:G$3)) == substract the numbers above from the input number, e.g. 27471187 - 0
= 27471187,27471187,27471186 ,27471083
INT((J3-INT(J3/C$3^(D$3:G$ 3)))) == round down the numbers to an integer (FLOOR function)
=27471187,27471187,2747118 6,27471083
SUM(INT((J3-INT(J3/C$3^(D$ 3:G$3))))) == sum the numbers (SUM function)
=109884643
TEXT(SUM(INT((J3-INT(J3/C$ 3^(D$3:G$3 ))))),"000 0000000") == left pad the number (LPAD function)
=0109884643 = final output
I want to create a user defined function. The function will take a number as a parameter (example above was 27471187) and return a number
The static values in the cell (i.e. C3-H3) and the formula (L3) is the rule by which the input value will be converted into the new number.
So in the existing spreadsheet I have:
C3 = 2
D3 = 32
E3 = 28
F3 = 24
G3 = 18
H3 = 0
INPUT PARAMETER = J3 = 27471187
OUTPUT PARAMETER = L3 (contains the array formula) = {=TEXT(SUM(INT((J3-INT(J3/
L3 displays = 0109884643
hgholt - thank you for your response!!! I didnt know about F9, Pressing F9 has given further clarity to what the formula is doing and now I will be able to proceed with the function.
FYI:
C$3^(D$3:G$3) == 2 to the power of the 4 numbers in the cell, e.g. 2^32, 2^28 etc. (POWER function)
= 4294967296,268435456,16777
J3/C$3^(D$3:G$3) == divide input number 27471187 by each of the 4 numbers above
= 0.00639613415114582,0.1023
INT(J3/C$3^(D$3:G$3)) == round down the numbers to an integer (FLOOR function)
= 0,0,1,104
J3-INT(J3/C$3^(D$3:G$3)) == substract the numbers above from the input number, e.g. 27471187 - 0
= 27471187,27471187,27471186
INT((J3-INT(J3/C$3^(D$3:G$
=27471187,27471187,2747118
SUM(INT((J3-INT(J3/C$3^(D$
=109884643
TEXT(SUM(INT((J3-INT(J3/C$
=0109884643 = final output
ASKER
The use of F9 is what I needed to crack this. I am able to step through the process of what this formula is doing and get the output number.
Now to write the function!
Once again thank you hgholt. Thank you all who commented
Now to write the function!
Once again thank you hgholt. Thank you all who commented
With your data and Excel 2007, it generates an error for me.