Forum Discussion

null null's avatar
null null
Copper Contributor
Feb 16, 2018

Constants in formulas

I still use @SUM() formatting for my Excel93 formulas.  I have payroll worksheets that have a line for each week of the year.  I enter the number of days worked (C1) multiplied by pay rate (D10) to get the week's gross amount (D1).  However, when I copy the formula throughout the worksheet, my constant, the pay rate (D10) changes to the next line (D11),etc.  How do I keep the pay rate constant throughout the worksheet?

Formulas & Functions
Need Help

6 Replies

  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Feb 16, 2018

    Hi there


    Adjust your formula slightly by using $ within the cell reference, in your case it would be $D$10.

    This is called Absolute Cell Referencing and will ensure that the cell reference for your Pay Rate won't change.

    Hope that helps.

    Cheers
    Damien

    • Phillip J. Seaman's avatar
      Phillip J. Seaman
      Copper Contributor
      Apr 18, 2018

      Hi Damien,

      I have the same issue and your solution did not work for me... let me elaborate a bit...

      Cell L3 value = $0.29

      I have a table that has rows of calculated fields that have the following formula in each =IF(D7=-10, ,D7*L3)

       

      I have the same issue if I copy and paste this cells formula it changes the D reference but also the L reference.  D SHOULD change but L should remain L3...

      I tried putting a $ sign in front of the L (i.e. =IF(D7=-10, ,D7*$L3) - FAILED

      So where do I put the "$"?

       

      Thanks for your help.

      • Damien_Rosario's avatar
        Damien_Rosario
        Silver Contributor
        Apr 18, 2018

        Hi Phillip

        The $ locks in the column/row.

        So $L locks in column L and $3 locks in row 3.

        As Sergei says above, $L$3 will do the trick.


        Cheers
        Damien

Resources