Prevent cell numbers from incrementing in a formula in Excel

0 votes

I have a formula in Excel that needs to be run on several rows of a column based on the numbers in that row divided by one constant. When I copy that formula and apply it to every cell in the range, all of the cell numbers increment with the row, including the constant. So:

B1=127
C4='=IF(B4<>"",B4/B1,"")'

If I copy cell C4 and paste it down column C, the formula becomes

=IF(B5<>"",B5/B2,"")
=IF(B6<>"",B6/B3,"")
etc.

when what I need it to be is

=IF(B5<>"",B5/B1,"")
=IF(B6<>"",B6/B1,"")
etc.

Is there a simple way to prevent the expression from incrementing?

Mar 11 in Database by Edureka
• 13,640 points
41 views

1 answer to this question.

0 votes

In Excel, you can use a feature called 'locked reference,' and you can lock a range with $ symbols. You'd use the following as an example:

=IF(B4<>"",B4/B$1,"")
This freezes the 1 in B1 so that it doesn't change when you copy it to the next row.

When you copy $B$1 down a row or across a column, the range does not change.

answered Mar 15 by gaurav
• 13,560 points

Related Questions In Database

0 votes
1 answer

Remove time from date field in Excel formula

Use the Find And Replace function to ...READ MORE

answered Mar 30 in Database by gaurav
• 13,560 points
26 views
0 votes
1 answer

What is the Excel formula to countif text partially makes up a cell?

With Formulas, Countif Partial String/Substring Match We can ...READ MORE

answered Mar 30 in Database by gaurav
• 13,560 points
38 views
0 votes
1 answer

How to enter a series of numbers automatically in Excel

Excel, unlike other Microsoft Office programmes, does ...READ MORE

answered Apr 4 in Database by Edureka
• 13,640 points
25 views
0 votes
1 answer

How do I split a cell in Excel into two or more where they are divided horizontally?

Table cells should be combined. To make a ...READ MORE

answered Apr 6 in Database by gaurav
• 13,560 points
45 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
339 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,080 points
303 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,080 points
3,170 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
2,581 views
0 votes
1 answer

Insert a value to a cell in excel using formula in another cell

Select the cell where the formula should ...READ MORE

answered Mar 25 in Database by gaurav
• 13,560 points
77 views
0 votes
1 answer

Excel formula to remove space between words in a cell

There are three fast techniques to get ...READ MORE

answered Mar 30 in Database by gaurav
• 13,560 points
38 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP