Hi,
I am having trouble using an excel formula of type:
SUM IF (a<b; PRODUCT(a;c); PRODUCT(b,c))/(e-f)
where a, b, c, d, e, f in the above formulae are all row data. The exact formula i am using is as shown below:
=(SUM(IF($C$7:$AL$7<C8:AL8;PRODUCT($C$7:$AL$7;$C$6:$AL$6);PRODUCT($C$8:$AL$8;$C$6:$AL$6))))/(C3-C37)
The result is zero for all cells, and i am wondering if there is something, maybe syntax violation, or some other easy way of doing this? Can someone help me?
Thanks,
Manywise.
The formula outlined would need to be confirmed as an Array obviously.
It's not clear what you're trying to achieve exactly but if you want the product of the individual terms - eg:
SUM of PRODUCT(C6,C7) + PRODUCT(D8,D6) etc divided by (e-f) then you would need to adjust slightly, perhaps to:
=SUM(IF($C$7:$AL$7<$C$8:$AL$8;$C$7:$A$L7;$C$8:$AL$8)*$C$6:$AL$6)/(C3-C37) confirmed with CTRL + SHIFT + ENTER
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Did you array-enter it (Ctrl-Shift-Enter)?
Thanks a lot, i did use CTRL + SHIFT + ENTER, it seems to be working now. The problem was how i computed the product. Considering the output and amount of data is not easy for me to verify that it does exactly what i want it to do, but i will have to try different ways to that. Thanks again for the help.
BR
Manywsie
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks