Converting Excel formula to python

Question

I have a spreadsheet with this formula. I am able to understand the condition checking part, the calculation of ($R7/$O7) and default value if condition does not satisfy. What exactly happens inside the PRODUCT(1+($U7:Z7)))-1 ?

{=IF($T7>=AA$5,($R7/$O7)/(PRODUCT(1+($U7:Z7)))-1,"")}

Also, why do we have {}? If I manually type the formula in some cell, it does not work.

I am trying to convert this formula to python. This is the code I have:

df.loc[(df['T'] >= df['AA']), 'x'] = (df['R']/df['O'])/PRODUCT()-1

My question is how do I compute the PRODUCT part of this calculation?

"why do we have {}" - this is an array formula in Excel, typed without the braces and entered by using CTRL-SHIFT-ENTER. — asongtoruin
– asongtoruin, Commented Apr 4, 2017 at 9:47
AFAIK, the PRODUCT function takes in two inputs and multiplies them. Your example has only one (assuming it's formatted correctly - does it return an input in Excel?) — Josh Friedlander
– Josh Friedlander, Commented Apr 4, 2017 at 9:52
(PRODUCT(1+($U7:Z7))) calculates the product of every value+1 in cells U7 to Z7. The {} is needed for adding 1 to every value before calculating the product. The last -1 simply subtracts 1 from the product. CSE-formulas basically works like it first calculates one array (in this case an array where 1 is added to every value) and then performs some other calculation on that array, in this case the product. — Karl Anka
– Karl Anka, Commented Apr 4, 2017 at 9:54

Karl Anka · Accepted Answer · 2017-04-04 11:07:16Z

3

If you just want to know how to calculate the product of an array where 1 is added to every value and 1 subtracted from the result it can be easily done with numpy:

import numpy as np

arr = np.array([1,2,3,4,5])
product = np.prod(arr+1) - 1
print product

Numpy calculations are done array-wise, so adding 1 to every value is simply array+1

Based on your updates in the comments this is how its done:

df.loc[(df['T'] >= df['AA']), 'x'] = (df['R']/df['O']) / ((df[['a', 'b']]+1).product(axis=1) - 1)

Where a and b are the column names. Notice that this formula returns NaN when df['T'] >= df['AA'] is false.

edited Apr 4, 2017 at 11:07

answered Apr 4, 2017 at 10:05

Karl Anka

2,9092 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting Excel formula to python

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related