Is there anywhere on campus where i can get help with Excel?

9/14/2007 1:52:39 AM

what do you need to do in excel?

9/14/2007 2:05:31 AM

Excel is my little bitch.

I've used it to do such things as:

- Play the classic 'worm'/'snake' game
- DirectX graphics
- Scan a spreadsheet for links to images on the web and replace the references with downloaded copies of the image
- Solve the time-dependent neutron diffusion equation (finite differences)
- Solve the time-dependent neutron transport equation (one sheet defines spatial mesh, another is material properties, last generates plots of flux distribution)
- Database integration
- Statistical processes

If a computer can do it, I can do it using Excel.



What's the question?

9/14/2007 3:51:53 AM

^LOL

What is the question?

[Edited on September 14, 2007 at 11:32 PM. Reason : .]

9/14/2007 11:31:07 PM

oops

[Edited on September 14, 2007 at 11:31 PM. Reason : .]

9/14/2007 11:31:40 PM

rofl

9/16/2007 5:30:45 PM

enroll in E115

9/16/2007 6:02:24 PM

^Jep E115 dont teach you excel, csc200 does on the other hand though

9/17/2007 8:59:07 PM

^yes it does

9/17/2007 9:15:43 PM

^since when i never did a single thing in E115 dealing with excel, it was all unix based stuff terminal stuff(of course i did do the laptop section so we might have done different shit)

9/17/2007 11:59:45 PM

^well, i'm assuming it's been 3 years or so since you took it. i wouldn't imagine curriculum would change at all in that time frame.

9/18/2007 1:09:08 AM

Can any of you excel gurus add anything to this thread?

http://thewolfweb.com/message_topic.aspx?topic=494587

9/18/2007 10:30:34 AM

here's a stupid excel question that i haven't really figured out the answer to...

i like how sometimes you can copy/paste cells and the equations get updated in the new cells. that's a real time-saver. however sometimes i just want to move some cells around and keep the equations the same. how do you do that while keeping the equations the same?

for minor stuff it isn't that bad, but if i have an array equation with lots of if's and conditions, it can be a pain to update 30 different parameters per equation, for 30 or 40 different equations

9/18/2007 12:09:10 PM

To MOVE them without changing the cell references, highlight the cells you want to move and click and drag on the black selection border. You can only do this with simple selections though (i.i. you can't seect one region, then use CTRL click to select another region and move both at once)

9/18/2007 12:45:00 PM

I've got a question for you:

where can i find a reference online that gives me OLE automation functions used to create and update Excel files in Perl?

currently, I've hacked it using the Microsoft Excel Objects (designed for VBA) and trying to convert them to Perl as used by the Win32::OLE module

here's some random snippets of code to give you an example of what im doing. this is incomplete and won't run as it exists here.

Quote :

" use Win32::OLE qw(in valof with); use Win32::OLE::Const 'Microsoft Excel'; my $excelfile = $currentdir."\\wqm.xls"; my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); $Book = $Excel->Workbooks->Open($excelfile); $Sheet = $Book->Worksheets(1); $Sheet->{name}='Data'; $Sheet->Cells(1,32)->{Value} = "$serialnum-$devicenums[0]"; $Sheet->Cells(1,(@devicenums-1)*7+3)->{Value} = "S/N $serialnum"; $Sheet->Cells(1,(@devicenums-1)*7+3)->Font->{Bold} = 1; $Sheet->Cells((@devicenums-1)*8+5,28)->{Value} = "CalDate $oxcal"; $Sheet->Columns("A:A")->{NumberFormat} = "mm/dd/yy hh:mm;@"; $Sheet->Columns("AD:AD")->{NumberFormat} = "0.0000E+00"; $Sheet->Columns("C:Z")->{ColumnWidth} = "7.00"; $Sheet->Columns("E:F")->{ColumnWidth} = "5.29"; $Sheet->Rows("1:1")->{NumberFormat} = "@"; $chartRange = "A:A,H:H,O:O,V:V,X:Z"; $Chart = $Book->Charts->Add; $Chart->{ChartType} = xlXYScatter; $Chart->{Name}="Oxygen"; $Excel->ActiveChart->SetSourceData({Source => $Sheet->Range($chartRange)}); with($Chart, HasLegend => 1, HasTitle => 1); $Chart->ChartTitle->Characters->{Text} = "Oxygen"; $Chart->SeriesCollection(3)->Select; with($Chart->SeriesCollection(3), MarkerBackgroundColorIndex => 4, MarkerForegroundColorIndex => 4, MarkerSize => 2 ); with($Chart->Axes(xlCategory), MinimumScale => $daterangelo, MaximumScale => $daterangehi, MajorTickMark => xlOutside, MinorTickMark => xlOutside, TickLabelPosition => xlNextToAxis ); $Chart->Legend->Select; $Chart->Legend->LegendEntries(6)->Delete; $Chart->Legend->LegendEntries(5)->Delete; $Chart->PlotArea->Border->{LineStyle} = xlContinuous; $Chart->PlotArea->Border->{Color} = 0; $Chart->PlotArea->Interior->{Color} = 0xFFFFFF; $MovAvg[1] = $Chart->SeriesCollection(1)->Trendlines ->Add({Type => xlMovingAvg, Period => $PERIOD, Name => $TIMEAVG}); $MovAvg[1]->Border->{Color} = 0xA00000; if (!$LINEWEIGHT) { $MovAvg[1]->Border->{Weight} = xlThin; } "

basically, I'm taking thousands of lines of raw instrument data in HEX, and converting it to meaningful excel graphs, after a lot of conversions, and setting up the graphs in a particular consistent way (colors, axes, trendlines, etc)

what ive got works, but its a bitch to maintain especially if i have to add new elements... i have to hunt and poke around google and MSDN and create and dissect macros, etc. etc. And god forbid I ever have to pass this off to a tech or something.




[Edited on September 18, 2007 at 1:35 PM. Reason : ]

9/18/2007 1:33:46 PM

Quote :

"To MOVE them without changing the cell references, highlight the cells you want to move and click and drag on the black selection border. You can only do this with simple selections though (i.i. you can't seect one region, then use CTRL click to select another region and move both at once)"

Thanks. I think I meant to ask how do you duplicate a cell that contains an equation, without updating the references automatically.

9/18/2007 2:12:34 PM

The only way I know is through VBA.

9/18/2007 3:46:21 PM

oops

[Edited on September 18, 2007 at 4:08 PM. Reason : ]

9/18/2007 4:06:50 PM

for the ridiculous perl question, create a new chart style and set it as the default or apply it to the charts you create. half the code you posted is chart formatting, which can be done away with. alternatively, you could create a copy of a chart and just give it the new data.


oh, and learn how to do REAL statistics

[Edited on September 18, 2007 at 4:30 PM. Reason : ]

9/18/2007 4:29:03 PM

its a huge amount of data, multiple instruments, and with multiple charts in one workbook. like about 10 different charts. then new workbooks are made every week or two.

the Perl code does make a template, of sorts, via subroutines. i just put extracted lines in there as examples of code snippets.

i guess im just looking for a compilation of OLE commands for Perl.

i probably shoulda done it all in VBA anyhow, but i have an aversion to learning Visual Basic, since ive avoided it for so long.

9/18/2007 5:21:57 PM

How hard would it be to learn VBA?

9/18/2007 5:54:43 PM

Quote :

"I've used it to do such things as: - Play the classic 'worm'/'snake' game"

Huh?

Explain please! And how as well, so I can do it too.

9/18/2007 7:17:07 PM

Quote :

"its a huge amount of data, multiple instruments, and with multiple charts in one workbook. like about 10 different charts. then new workbooks are made every week or two. the Perl code does make a template, of sorts, via subroutines. i just put extracted lines in there as examples of code snippets. i guess im just looking for a compilation of OLE commands for Perl. i probably shoulda done it all in VBA anyhow, but i have an aversion to learning Visual Basic, since ive avoided it for so long."

You're using the Excel Object Library. The objects and structure are exactly the same in VBA. The only thing that changes is the syntax of the language. A chart template will eliminate the need to format the charts individually, which can make your code run faster, as well as look simpler and be easier to maintain.

The only code you posted is chart-related, so that's all I can comment on. From your description, it sounds like something that can be done in Excel without any automation. You can probably accomplish everything by setting up a workbook template and creating new workbooks from the template and importing your new data.

Quote :

"How hard would it be to learn VBA?"

It's really not that hard at all. The syntax is pretty simple.

Quote :

" Huh? Explain please! And how as well, so I can do it too."

VBA code + cells made into a square grid + cell borders for walls + a little bit of imagination. It was really easy to code up.

9/18/2007 9:28:15 PM

Quote :

"Thanks. I think I meant to ask how do you duplicate a cell that contains an equation, without updating the references automatically. "

the best way i've found is to highlight the text and copy and paste the text rather than just copy/paste cells. tis still aggravating.

9/26/2007 7:19:10 PM

If you have dollar sign in front of the number if you don't want it to change or a dollar sign in front of the letter if you don't want it to change.

For example, =A1+$B$1 and you were filling down in column C, it would keep updating A2, A3, A4, etc but it would stay B1.

9/27/2007 4:31:04 PM

he was talking about reproducing a range identically. while you CAN convert all your references to absolute references then copy/paste, that's probably not the solution he's looking for. it's pretty easy to write a VBA macro though and bind it to a key.

9/27/2007 5:08:54 PM

What's a good resource to learn how to use various conditional logic in each cell?

10/1/2007 3:31:32 PM

Ok, let me be a little more specific, I had been using java to read copied data from a text file, but that is getting annoying. I want to know the easiest way to do something like this for a whole columns of cells

IF (cellA = valueA or cellA = valueB or cellA=valueC)

AND

IF Date stored in cellB falls after date stored in cellC and before data stored in cellD

AND

IF(cellE <= (cellF - (cellG + cellH + …. + cellL)))
Then cellL = cellL + cellE

ELSE IF (cellE > (cellF - (cellG + cellH + …. + cellL)))
Then cellL = cellL + (cellF - (cellG + cellH + …. + cellL)))
And cell M = cellM + cellE – (cellF - (cellG + cellH + …. + cellL)))

10/1/2007 4:16:46 PM

unfortunately it looks like this:

IF(AND(OR(cellA='valueA',cellB='valueB',cellC='valueC'),celle<=...),VALUE_IF_TRUE,VALUE_IF_FALSE[or another IF for elseif])

It gets pretty complicated. I would just go ahead and use java or VBA.

10/1/2007 5:01:56 PM

lame

How deep can I nest stuff?
How can I cause a calculation in one cell to put data down in other cells?
How do I greater than and less than comparisons of dates?
How easy is VBA to learn?

10/1/2007 5:34:40 PM

you can use <, >, <=, >= and = on dates. a date that has no time associated with it (like 12/01/2007) is considered at midnight at the start of the day (like 12/01/2007 12:00:00 AM)

you can nest 255 levels deep, but I'm pretty sure there's a maximum number of characters allowed in a cell that you will reach first.

no, the built-in functions don't allow you to modify other cells based on the result of the current cell, so you'd have to put similar logic in all the cells you want values in (yes it's a waste of CPU power).

you can create functions in VBA that affect other cells

the syntax for VBA is identical to visual basic 6 (newer versions of office have an extension for .NET programmability support). anything that can be done in VB can be done in VBA. any function of excel (or any other office application) can be called using VBA. the best thing you can do is get yourself an object reference.

10/1/2007 10:16:48 PM

How easy is visual basic to learn?

10/1/2007 10:29:15 PM

that's a subjective question. if you're not a fucking idiot, not very hard. hence visual BASIC.

10/2/2007 4:46:07 PM

I already know java. Will it be worth my time to learn vb just for one project?

10/2/2007 5:25:06 PM

Process the data with java and output it to a comma separated file. Import with Excel.

Profit.

10/2/2007 7:01:31 PM

I didn't think a csv file could have formulas and formatting?

10/2/2007 7:20:20 PM

Quote :

"you can nest 255 levels deep, but I'm pretty sure there's a maximum number of characters allowed in a cell that you will reach first."

I think it's 1024 characters, if i remember correctly. I don't know about the nesting for conditionals, but I think there is a limit of 7 or 8 nested IF's, at least in o2k3

10/2/2007 7:26:16 PM

seems more manageable if you segment it, say in a few cells ona background sheet

10/2/2007 7:30:31 PM

no CSV files can not have formatting or formulae. what i meant was for you to process the data in Java, then import the data in excel and format it. if you want to process the data within excel, it will probably require VBA

10/3/2007 1:10:00 PM

I'm making the guy copy of the data to text file, reading it in from there, and then reading several specific columns out to another text file he can copy and paste in excel.

10/3/2007 1:20:01 PM

This is totally unrelated and probably very easy question. If I have a series of payments that occur far out in the future and don't connect to the present, how can I convert them to a current sum in excel?

For example if I make 5 payments at times 6-10, is there an easy way I can convert this to a current sum at time 0?

10/3/2007 9:56:46 PM

you can nest all those financial commands

10/3/2007 10:01:36 PM

Yeah, but my problem is I can convert all the uniform payments to time 5 PV(rate,nper,pmt) but then there seems to be no function to take the future value to a present worth.

10/3/2007 10:05:22 PM

isnt that what PV is?

taking a FV to PV?

10/3/2007 10:08:23 PM

PV:
Present value of an investment. The present value is the total amount that a series of future payments is worth now.



Thats from the excel help file. That to me means a series of payments to present worth. FV is the same thing but future value. There doesn't seem to be any F->P or P->F without having a series uniform payments in between.

10/3/2007 10:13:56 PM

Quote :

"FV(rate,nper,pmt,pv,type)"

you dont have to fill in all the fields

just do a =FV(rate,nper,,pv,type)

10/3/2007 10:19:10 PM

Thanks. I knew it was stupid simple. I appreciate it.



Edit: Damnit. It turns out I didn't even need it. Intelligent goal seeking ftw.

[Edited on October 3, 2007 at 10:58 PM. Reason : goal seek]

10/3/2007 10:39:42 PM