Any Excel experts around?

[alarimer]

I have a huge array of data that I am trying to do something with. I am useless at pivot tables- they never work for me.
I have 4146 samples. Column A is the sample date in the format ddmmyyyy (all in one). Columns B through AS are species. The numbers in the cells are the number of individuals caught in that sample. What I am trying to do is divide all the raw catch data into catch-per-unit effort by dividing each cell by .03. What is the easiest way to do that? Maybe a macro? (which I don't really know how to do). Once I get that converted I need to average the catch-per-unit by year, using the sample date. I have already sorted by the date so they are in ascending order by year. This is all in order to display annual catch per unit effort graphically.

[havocmom]

1. Who is Amy Excel?

;)

[alarimer]

2. Oops!

Spellcheck is my friend.

[havocmom]

3. if you don't get any help here, post in the computer forum too

moves slower and there are people here who really are a help. Wish I could help you, but I barely have a nodding acquaintance with Excel.

[alarimer]

4. I can't post there

not a current donor and can't really do it right now.

[Nickster]

5. Check your PM. n/t

[havocmom]

6. alarimer, did you post in computer forum? You can now

Some nice person (possibly the one who advised you to check your PMs?) made sure your star is shining.

Gotta love DUers

[alarimer]

7. Well I haven't yet

I think I may have found a way to do what I want.

But I sure appreciate whoever did that.

[mike_c]

8. did you get an answer? Here's one way to do it....

1) Assume your data are on worksheet #1

2) Open a second worksheet and create a matrix of cells with 0.3 in each cell-- the matrix should be exactly the same size as the one in worksheet 1. The easy way to do this is to copy the data matrix from worksheet #1 to worksheet #2 and then search-and-replace everything with 0.3 (control-h).

3) Open an third worksheet and enter the formula =Sheet1!A1/Sheet2!A1 into the first cell. Substitue your actual first cell if it isn't A1. Enter. Voila! The first data element divided by 0.3. Now just copy that cell and paste to the rest of your matrix on worksheet #3.


That said, why are you trying to manipulate data in Excel?! Get thee to the R-project site: http://www.r-project.org/

In R, what you're trying to do is as simple as the command: data_matrix/0.3

Hope this helps.

[alarimer]

9. thanks

As for R, too steep a learning curve for me. I just can't figure it out. I read all the manuals but all I do is get frustrated. I can't even get the date into R in order to use it. So I have pretty much given up on that. And it's a shame because R does things graphically that look better than anything produced with excel.

[thefool_wa]

13. This is the long way around

The same thing can be accomplished by a simple formula and only 1 other table.

[mike_c]

15. yeah-- an even easier solution just occured to me....

Put 0.3 in a cell somewhere else, say Z500. Then for each cell in the data matrix just do =A1/$Z$500.

[thefool_wa]

16. that would work too

but you can just use the integer - there is no need to assign it to a cell


SHEET2 - Cell B2
='sheet1'!b2/.03


and then just copy and paste in the table on sheet 2.

If you planned on changing the integer - I would put it in a cell, but otherwise just use the integer.

[Poiuyt]

10. Excel tips and tutorials

http://www.personal-computer-tutor.com/TOC.htm

I don't know if your answer is in there, but there are some neat things.

[alarimer]

11. Thanks

That does look like a good resource.

[thefool_wa]

12. Here's what I would do

I work in Excel all the time as I am a "number crunching monkey" for a call center.

Create a second tab with the same column/row headers as your initial tab (you can use the INDEX feature for this to insure accuracy).

Next, start in Cell B2 and create a formula that divides the corresponding cel on tab 1 by .03. Then just copy and paste across the entire table and it should give you a second table with the results you are looking for while preserving the initial data.

LMK if this helps.

[alarimer]

14. That worked. Thanks. n/t

[thefool_wa]

17. not a problem (nt)

[alarimer]

18. Still can't make charts from that though

If I use a pivot chart, I get everything on one chart, which I do not want. So I still need to do each species separately anyway.

[thefool_wa]

19. What do you want your chart to ential?

If you want a comparative graph - you can tell a chart to graph based on two separate data sets - just use one of the 3d charts.

This actually might be easier if I could see a copy of the worksheet you are using. Also, I don't think I fully understand what your final result needs to be.