Solved

Translation of an excel formula to Oracle SQL

Posted on 2014-02-28
18
797 Views
Last Modified: 2014-02-28
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.
0
Comment
Question by:klyles95
  • 5
  • 5
  • 4
  • +2
18 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39896051
>>What is this formula doing?

With your data and Excel 2007, it generates an error for me.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39896069
I'm getting an error with Excel 2010
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39896072
Ditto for Excel 2003.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39896086
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,000,000.00

   which is close, but rounded to:


select power(2,102) from dual

 5070602400912917605986812821504  which checks out by my math
0
 

Author Comment

by:klyles95
ID: 39896087
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39896094
>>and then press CTRL+SHIFT+ENTER

OK, I get a number.  Now to figure out what that number actually is...
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39896095
doing that I get

4294967296


which is 2^32  or , with respect to your spreadsheet - 2^D3
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39896103
>>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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39896109
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,000,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)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:klyles95
ID: 39896116
OK...so the array formula I am translating is this :

=TEXT(SUM(INT((27471187-INT(27471187/2^(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?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39896123
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39896139
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-INT(27471187/2^(D$3:G$3))))),"0000000000")

what are you expecting from this bit????
2^(D$3:G$3)
0
 

Author Comment

by:klyles95
ID: 39896140
REPEAT:

the array formula I am translating is this :

{=TEXT(SUM(INT((27471187-INT(27471187/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?
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39896231
The formula makes an array with the 4 numbers {4294967296,268435456,16777216,262144} when array entered (Ctrl+Shift+Enter).
The numbers are the result of the calculations
2^32 = 4294967296
2^28 = 268435456
2^24 = 16777216
2^18 = 262144
Go to the formula line, highlight all, press F9 to see the result, Esc to leave.
But what the purpose is I don't know, since the result in a cell, will only be the first value.

It can be used in another formula.
Sum of the 4 values =SUM(C$3^(D$3:G$3)) Array entered
With Index like this =INDEX(C$3^(D$3:G$3),2) the result will be the second value.
Could be it is made as a test before making a name with the formula.
A name AAAA with the formula in the "Refers to" =C$3^(D$3:G$3), could be used in a formula like above =INDEX(AAAA,2)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39896283
>>"REPEAT"

I'd like to stress that you are not listening to us. The formula in use is not good.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39896331
>>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?
0
 

Author Comment

by:klyles95
ID: 39896505
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))))),"0000000000")}  
      
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,16777216,262144

J3/C$3^(D$3:G$3) == divide input number 27471187 by each of the 4 numbers above
= 0.00639613415114582,0.102338146418333,1.63741034269332,104.794261932373

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,27471186,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))))),"0000000000")  == left pad the number (LPAD function)
=0109884643 = final output
0
 

Author Closing Comment

by:klyles95
ID: 39896510
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
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now