# Excel - COLUMN Function Problem - Expert Solution

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

I need a formula to convert the numbers in column A and column B into values to get a linear relationship
Solved by V. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 15/09/2018 - 10:41
Welcome to Excelchat, I see that your question is about converting text to values
User 15/09/2018 - 10:41
yes
Excelchat Expert 15/09/2018 - 10:41
Excelchat Expert 15/09/2018 - 10:42
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 15/09/2018 - 10:42
thanks
Excelchat Expert 15/09/2018 - 10:43
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert 15/09/2018 - 10:43
Do you have a sheet to upload or we will use the view screen?
User 15/09/2018 - 10:43
how do I convert the temperature and rate constant data into values that will give me a linear relationship
Excelchat Expert 15/09/2018 - 10:43
User 15/09/2018 - 10:43
I have ended some data into the right hand screen
User 15/09/2018 - 10:44
entered
Excelchat Expert 15/09/2018 - 10:44
I am not sure ?
Excelchat Expert 15/09/2018 - 10:45
for every increase in K you expect what?
User 15/09/2018 - 10:45
I know I have to use a formula but what one?
User 15/09/2018 - 10:46
for temperature increase you expect rate constant increase
User 15/09/2018 - 10:46
all I need to know is what formula I should be using
User 15/09/2018 - 10:46
on excel
Excelchat Expert 15/09/2018 - 10:47
You would have to develop the formula from data , if not a standard exisitng formula
User 15/09/2018 - 10:47
I have the formula ln(k)= - (Ea/R) (1/T) + ln(A)
User 15/09/2018 - 10:47
but idk where that is on excel to do this conversion
User 15/09/2018 - 10:47
but I don't know where that is on excel to do this conversion
Excelchat Expert 15/09/2018 - 10:47
We can start by defining the variables
Excelchat Expert 15/09/2018 - 10:48
In K , is the inverse of K (column A) here?
User 15/09/2018 - 10:48
under the formals tab on excel there are many many formulas available, I'm just not sure which one it is that I should be using??
Excelchat Expert 15/09/2018 - 10:49
Yes, but we need to know what the variables represent in your formula - What is Ea/R ?
User 15/09/2018 - 10:50
A is the frequency factor
User 15/09/2018 - 10:50
which is "k"
Excelchat Expert 15/09/2018 - 10:50
Ok, then that needs to be in excel
User 15/09/2018 - 10:51
Ea is the energy of activation (units of kJ/mol)
User 15/09/2018 - 10:51
R is the thermodynamic gas constant
Excelchat Expert 15/09/2018 - 10:51
we need those variables defined in excel
Excelchat Expert 15/09/2018 - 10:52
EXCEL formulas rely on cell data , regardless of type
User 15/09/2018 - 10:53
I have to convert the temperature and rate constant (shows to the right on the spread sheet) into values that will give me a linear equation (which is the equation I gave above)
User 15/09/2018 - 10:53
I just don't know where that equation can be found on EXCEL
Excelchat Expert 15/09/2018 - 10:55
The only possibility of a defined formula of this type in EXCEL 2016, would be under FORMULAS, MATH&TRIG
Excelchat Expert 15/09/2018 - 10:55
But I dont believe there is a defined formula set up for this type
User 15/09/2018 - 10:55
I'm asking what formula I use for that?
User 15/09/2018 - 10:56
I'm told there is a formula for that?
Excelchat Expert 15/09/2018 - 10:56
Yes, you have one but , need to find cell values to create in EXCEL, if there are defined constants, need those in EXCEL
Excelchat Expert 15/09/2018 - 10:57
Did you check MORE FUNCTIONS, ENGINEERING?
User 15/09/2018 - 10:58
they don't show me the equation or I would have looked for it.. it just says a word and doesn't tell you the actual equation being used
Excelchat Expert 15/09/2018 - 11:00
If you can not enter the values and constants in EXCEL there is no way EXCEL can do this
Excelchat Expert 15/09/2018 - 11:00
There are defined formulas, but if you dont know the name , no way to select.
Excelchat Expert 15/09/2018 - 11:01
I am looking at resources in the archives, but not coming up with any simple answers, but will keep lookiing
User 15/09/2018 - 11:03
I have the values on the right
User 15/09/2018 - 11:03
the formula is for rate constant that has been mathematically changed to show the linear relationship of the rate constant formula
Excelchat Expert 15/09/2018 - 11:04
Excelchat Expert 15/09/2018 - 11:04
all we need to do is plug values from col A and B into the formula
Excelchat Expert 15/09/2018 - 11:05
so which columun = Ea/R?
Excelchat Expert 15/09/2018 - 11:05
Which has T?
Excelchat Expert 15/09/2018 - 11:05
Where do we find A? what cell?
User 15/09/2018 - 11:06
T is col A
User 15/09/2018 - 11:06
R is 8.31x10^-3
User 15/09/2018 - 11:07
k is col B
Excelchat Expert 15/09/2018 - 11:10
Ok, if I got this right..
Excelchat Expert 15/09/2018 - 11:11
take the constant R times the inverse of K , plus rate constant
Excelchat Expert 15/09/2018 - 11:12
Not sure about plus or times the rate constant?
Excelchat Expert 15/09/2018 - 11:13
Wait is this Ln(A) -meaning a log function?
User 15/09/2018 - 11:14
yeah
Excelchat Expert 15/09/2018 - 11:15
Ok, cells and formula is filled in
User 15/09/2018 - 11:15
I'm not sure the value of Ea though
Excelchat Expert 15/09/2018 - 11:15
You can justr copy down
User 15/09/2018 - 11:16
it just tells me Ea is the energy activation (units of kJ/mol)
Excelchat Expert 15/09/2018 - 11:17
I can only help with EXCEL
User 15/09/2018 - 11:19
so is there a formula to use on excel that will do the calculation for me or do I have to do it manually?
Excelchat Expert 15/09/2018 - 11:20
I will check again, but I think this is manual,
User 15/09/2018 - 11:23
thank you
Excelchat Expert 15/09/2018 - 11:24
Yes, you will need the formula inputs and any defined constants, like above and calc manually for this type of equation
User 15/09/2018 - 11:25
I'm told there is a way for EXCEL to do it but okay, thank you
Excelchat Expert 15/09/2018 - 11:26
It can do it , but you need the input values for a defined formula
User 15/09/2018 - 11:26
where do you go to do that?
User 15/09/2018 - 11:26
I'm told to use "EXCEL functions"
Excelchat Expert 15/09/2018 - 11:26
If the formula is not defined you can use graph tools or regression
Excelchat Expert 15/09/2018 - 11:26
This is an EXCEL function
User 15/09/2018 - 11:26
where's that?
Excelchat Expert 15/09/2018 - 11:27
=(\$G\$1) *(1/A2)+log(B2,10)
User 15/09/2018 - 11:27
what's that^?
Excelchat Expert 15/09/2018 - 11:28
That is the formula you gave me with the inputs in column A and B , using a constant in G1
User 15/09/2018 - 11:28
where do I go to find where to put in numbers to have EXCEL do the calculation?
Excelchat Expert 15/09/2018 - 11:29
Excelchat Expert 15/09/2018 - 11:29
Not sure?
User 15/09/2018 - 11:29
you said EXCEL can do calculations if I enter in the numbers.. where is that?
Excelchat Expert 15/09/2018 - 11:29
Where did you get column A , B and this constant ==(\$G\$1) *(1/A2)+log(B2,10)
Excelchat Expert 15/09/2018 - 11:30
sorry this constant==8.31*(10^-3)
User 15/09/2018 - 11:30
on a piece of paper my teacher gave me
Excelchat Expert 15/09/2018 - 11:30
You enter numbers in column A and B
Excelchat Expert 15/09/2018 - 11:30
The formula is in column C
User 15/09/2018 - 11:30
yes from the chart I was given
Excelchat Expert 15/09/2018 - 11:31
The constant is in cell g;1
User 15/09/2018 - 11:31
I don't know where to go on EXCEL to enter in my numbers and have EXCEL do the calculation for me rather than doing it my self manually? you said EXCEL can do the calculation if I enter in the numbers?
Excelchat Expert 15/09/2018 - 11:32
Enter in the numbers in column A and B , as we have in the view screen
User 15/09/2018 - 11:32
and plug them into the equation. I know that but you said EXCEL can do the calculation for me. HOW?
Excelchat Expert 15/09/2018 - 11:32
Copy the formula in column c for every row
Excelchat Expert 15/09/2018 - 11:33
Look at column C
Excelchat Expert 15/09/2018 - 11:34
It uses a constrant R in cell G;1 times the inverse of column A entry , plus the LOG (10) value of the entry in column B.
Excelchat Expert 15/09/2018 - 11:35
Is the formula in E correct and we have the inputs defined then answer is in columN C
Excelchat Expert 15/09/2018 - 11:37
I would question if we really have Ea/R, I think we just have R
User 15/09/2018 - 11:39
we don't have Ea because I'm not sure what that is? My teacher didn't say
Excelchat Expert 15/09/2018 - 11:39
Are you supposed to solve for EA?

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.