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.
