Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a column B, so my data is from B1 to say B500. I want C1 to be B1

C2 to be B1*B2.

C3 to be B1*B2*B3

C4 to be B1*...*B4 ...

C500 to be B1*...*B500

Is there an easier way to do this rather than typing the formula manually? I think I can use the function PRODUCT, but I would still have to type the rest of the references manually. Anyone has a more elegant solution?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Yes, you can use the PRODUCT() function, passing a range of cells as an argument. Simply enter the following into C1:

=PRODUCT(B$1:B1)

Then drag the formula down (by grabbing the small square at the bottom right of the cell highlight rectangle).

The $ means an absolute address, so the start of the range remains at B1 while the end is extended for each new row that you drag the formula to - for C2 it'll become B$1:B2, for C3 it'll become B$1:B3, and so on.


Alternatively, if the numbers in B1-B500 are sequential from 1 to 500, you can use the FACT() function (which calculates the factorial of a given number) to achieve the same thing.

share|improve this answer

Here's how I do this sort of thing:

  • In C1, I set =B1
  • In C2, I set =B2*C1
  • In C3 through C500, I make it a copy of C2, by selecting a box and using Ctrl+D.

When Excel copies formulas, it uses relative addressing, unless you tell it not to.

So C500 ends up with a formula that says =B500*C499.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.