CODEWITHSUNDEEP
excelformula
krisharmas
krisharmas

Reputation: 211

MS Excel create virtual column to use in formula

I want to do something similar to summing 1/x from 1 to 100 without creating an extra column to help with the calculation. I want my first column to be the numbers 1 through 100. And I want a cell to show the sum of 1/x where x is each cell in the first column. Currently the only way I can think to do this is to create a second column to do 1/x for each individual cell then sum the second column. Is there any solution to doing this without having to create the second column?

Thanks!

Upvotes: 0

Views: 2794

Answers (3)

Charles Williams
Charles Williams

Reputation: 23550

You need to use an array formula as below:

{=SUM(1/A1:A100)}

where A1:A100 contains 1 to 100

You create the array formula bu typing the formula as =SUM(1/A1:A100) and then pressing Control-Shift-Enter.

If you do it correctly the formula then shows up with the curly brackets {} but you don't enter the curly brackets yourself.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46451

You can use this "array formula"

=SUM(1/A1:A100)

confirmed with CTRL+SHIFT+ENTER

....or avoid array entry by using SUMPRODUCT

=SUMPRODUCT(1/A1:A100)

both versions assume you don't have zeroes (or blanks) in A1:A100

if you might have zeroes or blanks then use this array formula

=SUM(IF(A1:A100,1/A1:A100))

Upvotes: 3

Aprillion
Aprillion

Reputation: 22340

if you mean 1 + 1/2 + 1/3 + ... + 1/100, use following array formula (entered by Ctrl+Shift+Enter instead of just Enter):

=sum(1/row(A1:A100))

Upvotes: 2

Related Questions