# Excel - AVERAGE Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need help converting an array to an average number in a single cell before the formula auto creates the table. 2 columns 5 rows
Solved by S. Q. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 21/09/2018 - 01:57
Welcome to Excelchat, I see that your question is about conevrting an array to a formula
User 21/09/2018 - 01:57
yes
Excelchat Expert 21/09/2018 - 01:57
Would you mine to share your file with us to create the solution for you?
User 21/09/2018 - 01:57
I'm using Bloomberg formula in a single cell that generates a table for data
User 21/09/2018 - 01:57
2 columns 5 rows
User 21/09/2018 - 01:57
I don;t have it on this computer
User 21/09/2018 - 01:58
but I can type the formu;a
User 21/09/2018 - 01:58
just ignore the BDH part but I'll show the output
Excelchat Expert 21/09/2018 - 01:58
Thanks for sharing that information.
Excelchat Expert 21/09/2018 - 01:59
We can create another suited formula without your BDH
Excelchat Expert 21/09/2018 - 01:59
depending on the data to share for demonstration or solution
User 21/09/2018 - 02:00
ok there is the formula
Excelchat Expert 21/09/2018 - 02:00
So, can you share some data at our document preview?
User 21/09/2018 - 02:00
and here is the output
Excelchat Expert 21/09/2018 - 02:01
Well, can you show us your formula once again?
Excelchat Expert 21/09/2018 - 02:01
It would also better if you can type them on our Document preview together with your sample data for the solution
User 21/09/2018 - 02:01
where
Excelchat Expert 21/09/2018 - 02:01
"Document Preview" shown at the right side of the screen
User 21/09/2018 - 02:01
I'm typing on the sheet
User 21/09/2018 - 02:02
yes on blank.xlsx
Excelchat Expert 21/09/2018 - 02:02
Ows, I can't view your data yet, may I know which cell have you type it? or the location?
Excelchat Expert 21/09/2018 - 02:05
Up there?
User 21/09/2018 - 02:05
how can I share it
Excelchat Expert 21/09/2018 - 02:05
May I know the location ?
Excelchat Expert 21/09/2018 - 02:06
You may also share with us the URL on your google sheet if you have?
Excelchat Expert 21/09/2018 - 02:06
Or can you type your sample data and formula at the blank sheet starting at A1 location for us to view and create the solution?
User 21/09/2018 - 02:07
ok
User 21/09/2018 - 02:07
I'll share
Excelchat Expert 21/09/2018 - 02:07
Thank you
User 21/09/2018 - 02:09
can you see test'
Excelchat Expert 21/09/2018 - 02:09
Yes,
User 21/09/2018 - 02:09
ok
Excelchat Expert 21/09/2018 - 02:09
User 21/09/2018 - 02:10
ok that's the API formula
User 21/09/2018 - 02:10
and here is the output
Excelchat Expert 21/09/2018 - 02:10
User 21/09/2018 - 02:11
I don't have it on this computer
User 21/09/2018 - 02:12
but is there a way to add another formula inside cell A1 to get the average of column two
Excelchat Expert 21/09/2018 - 02:12
yes,
User 21/09/2018 - 02:12
ok
Excelchat Expert 21/09/2018 - 02:13
First, I will show you on how to get the average of column 2
User 21/09/2018 - 02:14
ok
Excelchat Expert 21/09/2018 - 02:14
User 21/09/2018 - 02:14
ok this is 2 step process
Excelchat Expert 21/09/2018 - 02:14
The formula of getting the average is at B8 that you may insert on your A1
Excelchat Expert 21/09/2018 - 02:15
showing like this formula: =average(B2:B6)
Excelchat Expert 21/09/2018 - 02:15
By the way
Excelchat Expert 21/09/2018 - 02:15
Have you got about the averaging of column 2 now?
User 21/09/2018 - 02:16
cell A2 to B6 is the result of A1
User 21/09/2018 - 02:17
I don't want the output in cell A2:B6 but in A1
User 21/09/2018 - 02:17
Do you know how to do that?
Excelchat Expert 21/09/2018 - 02:17
By the way, you can do this application if you add-in the app Bloomberg on you excel
Excelchat Expert 21/09/2018 - 02:18
Can you tell me your expected output should be?
Excelchat Expert 21/09/2018 - 02:18
User 21/09/2018 - 02:18
the output in cell A1 should be 3.38
Excelchat Expert 21/09/2018 - 02:19
So it is just the same as shown at your B8
User 21/09/2018 - 02:19
in addition to the Bloomberg formula we need to another formula around it so it doesn't generate the
User 21/09/2018 - 02:19
the array but produces the average of the data in the 2nd column
Excelchat Expert 21/09/2018 - 02:20
So, you may want to use another formula instead of using BDH, right?
User 21/09/2018 - 02:20
User 21/09/2018 - 02:21
ok new plan
Excelchat Expert 21/09/2018 - 02:21
Okay,
Excelchat Expert 21/09/2018 - 02:21
Mean?
Excelchat Expert 21/09/2018 - 02:22
any changes?
User 21/09/2018 - 02:22
ok
User 21/09/2018 - 02:22
how do we get the average of cell A1 in cell A2
Excelchat Expert 21/09/2018 - 02:23
Alright,
Excelchat Expert 21/09/2018 - 02:24
This will be another plan or change of your data, so we need to get the average form one cell only, right
User 21/09/2018 - 02:24
well I might be able to get the data into a signle cell like this but I'll need to convert it into an average in cell A2
Excelchat Expert 21/09/2018 - 02:25
okay, before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User 21/09/2018 - 02:25
ok well the first question you couldn;t solve
Excelchat Expert 21/09/2018 - 02:26
So, to resolve this problem, we need to split your data at A1 first, then get the average of it
User 21/09/2018 - 02:26
forget it

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.