Veeam is happy to provide a free NFR license for one year. It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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

klyles95Author Commented:

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:

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)

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

klyles95Author Commented:

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.

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

klyles95Author Commented:

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

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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