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