2

I have three columns. Two columns have values in them. In the third column I want to have each row be a percentage of the other two values in that row (ex. =N2/L2).

How can I have this third column always have the values of the numbers in that row?

The next row in this sequence would be =N3/L3, then =N4/L4. I want the values to be in the corresponding row.

  • Doesn't this work when you copy-paste the formula? – Franck Dernoncourt Dec 6 '13 at 16:39
  • I don't want to type in the numbers every time. – user2390330 Dec 6 '13 at 16:40
  • How about copy-paste the formula without editing it? It should change numbers for you. – Franck Dernoncourt Dec 6 '13 at 16:40
  • Didn't work, it shows the same number every time – user2390330 Dec 6 '13 at 16:42
  • Or just drag the auto-fill handle. – ale Dec 6 '13 at 16:42
2

To copy a formula from one cell to more cells within the same column (or row) and have the references change to match the appropriate cell, simply use the "auto-fill" tool. Click the cell you want to copy. In the lower right corner should be a square. Simply drag that handle to populate however many cells you need. Each reference to another cell in your formula will be incremented to match.

Google Support: Auto-fill

0

Another option is to enter:

=(N2:N/L2:L)

with Ctrl+Shift+Enter.

This will fill cells past where the column immediately to the left is blank (as will double-clicking the fill-handle - the behaviour for Sheets and Excel is different) whereas dragging can be stopped to suit (but could be tedious for a very large sheet).

On the other hand, the array formula version need not be applied to an entire column. If you know you would only drag down to say Row100 you could use:

=ArrayFormula(N2:N100/L2:L100) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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