0

I have a Table in Excel that is laid out as follows. What I require is a way of accessing a table row by index for a calculation.

To be precise, I have been asked to work out an overall coefficient for this table (and many like it). So in this case, the Coefficient would be -

=SUM(3*0, 1*2, 3*0, 11*0) / SUBTOTAL(109, [Count])

In short, I need a way of getting the total of [Value]*[Count] for every row, but without adding an extra column. The table below is an example, and some have more rows, so the solution must be scalable and not explicit, as in my example above.

The trouble is, I don't want to have to explicitly declare the value, but I can't find a way of referencing a column/row index. Is this possible? Thanks.

BigHand Mobile - Access                                Value   Count    %
I already use BigHand mobile                           0       3        16.7%
I don't use BigHand mobile, but I would like to        2       1        5.6%
I don't use BigHand mobile, and I do not require it    0       3        16.7%
I don't dictate so do not require it                   0       11       61.1%
Coefficient                                            ???
Improve this question
6
  • I don't understand, where does the 109 come from? You can definitely use an array formula or a formula that finds addresses and rows/columns with offset. I'm just not sure what it is that you're looking to pull, explicitly.
    – Raystafarian
    Jan 29, 2014 at 13:57
  • I'll update my question to better ask what is required. FYI, 109 is the SUBTOTAL key for SUM. So SUBTOTAL(109, (3, 1, 3, 11)) is basically the same as SUM(3, 1, 3, 11), it's just through habit that I use it. Thanks.
    – David Gard
    Jan 29, 2014 at 16:05
  • So is the problem that it's dynamic? A sumproduct would do what you need, if you defined the arrays like sumproduct(B:B,C:C) except your coefficient is in B. Where are you hung up? Counting the rows?
    – Raystafarian
    Jan 29, 2014 at 16:44
  • Ah ha, SUMPRODUCT([value],[count]) / SUBTOTAL(109, [Count]) to the rescue. Thanks for the heads up, I've never come across SUMPRODUCT() before (will mark as correct if you add as an answer).
    – David Gard
    Jan 29, 2014 at 16:54
  • Yeah, there are some interesting formulas out there that aren't easy to run across when looking!
    – Raystafarian
    Jan 29, 2014 at 17:20

1 Answer 1

Reset to default
1

Try using a sumproduct to get the array multiplication to work.

=SUMPRODUCT(B:B,C:C) / SUBTOTAL(109,C:C)

Improve this answer
1
  • Great stuff. As noted in the comments to my question, I used SUMPRODUCT([value],[count]) / SUBTOTAL(109, [Count]) as I was dealing with tables, not standard arrays, but SUMPRODUCT() was exactly what I needed. Thanks.
    – David Gard
    Jan 30, 2014 at 9:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .