Dear All,
Am looking for some insight into an array formula that can do the following:
a1*b1 + a1*b1*a2*b2 + a1*b1*a2*b2*a3*b3 + a1*b1*a2*b2*a3*b3*a4*b4
I recognise that this is easy to do in several columns and row cells, but I would like to have this in a single cell array formula.
Anyone have any insight?
Thanks and regards John
Seems to be interesting question but feeling little bit of heat in brain so I give up and I am posting my workings so that it may be helpful for some one to work on it and give suggestion
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Try this formulaOriginally Posted by John Vieren
=SUMPRODUCT(SUBTOTAL(6,OFFSET(A1:B4,0,0,ROW(A1:B4)-MIN(ROW(A1:B4))+1)))
You can extend it for longer ranges.....
Audere est facere
Hi,
Your formula does provide the desired result.
I have not used the subtotal function previously and upon investigation, subtotal 6 appears to be the product function.
The offset function appears to give me 4 arrays of 1, 2, 3 and 4 in lenght.
Not sure yet why you are using sumproduct, I would have thought sum would work?
Let me examine further in-depth, in the meantime many thanks for your insights, john.
Hi daddylonglegs
OK I understand your formula now, after some research on subtotal and offset functions.
Bascally you are returning 4 arrays with the offset function of height and with 1,2 ; 2,2 ; 3,2 and 4,2.
subtotal 6 creates the product of the 4 arrays.
I noticed that sum entered as an array formula instead of sumproduct provides the same result.
In this case sumproduct is simply a sum function is that correct?
Once again thanks for your help and have a great day, John
Need one more elevation of the formula
The formula now looks like follows:
=SUMPRODUCT(SUBTOTAL(6,OFFSET(C4:C7,0,0,{1,2,3,4}),OFFSET(B4:B7,0,0,{1,2,3,4})))
However, what I need is 1-OFFSET(C4:C7,0,0,{1,2,3,4}) for my formula to be complete.
I tried this and it seems that this is not accepted in the subtotal range.
The simple answer is to calculate the aforementioned in a column and that use that as the range instead of C4:C7.
Does my question have a solution?
Thanks and regards JV
not sure, and it's untested but you could give it a try.
Please Login or Register to view this content.
Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi,
Sorry for confusion, I really mean that I want the difference between 1 and OFFSET(C4:C7,0,0,{1,2,3,4}).
Any thoughts?
Regards JV
Will this one serve your needs?
1-SUMPRODUCT(SUBTOTAL(6,OFFSET(C4:C7,0,0,{1,2,3,4}),OFFSET(B4:B7,0,0,{1,2,3,4})))
Hi,
What i need is the following:
SUMPRODUCT(SUBTOTAL(6,1-OFFSET(C4:C7,0,0,{1,2,3,4}),OFFSET(B4:B7,0,0,{1,2,3,4})))
That formula gives me an error.
I suspect that the references in the subtotal functions must be just that without any manipulation.
Regards JV
Hello, John
Yes, I used SUMPRODUCT just to avoid "array entry" you can use SUM if you want but formula then requires CTRL+SHIFT+ENTER.
As you surmised SUBTOTAL can only use ranges, not arrays, so your 1-OFFSET(C4:C7,0,0,{1,2,3,4}) doesn't work in that context.
Can you clarify, do you actually want 1-OFFSET(C4:C7,0,0,{1,2,3,4}) or do you want to use 1-C4:C7 in place of C4:C7? Can you give an example with expected result
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks