# Excel - AVERAGE Function Problem - Expert Solution

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
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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.