I want to second Johnny's praise of SUMPRODUCT. It is very often
exactly what I need as well. Let me give an example. I'm a teacher,
and I have student grades in a spreadsheet, with students as rows,
assignments as columns, and grades in the intersecting cells. Above the
student grades, I have two special rows: points possible and weight for
each assignment. So it looks something like
... 8 9 10 ... 20 ...
A
B As1 As2 As3 Ask (assignments, tests, etc.)
C Weights w1 w2 w3 ... wk
D Pts possible n1 n2 n3 ... nk
E Student 1 s11 s12 s13 ... s1k
F Student 2 s21 s22 s23 ... s2k
..
where weights are between 0 and 1 and total to 1, and each sij is
between 0 and nj. Now I can compute Student 1's total as a number
between 0 and 1 using
=SUMPRODUCT(E8:E20; 1/$D8:$D20; $C8:$C20)
and copy this formula down the column for all students. I can even copy
the formula up to row C, where I should get 1.0000 as the answer, to
verify that the weights add up correctly.
Sorry if this is a bit off-topic, but I agree with Johnny that this is a
feature of the spreadsheet worth trumpeting.
Todd Wilson
Johnny Rosenberg wrote, on 06/02/2011 05:56 AM:
> It's unbelievable. Since 1999 I have used
> Excel/OpenOffice.org/LibreOffice quite a lot, almost daily. I consider
> some of my spreadsheets somewhat advanced with macros and long
> formulas doing some fancy stuff…
>
> Today I accidently ran into the SUMPRODUCT() function and found that
> it's exactly what I really needed for all those years! How could I
> manage without it? Until today I could, but now I reached the limits
> of the combination My brain + No SUMPRODUCT() function…
> So I started to search the Internet for how to solve a problem that I
> had, which I couldn't solve with SUMIF or other functions and I found
> some SUMIF examples. The last example of one web page was however a
> SUMPRODUCT() example which solved my problem very easily. I could even
> remove three entire columns in my spreadsheet, every one of which
> included some complex formulas…
>
> I guess the lack of knowledge of the SUMPRODUCT() function forced me
> to learn quite a few other functions, workarounds and tricks…
>
> Maybe this would rather be sent to the Discuss list, but I thought
> that this could be a hint for other people who didn't know about the
> SUMPRODUCT() function for some strange reason…
>
>
> Kind regards
>
> Johnny Rosenberg
> ジョニー・ローゼンバーグ
--
-----------------------------------------------------------------
To unsubscribe send email to [email protected]
For additional commands send email to [email protected]
with Subject: help
