2

I would like to use a same formula for cells in a column. Instead of writing it for all cells, is there a way to set it in a cell and tell to apply this formula to all cells in a column (or any set of cells)?

If I use the fill handle, as suggested by @Michelfrancis Bustillos, I have then to correct all cells because I am using a static cell.

enter image description here

As you can see, I would like to set the H1 cell for all cells in the column E. But the fill handle set H2 for the cell E4, and so on for the next cells.

The spreadsheet: https://docs.google.com/spreadsheets/d/1fM1Rrwj_lrxiGoiLyKEQqsYfcPbw6sA4avP9oVBGuHE/edit?usp=sharing

4

I would like to set the H1 cell for all cells in the column E. But the fill handle set H2 for the cell E4, and so on for the next cells.

This means you need an absolute reference: H$1 instead of H1. The dollar sign means this number will not be changed as the formula is propagated to other cells.

3

You can use the fill handle (small square that appears at the bottom right corner of the cell when it is selected/moused over) to fill the formula down.

  • I am using it, but it is not very useful because I have some cells which are the same for all rows and this tool is not smart enough to understand that. – Gael Jan 8 '16 at 15:16
  • So you want it so skip over certain cells, or you want it to not auto-increment? – Michelfrancis Bustillos Jan 8 '16 at 15:18
  • I have added some details in my question – Gael Jan 8 '16 at 15:27
  • Not having the time to read this all myself, I'd suggest you take a look here: mrexcel.com/forum/excel-questions/… – Michelfrancis Bustillos Jan 8 '16 at 15:31
  • Thank you. I do not have really understood how to do that yet, but I will! – Gael Jan 8 '16 at 15:40
1

Please try:

=ArrayFormula(H$1*(0,365*B3:B*C3:C+D3:D))

I have simplified your formula a little, anchored (with $) the H1 reference so that remains the row reference for the "H" column entry even when the formula is copied down, and, since you mention a formula for all cells in a column converted it into array form, so no need to copy or drag.

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.