Thread: Logarithms in Excel

I need to use excel to find a logarithm equation for my project.

the question is

d. Use Excel to fit an exponential equation, base e, to the entire data set of CPI numbers, using years since 1913 as your x value. Write the equation that you found. Also, show the graph that Excel produced.

i was able to make the graph, but i can't figure out where i would find the equation or how i would make the equation. if anyone can help it would be greatly appreciated.

[FONT=Times New Roman][SIZE=3]the set of data is at the following link which should open in excel, its not letting me post the table in here without messing up the order for some reason. http://brookdalecc.edu/MSDocs/MATH%2...data%20new.xls

Originally Posted by JGaraffa

I need to use excel to find a logarithm equation for my project.

the question is

d. Use Excel to fit an exponential equation, base e, to the entire data set of CPI numbers, using years since 1913 as your x value. Write the equation that you found. Also, show the graph that Excel produced.

i was able to make the graph, but i can't figure out where i would find the equation or how i would make the equation. if anyone can help it would be greatly appreciated.

[FONT=Times New Roman][SIZE=3]the set of data is at the following link which should open in excel, its not letting me post the table in here without messing up the order for some reason. http://brookdalecc.edu/MSDocs/MATH%2...data%20new.xls

To Plot a trendline in excel right click on a point in your graph and go to add trendline. If you've plotted time against ln(CPI) then pick a linear line. You should also be able to tick a box which says show equation and show R^2 value

i appreciate your quick response. unfortunately i have zero experience in excel. could you maybe put that in child-like terms hahaha i really don't get logarithms at all so its hard for me to understand.

Originally Posted by JGaraffa

i appreciate your quick response. unfortunately i have zero experience in excel. could you maybe put that in child-like terms hahaha i really don't get logarithms at all so its hard for me to understand.

When I was doing it I made an extra column with ln(CPI) in cell D1. In Cell D2 put in

Code:

=ln(C2)

then you can drag the cell down via that small button in the bottom right of the selected cell and drag it down to the bottom.

To plot the graph highlight your x values and pressing down control select the values in column D and plot. I'm not on windows at the moment so I can't go through a full method

thanks for your help. i was able to make the graph.

theres some more questions i don't really understand so i figure ill just put them on here if your able to help with them

please don't think im trying to get you to do my project, i just really don't understand it and could use a lot of guidance. my professor is pretty much a ghost outside of class and never really helpful in class.


a.) An R2 value close to 1 indicated a good fit. What was the value for R2, and was it close to 1?

b.) Based on the fact that CPI is a measure of inflation, is an exponential function a good fit for this data? Explain how you know.

c.) Is the exponent positive or negative? Explain what this means about what is happening to purchasing power.
d.) Use the function to predict the CPI in 2010. Show your work.
e.) When will the CPI be half what it was in 1983? Set up an equation and show your work.
f.) The economy of the United States has changed a lot since 1913, and it has had ups and downs. Pick a period of time in which the history and economy of the United States was more stable or less stable. For example, you may pick the end of World War II or the years around the Stock Market crash. Recalculate the whole exponential model for just that period of time. This means you are getting a new graph and new exponential equation for a smaller subset of the data. Write the new equation and R2 value.
g.) Explain which year you chose and why.
h.) Did your second model fit better or worse? How do you know?


1. We will now examine what happens when we take the logarithm of the Consumer Price Index data.
a.) Open the entire data file from the previous question. Create a new column called ln purchasing power. (These instructions are adapted from the Journal of Online Mathematics and its Applications.):
· In the first empty cell under the new column heading, write = ln (C2). (The = symbol is used for any calculation in Excel.) You should see the result 2.29…., the natural logarithm of 9.900 (the number in cell C2). The formula appears at the top of the worksheet, next to the = sign.
· Copy the formula down the page: Click on cell D2. Move the pointer to the lower right corner, so that a cross appears in place of the black box in the lower right corner of Figure 8. Drag the pointer down the column. The natural logarithm is then calculated for cells D2, D3, and so on. The result should have the result of the natural log extended all the way to the last year in the table.

Copy the first 6 rows of your new table, to show that you have correctly used Excel to take the natural log.

b.) Fit a linear equation to all the new data (not just the first 6 rows), and write the results. You should be comparing years since 1913 with column D, the ln of the purchasing price.

c.) How is the linear equation you just got using the natural logarithm of the data related to the exponential curve you found in the previous question? Answer the following question and use the properties of logarithms to find out.

Write the exponential equation from the previous question and take the ln of this equation.

Caution: use the properties of logs to write the ln of two multiplied numbers as two separate logs. Similarly, use the properties of logs to properly rewrite the ln of e to a power.

d.) You should find that the linear equation you just got using the natural logarithm of the data is related to the exponential curve you found in the previous question. How are the two related? Your answer can be one short sentence.

I think seeing your prof or reading up a bit on excel might help

Originally Posted by JGaraffa

a.) An R2 value close to 1 indicated a good fit. What was the value for R2, and was it close to 1?

b.) Based on the fact that CPI is a measure of inflation, is an exponential function a good fit for this data? Explain how you know.

c.) Is the exponent positive or negative? Explain what this means about what is happening to purchasing power.

a) The R² value is a measure of deviation between the data and the equation and is gotten from excel, it is under the box where it says show equation. The closer to 1 the tighter the fit. A value of R²=1 is a perfect fit. Anything above 0.9 is considered a good fit

b) The graph seems to indicate so because there is a rapid growth in short time

c) Look up exponential growth . The conclusions should be clear

d.) Use the function to predict the CPI in 2010. Show your work.
e.) When will the CPI be half what it was in 1983? Set up an equation and show your work.

d) Set t as the appropriate value and use the equation to find f(t)

e) Set f(t) (or y as excel will tell you) equal to half the value of 1983 and solve for t

f.) The economy of the United States has changed a lot since 1913, and it has had ups and downs. Pick a period of time in which the history and economy of the United States was more stable or less stable. For example, you may pick the end of World War II or the years around the Stock Market crash. Recalculate the whole exponential model for just that period of time. This means you are getting a new graph and new exponential equation for a smaller subset of the data. Write the new equation and R2 value.
g.) Explain which year you chose and why.
h.) Did your second model fit better or worse? How do you know?

f) Do much the same as the original task just over a smaller domain

g) Pick one and explain why

h) Check the R² value - the one closest to 1 is the best fit

c.) How is the linear equation you just got using the natural logarithm of the data related to the exponential curve you found in the previous question? Answer the following question and use the properties of logarithms to find out.

Write the exponential equation from the previous question and take the ln of this equation.

Caution: use the properties of logs to write the ln of two multiplied numbers as two separate logs. Similarly, use the properties of logs to properly rewrite the ln of e to a power.

The linear equation corresponds to the exponential before.

if then