Is anybody really good with Excel?

[Phaycops]

Sigh. So here I am trying to do my homework, and I'm slowly, well, not at all that slowly, realizing I have no freakin' clue what I'm supposed to do, or even how to start!

Here's the quote from my assignment sheet:

Quote:

Write an excel spreadsheet program to calculate the 1-D Ogata-Banks solutions for a) concentration vs. time, and b) concentration vs. distance.

So let me put it like this: I have no clue what this means. How does one write an excel spreadsheet program? How do I use an equation that's pretty much entirely variables in excel? Won't it get mad and give me horrible error messages like NAME?# ? Can anyone help me before I cry? Thanks!

[roger]

You can do computations in Excel using equations, allowing you to reference other cells.

You reference other cells by naming them: for example A3 specifies the cell in column A, row 3. So if you place the string "=A3 + B3" in a cell (minus the quotation marks), that cell will display the sum of cells A3 and B3.

So to build a spreadsheet that calculates the %15 tip on a bill, you may have the user type in the amount in A1, and place the formula "=A1 * 0.15" in B1 (to calculate the tip amount), and the formula "=A1 * 1.15" in B2 (to calculate the total amount to be paid).

In Excel, press the F1 key to get help, and search on equations. That will get you started. There's a lot more than what I described above.

[pgwenthold]

Quote:

Originally posted by Phaycops

So let me put it like this: I have no clue what this means. How does one write an excel spreadsheet program? How do I use an equation that's pretty much entirely variables in excel? Won't it get mad and give me horrible error messages like NAME?# ? Can anyone help me before I cry? Thanks!

Not at all. "Programming" in Excel basically means writing formulas that calculate a value based on what's in other cells. So for example, if you want to calculate A + B for a series of values of A and B, you basically put values for A in (for example) Column A, and the corresponding values for B in column B. Then in column C you write a function. So cell C1 might be something like

=A1+B1

Thus the value in C1 will be the sum of A1 and B1. Now if you copy C1 and paste into C2 and look at the cell contents, you will see that it is

=A2+B2

IOW, the row number adjusts automatically. Thus, you only need to put the function in one cell (I use the top one), highlight all the cells in the column that you want to put the formula in, and then go to Edit->Fill -> Down and it will automatically copy your formula.

You can do any math operation, and there are plenty of built-in formulas in excel (i.e. =sin(x) to get the sine, etc).

It's also possible to create little basic scripts that do systematic things, but you don't need that.

[Soapy Sam]

Phaycops- is this "homework" an exercise in programming, or is the use of a computer merely a means to an end?

If the former, then by "programming" Excel, I would suppose they mean using Visual Basic for Applications. In your Excel application, type ALT-F11 to bring up the VB editor. If this is wholly unfamiliar, (as I suspect is the case), then close it again and go back to pgwenthold's post. At least you'll know what the VB editor looks like next time you encounter it.

My strong reccommendation, if this is actually non-computer related work, is this;- if you know how to do the exercise with pencil and paper, do so. Far too much time is wasted trying to do things with computers which could be done faster with a calculator. Call me a luddite, but I personally believe the misuse of computers has cost business billions in wasted time.

[patnray]

I know Excel functions and VB for Applications fairly well. Let me know the formulas for "the 1-D Ogata-Banks solutions for a) concentration vs. time, and b) concentration vs. distance" and I guide you in developing the Excel formulas.

[Phaycops]

No, it doesn't have anything to do with VB, as far as I know. I talked with my TA, and pgwenthold seems to be correct. All I have to do is type in the variables in different columns, then graph one against the other. But what's happening now is I'm not getting the shape of curve I'm supposed to. But I think that's my fault. Thanks for the help, everyone. Oh, also, I found out you need to have a special toolpack enabled, and that's why I was getting weird error messages It would have been nice to have known that before I tried starting this. Yeesh.

[Soapy Sam]

Welcome to the wonderful world of Microsoft. Where would you like to go today?

Decreasing the interval between data points might change the curve shape.

[Wolverine]

Quote:

Originally posted by Phaycops
Oh, also, I found out you need to have a special toolpack enabled, and that's why I was getting weird error messages It would have been nice to have known that before I tried starting this. Yeesh.

Let me guess... Microsoft Equation Editor?

If so, been there, done that... but survived it.

[shanek]

Quote:

Originally posted by Phaycops
But what's happening now is I'm not getting the shape of curve I'm supposed to. But I think that's my fault.

Make sure you selected the right part for series. If you select rows instead of columns, or vice-versa, strange things happen. A lot of people make that mistake.

[Phaycops]

Quote:

Originally posted by shanek


Make sure you selected the right part for series. If you select rows instead of columns, or vice-versa, strange things happen. A lot of people make that mistake.

Nah, it's not that; I straightened that part out, though I don't know if I did it "legally," 'cause I sorta fudged some numbers.

No, now I need to know why the correction my TA gave me to calculate the complimentary error function of negative betas isn't working. D**n you, #NUM!

Anyway, sorry to keep at this topic, but this is frustrating to me. I think Soapy Sam is correct. This professor tends towards using computers for computers' sake a lot. I really want to just do it on my calculator, but I think that would be not the point of the assignment. Anyway, if anyone knows anything about making excel calculate erfc's for negative numbers, let me know. I'm off to bang my head against something. Maybe beer.

[Vorticity]

Phaycops:

Regarding the Ogata-Banks solution, it is a particular solution to a Partial Differential Equation known as the diffusive Berger equation ( http://cern57.ce.uiuc.edu/ce357/tran...One_D_1st.html ).

Are you supposed to take the analytical solution as given (with the error functions and so forth) and just plot it, or is part of the assignment to actually numerically compute solutions as a function of time and position, over some region in x-t space? If the latter, then its a wild coincidence, 'cause I just spent the whole day doing the exact same thing, writing a numerical scheme for the Berger's equation. I've got all the code right in front of me, and I know most of the standard numerical methods.
Is that the kind of info you need?

[Dymanic]

Can't help with the Ogata-Banks thing, but try this if you think Excel can't be fun:

1. In Exel 97, open a new blank worksheet.

2. Press "F5", and type "X97:L97" (no quotes) in the "Reference Box",
then click "OK".

3. Now hit your "tab" key once (you should end up in cell M97).

4. Press "Ctrl" and "Shift" while clicking once on the "Chart Wizard"
icon (the one at the top with the blue and red bar chart).

[shanek]

And people wonder why MS software is bloated...

----
So let me put it like this: I have no clue what this means. How does one write an excel spreadsheet program? How do I use an equation that's pretty much entirely variables in excel? Won't it get mad and give me horrible error messages like NAME?# ? Can anyone help me before I cry? Thanks!
----


Hey,

In Excel, you have a bunch of cells. Each cell is a point on the grid. So A2, is the second cell down in the A column. B6 is the 6th cell down in the B column. A2:B6 is the whole range of cells that you select when you drag your mouse from A2 to B6.

One can enter in data to these cells, and reference the data from other cells through formulas.

For example, if you type 10 in cell A1, then click on cell B1 and type
(the things after the numbers)

1. =
2. 2*A1

Then cell B1 will show 2*A1 = 2*10 = 20.

When you press =, you are actually telling Excel you want to type in a formula.

To make things faster, you can also, instead of typing in A1, you can just click on the A1 cell, and Excel fills in A1 into your formula.

So what you need, is a formula which calculates the 1-D Ogata-Banks solutions for a) concentration vs. time, and b) concentration vs. distance.

So you need to know:

1. the formula for actually calculating the 1-D OB solutions, and
2. you need to know what you need to vary, and what you need to keep constant for a) and b)

Here are some things to get you more than started, in general, but especially on b).

The Error Function function in Excel is ERF(lower_limit,upper_limit). In Excel, go to the help index, and type in ERF, to get more info on it. In general, Excel's help is excellent.

So, in b), you need to know 1. the formula for calculating the 1-D OB solutions, and 2. what needs to vary, and what needs to be kept constant.

Well, I assume you know the formula. As far as what to vary, we are seeing what happens when the distance increases, so we need a column of distances. Once we have this column, we evaluate our function over this entire column.

So for example, my distances start in cell A1 and go down column A.

So, say I click on cell B1 and type


=IF(($A1-V*T)/(2*(D*T)^0.5)<0,0.5*IC*(1+ERF(-($A1-V*T)/(2*(D*T)^0.5)+EXP(V*$A1/D)*ERFC(($A1+V*T)/(2*(D*T)^0.5)))),0.5*IC*(ERFC(($A1-V*T)/(2*(D*T)^0.5))+EXP(V*$A1/D)*ERFC(($A1+V*T)/(2*(D*T)^0.5))))


Where, $A1 is your column of distances. V is the cell that contains the velocity constant, T is the cell which contains the time in days (constant), D is the cells which contains the disperson (constant), and IC is the cell which contains the initial concentration (constant).

You'll need to figure out what the $'s mean, as well as the syntax for the IF function. You'll also have some $'s around some other things, in different places, because you don't want some of these things to change.

When you are done, you can plot column A and column B, and you can visually see what happens to the concentration as the distance increases.

a) is similar.

Does this help, especially for b)?

Quote:

Originally posted by Dymanic
Can't help with the Ogata-Banks thing, but try this if you think Excel can't be fun:

1. In Exel 97, open a new blank worksheet.
2. Press "F5", and type "X97:L97" (no quotes) in the "Reference Box",
then click "OK".
3. Now hit your "tab" key once (you should end up in cell M97).
4. Press "Ctrl" and "Shift" while clicking once on the "Chart Wizard"
icon (the one at the top with the blue and red bar chart).

Ah, nothing happens in versions greater than 97 apparently. What happens in Excel 97?

[boooeee]

Naked pictures of Bill Gates. In very unflattering lighting

[Phaycops]

Thank you all for your help, especially Vorticity and Whodini. I've gotten the hang of entering in multiple variables and so on, now I need to just think my way through calculating C/Co while varying different, uh, variables. It's times like this I really wish I could be doing some paleoclimatology. Oh, no, wait, no I don't. This is WAY more fun, let me tell you. That really says something about paleoclimatology, though, eh?

Vorticity, I pmed you!

[Aoidoi]

Quote:

Originally posted by boooeee
Naked pictures of Bill Gates. In very unflattering lighting

I don't think any lighting would be flattering. Or, perhaps more accurately, no light at all would be the most flattering.

IIRC there's a little DOOMesque game you can walk around and see pictures of the programmers. And yes, my first thought when I saw it was "now I know why these silly programs get bigger all the time."

Oh, and way to go Phaycops!

[Dymanic]

Ah, nothing happens in versions greater than 97 apparently.

I wondered about that. I just have 97. Might try replacing the 97's with whatever you have?

IIRC there's a little DOOMesque game you can walk around and see pictures of the programmers

Close. It's actually a flight simulator. If you cruise around, you can find a sort of monolith thing with scrolling credits including the names of the programmers.

[Soapy Sam]

Is this the Easter Egg that flies you around a moving list of programmer's names? I had that in Excel (95??), but lost the formula.

I once played with an Excel egg which let you drive around on a track and shoot other cars (the programmers' names were printed on the track). I guess this explains why Excel, particularly, is so bloated.

[Aoidoi]

Hmmm... the Easter Egg I remember had you walk around and if you could cross this little bridge thing there was a picture of the programmers. I didn't realize that it was a tradition to have something like this in each version.