Hi Excel folks, would appreciate some help on whether I can cut down a step of creating a helper column by using an array formula (or any other method but not VBA)? Currently I'm using a helper column and sumproduct with a double negative (--) (to get a sumproductif formula). However, I'm looking to cut down as many steps as possible because my table is large, and I want to filter it on the go (hence will need a formula that won't mess up when I filter the table). Thanks a million!!
How is column E calculated?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Column E is a hard-coded number. Background: It's the return on the stock in said row.
But your explanatory text in the file states:
"Can I get the numbers in B14:B16 without creating column E which is the helper?"
But if those values are hard-coded, and so we don't know how they are derived, how can we expect to generate results without using them?
Regards
Oh sorry! Column D is the helper row.
Edited, response was to column E as the helper, not D
Just replace $D$2:$D$10 with an extended version of your current formulas in that column, i.e. in B14:
=SUMPRODUCT(--($B$2:$B$10=A14),$C$2:$C$10/SUMIF($B$2:$B$10,$B$2:$B$10,$C$2:$C$10),$E$2:$E$10)
Regards
Try in B14
=SUMPRODUCT(($B$2:$B$10=A14)*$E$2:$E$10*INDEX($C$2:$C$10*($B$2:$B$10=A14)/SUMIF($B$2:$B$10,A14,$C$2:$C$10),0))
Copy down
Life's a spreadsheet, Excel!
Say thanks, Click *
This works. Thanks a mil!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks