All categories

2 years ago

Which references are updated when you copy the formula =$E6-MAX(H$1:J4)

Computers and Technology

1 answer:

kykrilka [37]2 years ago

7 0

Once you uncover the mystery of Absolute References, copying and pasting formulas becomes child’s play.

You might have seen cell references in formulas surrounded by ‘$’ signs. For example $D$3:$D$10. What’s that all about?

Excel Absolute References

Well, the ‘$’ before the column or row reference instructs excel to keep the reference absolute. Huh, I hear you say. Ok, I’ll explain in English.

Looking at the table below we have a Commission Rate of 3% in cell G3. In column E we want to calculate the commission as Total $k x Commission Rate 3%.

Excel Absolute References

I could simply enter the formula as =D3*3% and copy it down column E, but there are two problems with this that make it not ideal:

1) I can’t easily see what the Commission Rate is without looking in the formula bar. I could put it as part of the column heading e.g. ‘Commission $ @ 3%’, but that makes my heading bigger than it needs to be, and if I change the rate at a later date I have to go back and change the heading too, which is easily overlooked.

2) If I change the rate I need to change the formula and copy it down the column again. Ok, so it might only take a few seconds to do this, but if you’re playing with scenarios and you want to change the rate a few times to see what the results are it’s much quicker and easier to just type a new figure in cell G3 and let Excel do the work to change the formula.

So assuming I’ve convinced you that referencing one cell for the commission rate is the best method let’s look at how absolute references work.

Absolute Reference example

First let’s look at what happens if you don’t use an absolute reference. If you entered in cell E3 the formula =D3*G3 you would get the correct answer. But if you were to copy that formula down the rest of column E Excel would dynamically update the formula to increase by one row as it goes down the page. You can see this to the left where the reference to G3 goes G4, G5, G6 and so on.

How Absolute References Work in Excel

What we want Excel to do is dynamically update the cell reference to column D, but to keep the Commission Rate reference on cell G3. To do this we would use the ‘$’ signs to instruct Excel that this is an absolute reference, like this =D3*$G$3. Then when we copy the formula down the column it will be entered like example on the left here.

Other ways to use absolute references

Make a whole range of cells an absolute reference: $D$1:$F$1

Make only the column absolute $D3

Make only the row absolute D$3

As you can see in the examples above, whatever the ‘$’ sign prefixes is absolute. i.e. as you copy the formula anywhere in the spreadsheet the reference prefixed by the ‘$’ sign will not change.

Shortcut to entering Absolute References

The magical F4 key instantly enters the ‘$’ signs for you. You can do it while you’re building your formula, or you can go back and edit the formula and enter them. Of course you can also type them in but it’s quicker to use F4.

Let’s look at the different ways you can enter absolute references using the F4 key in more detail

1) While you’re building your formula; as you can see below I have started to type a formula into the cell E3. I have just selected cell G3, as you can see by the marching ants (dashed line) surrounding the cell.

How to enter Absolute References

At this point, before I press enter to finish the formula, I can press the F4 key and Excel will automatically put the ‘$’ signs around G3 for me like this.

F4 key shortcut

2) Or I can go back to a cell at any time and press the F2 key to edit the cell. I can then put my cursor anywhere in the cell reference I want absolute and press F4. See below.

F4 key editing shortcut

3) If you want to absolute a range you have to highlight the cell range like the example below before pressing F4.

applying absolute references

4) The above examples show you how to apply an absolute reference to the column and the row, but if you keep pressing F4 Excel will scroll through your options. Using =D3*G3 as an example, I want to absolute G3:

a. With the first press of F4 you will get =D3*$G$3

b. With the second press of F4 you will get =D3*G$3

c. With the third press of F4 you will get =D3*$G3

d. With the fourth press of F4 you will get =D3*G3

So, now you know how Absolute References work in Excel, and how to apply them quickly using the F4 key, you can start to build better spreadsheets that you can dynamically update.

You might be interested in

A time-saving strategy that helps define unfamiliar words involves using

yuradex [85]

The correct answer is A. Familiar words for clues

Explanation:

Finding unfamiliar words is common while reading, especially in texts that belong to a specific field such as medicine, technology, etc. This can be handled through multiple strategies such as using a dictionary, guessing the meaning of the word based on its parts, and using context clues.

In this context, one of the easiest and most time-saving strategy is the use of context clues that implies using the familiar words as clues to guess the meaning of an unfamiliar word. This is effective because in most cases the meaning of an unknown word can be determined using the context of the word or words around the unknown word. Also, this strategy takes little time because you only need to analyze the sentence or paragraph where the unknown word is. Thus, the time-saving strategy to define unfamiliar words involves using familiar words for clues.

6 0

2 years ago

Read 2 more answers

A bunch of computer scientists take over an island and start their own country. They want the license plates to use binary numbe

Thepotemich [5.8K]

Answer:

Their country can support 128 unique license plates

Explanation:

Since there is space for 7 digits on each license plate, the first plate starts at 0000000 (seven 0).

Binary numbers contain only the digits 0 and 1.

Thus, there are only two possibilities for each digit.

Using the multiplication counting principle, the number of total different binary numbers, with seven digits is 2 multiplied seven times:

2 × 2 × 2 × 2 × 2 × 2 × 2 = 2⁷ = 128 ← answer

5 0

2 years ago

Marissa works at a company that makes perfume. She noticed many samples of the perfume were not passing inspection. She conducte

garik1379 [7]

Writing a business letter as if she gets her point across to the head of department then he could change the way they made the perfume so they would pass inspection and standard.

6 0

2 years ago

Read 2 more answers

Create an algorithm that will convert dog years to human years using the assumption that 1 dog year = 7 human years. Prompt the

matrenka [14]

Answer:

Algorithm:

1. Declare and initialize variable one_dog_year=7.

2.Ask user to give dog age.

2.1 Read the dog age and assign it to variable "dog_age".

3.Create a variable "human_age" to store the equivalent human age.

3.1 Calculate equivalent human age as "human_age=one_dog_year*dog_age".

4.Print the equivalent human age of dog age.

7. End the program.

// here is algorithm implemented in c++

#include <bits/stdc++.h>

using namespace std;

int main()

{

// initialize one dog year

int one_dog_year=7;

int dog_age;

int equi_h_age;

cout<<"Enter the dog age:";

// read the dog age

cin>>dog_age;

//calculate the equivalent human age

equi_h_age=dog_age*one_dog_year;

cout<<"equivalent human age is : "<<equi_h_age<<endl;

return 0;

}

Output:

Enter the dog age:2

equivalent human age is : 14

4 0

2 years ago

Describe two ways the ARPANET is different from the internet

spayn [35]

ARPANET was the network that became the basis for the Internet. Based on a concept first published in 1967, ARPANET was developed under the direction of the U.S. Advanced Research Projects Agency (ARPA). In 1969, the idea became a modest reality with the interconnection of four university computers. The initial purpose was to communicate with and share computer resources among mainly scientific users at the connected institutions. ARPANET took advantage of the new idea of sending information in small units called packets that could be routed on different paths and reconstructed at their destination. The development of the TCP/IP protocols in the 1970s made it possible to expand the size of the network, which now had become a network of networks, in an orderly way.

8 0

2 years ago