With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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.

=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

=C$3^(D$3:G$3)

and then press CTRL+SHIFT+ENTER

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

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.

{=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)

=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?

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

This is not working as expected:

=TEXT(SUM(INT((27471187-IN

what are you expecting from this bit????

2^(D$3:G$3)

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?

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?

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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)