Forum Index Register Members List Events Mark Forums Read Help

 JREF Forum Is anybody really good with Excel?

 Welcome to the JREF Forum, where we discuss skepticism, critical thinking, the paranormal and science in a friendly but lively way. You are currently viewing the forum as a guest, which means you are missing out on discussing matters that are of interest to you. Please consider registering so you can gain full use of the forum features and interact with other Members. Registration is simple, fast and free! Click here to register today.

 Tags microsoft excel help

 10th February 2003, 12:58 PM #1 Phaycops Guest   Join Date: Nov 2002 Location: Upstate NY Posts: 517 Is anybody really good with Excel? 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!
 10th February 2003, 01:17 PM #2 roger Penultimate Amazing     Join Date: May 2002 Location: Mountain View, CA Posts: 11,021 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.
 10th February 2003, 01:18 PM #3 pgwenthold Penultimate Amazing   Join Date: Sep 2001 Posts: 12,071 Re: Is anybody really good with Excel? 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. __________________ "Baseball is a philosophy. The primordial ooze that once ruled our world has been captured in perpetual motion. Baseball is the moment. Its ever changing patterns are hypnotizing yet invigorating. Baseball is an art form. Classic and at the same time...progressive. Baseball is pre-historic and post-modern. Baseball is here to stay." (Stolen from the side of a lava lamp box, and modified slightly)
 10th February 2003, 01:38 PM #4 Soapy Sam NLH   Join Date: Oct 2002 Posts: 25,885 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.
 11th February 2003, 10:53 AM #5 patnray Graduate Poster   Join Date: Mar 2002 Location: San Jose, CA Posts: 1,008 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.
 11th February 2003, 12:05 PM #6 Phaycops Guest   Join Date: Nov 2002 Location: Upstate NY Posts: 517 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.
 11th February 2003, 12:45 PM #7 Soapy Sam NLH   Join Date: Oct 2002 Posts: 25,885 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.
 11th February 2003, 01:03 PM #8 Wolverine Grumpy Stinky Mustelid     Join Date: Jun 2002 Location: Austin, TX Posts: 1,690 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.
 11th February 2003, 02:59 PM #9 shanek by Charles M. Schulz     Join Date: Aug 2001 Posts: 15,990 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. __________________ "James Randi is awesome!" —Ian Bernard, primary host of Free Talk Live "It really does take people like Penn & Teller or James Randi to be able to see through these deceptions, and so those are perhaps the people we should be paying the most attention to." —Harry Browne, 4/10/2004 I know there is a lesson to be learned here somewhere, but I don't know what it is.
 11th February 2003, 05:43 PM #10 Phaycops Guest   Join Date: Nov 2002 Location: Upstate NY Posts: 517 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.
 11th February 2003, 08:19 PM #11 Vorticity Fluid Mechanic     Join Date: Apr 2002 Location: Los Alamos, NM Posts: 2,651 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? __________________ Free lunch. Final wisdom. Total coverage. http://stopsylvia.com
 12th February 2003, 09:11 AM #12 Dymanic Illuminator   Join Date: Sep 2002 Posts: 4,735 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).
 12th February 2003, 09:15 AM #13 shanek by Charles M. Schulz     Join Date: Aug 2001 Posts: 15,990 And people wonder why MS software is bloated... __________________ "James Randi is awesome!" —Ian Bernard, primary host of Free Talk Live "It really does take people like Penn & Teller or James Randi to be able to see through these deceptions, and so those are perhaps the people we should be paying the most attention to." —Harry Browne, 4/10/2004 I know there is a lesson to be learned here somewhere, but I don't know what it is.
 12th February 2003, 11:38 AM #14 Guest   Posts: n/a Re: Is anybody really good with Excel? ---- 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)?
 12th February 2003, 11:41 AM #15 Guest   Posts: n/a 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?
 12th February 2003, 12:29 PM #16 boooeee Dart Fener     Join Date: Aug 2002 Location: The Lando System Posts: 2,396 Naked pictures of Bill Gates. In very unflattering lighting
 12th February 2003, 01:05 PM #17 Phaycops Guest   Join Date: Nov 2002 Location: Upstate NY Posts: 517 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!
 12th February 2003, 03:33 PM #18 Aoidoi Illuminator   Join Date: May 2002 Posts: 4,418 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! __________________ "The priests used to say that faith can move mountains, and nobody believed them. Today the scientists say that they can level mountains, and nobody doubts them." - Joseph Campbell We cannot defend freedom abroad by abandoning it at home. —Edward R. Murrow
 12th February 2003, 03:45 PM #19 Dymanic Illuminator   Join Date: Sep 2002 Posts: 4,735 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.
 13th February 2003, 08:53 AM #20 Soapy Sam NLH   Join Date: Oct 2002 Posts: 25,885 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.
 13th February 2003, 01:05 PM #21 rwald Guest   Posts: n/a 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.
 13th February 2003, 01:13 PM #22 Aoidoi Illuminator   Join Date: May 2002 Posts: 4,418 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. __________________ "The priests used to say that faith can move mountains, and nobody believed them. Today the scientists say that they can level mountains, and nobody doubts them." - Joseph Campbell We cannot defend freedom abroad by abandoning it at home. —Edward R. Murrow

JREF Forum

 Bookmarks Digg del.icio.us StumbleUpon Google Reddit