I'm going to second the above post, if it is not absolutely required of you to use excel, it is much easier to use math-ready software. I personally love matlab. If you've ever used any programming language, you should pick it up rather quickly. Even if not, it's still rather self explanatory on a lot of things, and for those that are not the help files are very good.
------------------------
I haven't used excel in a long time and don't have it on my computer, so if there's any errors I apologize. I don't know of excel having any ability to graph functions like that built in. Maybe there is, but I doubt it.
However, as far as computers go, anything you can do in some language or software you can do in a ton of other languages or software, you just may have to do a lot of the legwork that's already been done for you in the way of pre-defined functions.
If you wanted to use excel for graphing a function, I guess you could do the following:
cell A1: "wavelength" or "lambda"
cell A2: "energy density" or "function value"
Those are just going to be your labels for the graph.
now, about graphing. Let's use f(x) = x^2 as an example, graphed from x= -1 to x = 1.
Set cell A2 as "-1".
Set cell A3 as "=A2+.01"
the enter sign means you want the cell to do some sort of calculation, as opposed to display the text you enter. So, what displays in A3 should be: "-.99".
Now, make sure A3 is highlighted. there should be a little box in the bottom right corner, click it and drag it wayyyyyy down. All the way to cell A202. It should auto update the cell for you so that it always just adds .01 to the cell above it.
You should be looking at a list of numbers from -1 to 1, in increments of .01
Go back up to the top. Put cell B2 as "=A2*A2". Again, click and drag it down all the way to B202, using the little box in the corner. This should give you a list of values, such that the number in Bx is always the number in Ax squared. Now, you need to graph these. So highlight the block of cells with corners: A1, B1, A202, B202.
This is where my memory is a little fuzzy. So your going to have to play around with it, but this is the gist (make sure the cells are highlighted before you do this):
On the menu on the top,
insert > chart(or graph, not sure)
Tell it you want to make a line chart. Press ok a bunch of times. At some point it's going to give you a chart options page, where you can label the things like the x and y axis, set the scale, etc. Choose whatever fits your needs best, but you'll probably at least want to display grid lines to make it easier on yourself. Sorry, I wish I could be more helpful but, again, don't have excel on the computer.
Although, for problem 4, it would probably just be easier (and more accurate) to look at the data in your spreadsheet and find the values that are closest to what your looking for. Specifically, you could do this:
In cell C3, put "=B3-B2"
This is just going to be the change between the two points. Since B3 is to the right of B2 on the graph, if this number is positive, it is increasing, if it is negative, decreasing. Do the box-drag-down thing again to C202, and take a look at the data.
Find all the points where it changes from positive to negative. From your knowledge of derivatives, you should know there is a local maximum inbetween these two points. Conversely, negative to positive implies a local minimum.
If you followed the instructions above with the numbers I put in exactly, then the last negative cell should be C102, and the first positive cell should be C103. So you know the local minimum of f(x) = x^2 is located in the closed the values of A102 and A103. For f(x) = x^2, it's obviously 0. Which should be cell A102.
-------------------
As a side note, I doubt any of the constants are pre-defined in excel (even pi, although I could be mistaken), so you will have to enter them in manually.
-------------------
If you decided to go with matlab, you could have just done the following:
x = -1:.01:1;
f = x.^2;
plot(x,f), xlabel('x'), ylabel('f');
So I highly recommend learning it. You will not be unhappy with yourself in the long run. Once you get good at it you can also interface it with C programs and all sorts of fun stuff. Also, part 4 would be very easy once you have the graph window open. You wouldn't need to figure out the differences between points or any of that nonsense.
-----------------
The MATLAB code really does exactly the same thing.
The first line makes a matrix with values going from -1 to 1, with increments of .01.
The next line makes a matrix with values that are equal to the square of the values in correlating places in the matrix x.
The rest should be pretty obvious ;)
At any rate, it should be easy to see what's going on here. The software can't graph like we do, it just plugs in a ton of points and puts them down and tries to connect the dots as good as possible. That's why excel will give you essentially the same results as any other software.
This brings up something related: The smaller the intervals between points are, the more accurate your graph will be. For instance, if you graphed
sin(2pi*x) from x=1 to x=100
using the method i described above, but you set yourself to have points of distance 1 apart, your just going to get a straight line. You see where I'm going with this. The same is true with MATLAB or anything else like that. It doesn't know what sine is, it just plots a bunch of points. So if the graph doesn't look like what you think it should, don't trust the computer and change the parameters.
*gasp for air* lol
EDIT: changed " to ' in the MATLAB code. Got my syntax all mixed up :)
