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
Please continue
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
So we need to add-in your Bloomberg api first
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
to insert at your A1
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
Since, I've produce the average of your Col 2 already?
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
no In addition to
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.
