I'm trying to quickly compute the sum of reciprocals of several values using Excel 2011 for Mac. Most of the documentation and answers I can find online say it should be as easy as =SUM(1/rangestart:rangeend) but I'm getting unexpected results and really weird behaviour from Excel. The only real way I can illustrate it is with a copypasta from Excel to plaintext:
|C1 C2 C3 C4 C5 C6
----+---------------------------------------------------------------
R1 |R Ohms 4 F2
R2 |1 1000 sum(R1:R5) 14500 4 F3
R3 |2 2200 1/sum(R1:R5) 6.90E-05 4 F4
R4 |3 5600 sum(1/R1:R5) 0.000178571 4 F5
R5 |4 1000 1/R1+…+1/R5 0.002845883 #VALUE! sum(1/F2:F5)
R6 |5 4700 1 1/F2+…+1/F5
Columns 1-4 are in reference to resistors. SUM(R1:R5) is if they're in series, and SUM(1/R1:R5) should be equal to 1/R1+...+1/R5 as if they're in parallel, but obviously the answers are different. Furthermore, in columns 5 and 6, I tested this with values that would give me a known result as a control experiment, and Excel threw an error with SUM(1/F2:F5) instead of a result!
What am I missing to do this the right way in Excel, and what's going on here?
Per @chris neilsen (and for the sake of an answer):
You need to enter =SUM(1/R1:R5) as an Array Formula
(ie press Command+Return rather than just Return)