**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have used the average button under the autosum drop down to get averages. Now I need to take those averages, and divide each number by 6, and make a new chart but I cannot copy paste the averages because they are formulas not numbers. I get #REF when i try to paste the averages in a new cell.

Solved by S. Q. in 20 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
30/07/2018 - 06:33

Hi

Excelchat Expert
30/07/2018 - 06:34

Could you please provide more details about the problem

User
30/07/2018 - 06:35

I used the average button under the autosum drop down menu to get averages of data

User
30/07/2018 - 06:35

but now i cannot copy paste those averages into new cells so that i can do more with them, it is saying #REF

Excelchat Expert
30/07/2018 - 06:35

okay

Excelchat Expert
30/07/2018 - 06:36

you can use the formula generated using that option

User
30/07/2018 - 06:37

I don't understand your answer. How would I do that?

Excelchat Expert
30/07/2018 - 06:37

I was about to explain that

Excelchat Expert
30/07/2018 - 06:38

=AVERAGE(A1:A4)

Excelchat Expert
30/07/2018 - 06:38

When you use autosum something similar would have been created, right?

Excelchat Expert
30/07/2018 - 06:38

in the cell, if you look at formula bar

Excelchat Expert
30/07/2018 - 06:38

Were you able to follow?

User
30/07/2018 - 06:39

Yes thank you. Do I need to type that formula into each cell? I have 168 cells that I need to fill in with the 168 averages that I just created.

Excelchat Expert
30/07/2018 - 06:39

If you change the above formula to =AVERAGE($A$1:$A$4)

Excelchat Expert
30/07/2018 - 06:39

If possible, please share the data you are working on

User
30/07/2018 - 06:40

Sorry but there is sensitive information in this file so I cannot share it. What does adding the dollar signs do?

Excelchat Expert
30/07/2018 - 06:40

If you want to use the same average everywhere, you would have to copy that to those cells

Excelchat Expert
30/07/2018 - 06:41

Use of dollar sign creates something called fixed referencing.

Excelchat Expert
30/07/2018 - 06:41

It uses the same range of cells as in the formula, no matter where you paste that

Excelchat Expert
30/07/2018 - 06:42

But if you do't put $ signs, excel would change the cell ranges relative to the new cells

Excelchat Expert
30/07/2018 - 06:42

This is called relative referencing

Excelchat Expert
30/07/2018 - 06:42

Were you able to follow this?

Excelchat Expert
30/07/2018 - 06:43

Let me know if you have any doubts

User
30/07/2018 - 06:43

Sorry but Im getting confused. I can't share the file but I will try to send you a screenshot.

Excelchat Expert
30/07/2018 - 06:44

Sure

Excelchat Expert
30/07/2018 - 06:45

In the screenshot I put random vlues and then used autosum in the A5 cell

[Uploaded an Excel file]

Excelchat Expert
30/07/2018 - 06:46

Highlighted portion is the formula which excel generated for that cell

Excelchat Expert
30/07/2018 - 06:48

Now when I copy the A5 cell to C5 cell, the excel changed the underlying formula according to the new cell. This is called relative referencing

[Uploaded an Excel file]

Excelchat Expert
30/07/2018 - 06:48

Notice the difference in the formula

Excelchat Expert
30/07/2018 - 06:50

Now, when I chnaged A1 to $A$1 and A4 to $A$4 in the formula manually and then copied this new formula to C5 cell, the formula didn't change

[Uploaded an Excel file]

Excelchat Expert
30/07/2018 - 06:51

This is called fixed referencing. This tells the excel to use the earlier range of cells for evaluating the formula

Excelchat Expert
30/07/2018 - 06:51

So, fixed referencing would help solve your issue

Excelchat Expert
30/07/2018 - 06:51

Let me know, if this did not solve the issue or if you have any queries

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*