Excel - Applying a formula for a range of cells

January 2017


Issue


I am trying to use a input cell in Excel:
  • In my Excel sheet I have data on A1,A2,A3,A4,A5 containing the amount of ml ink being used.
  • In cell B1 I have the input of Price per ml that I want the user to put in.
  • In C1 I have the formula of "=ROUNDDOWN(SUM(A1*B1),2)"
  • Now if I drag C1 down to C5 I want in my formula A1 to count from A1 to A5 but I don't want B1 to count but be constant.
  • My problem is when I drag down from C1 to C5 that in my formula B1 also counts from B1 to B5 .
  • How can I have B1 as input and remain constant in the formula

Solution

  • You have to put a $ between the B and 1 to fix the cell thus your formula become :

=ROUNDDOWN(SUM(A1*B$1),2)

Note

Thanks to aquarelle for this tip on the forum.

Related


Published by aakai1056. Latest update on November 6, 2016 at 02:03 PM by owilson.
This document, titled "Excel - Applying a formula for a range of cells," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).