Subject:
|
Re: Excel Question
|
Newsgroups:
|
lugnet.off-topic.geek
|
Date:
|
Wed, 5 Sep 2001 14:43:22 GMT
|
Viewed:
|
87 times
|
| |
|
|
In lugnet.off-topic.geek, Christopher Tracey writes:
[snip description of problem]
> Any ideas on how to accomplish this in Excel? This is for an
> undergraduate research project for which I am serving as an advisor. I
> know I could do with perl or fortran or something, but I don't think she
> is quite up to that level yet.
Here are some options:
1. Use MS-Access instead.
2. Brute force it, using a formula with a nasty set of nested =if()
functions, like =if(b2="x",1,if(c2="x",2,...))
3. Use hlookup.
4. Write a VB macro to figure it out.
Option 3 can be accomplished pretty easily. Using your example:
> obs 1am 1pm 2am 2pm 3am 3pm ...
> 1 x
> 2 x
> 3 x
> 4 x
Add another row, at the bottom of the table, with the time codes you want:
1 2 3 4 5 6 ...
Add a column, either on the far right or inserted to the immediate right of
column A, that uses hlookup. Each cell in this row holds the same formula:
=HLOOKUP("x",B2:$G$6,7-ROW())
In the real spreadsheet, the $G$6 should be the absolute address of the
right-hand cell of the new row you added (with the time codes). For you,
this will be something like $DQ$562.
The 7 in the '7-ROW()' will be number of data rows + 3, or 363.
So your hlookup will be something like:
=HLOOKUP("x",B2:$DQ$562,563-ROW())
There's probably an even easier way to do it than this, but I was in a hurry. :)
Steve
|
|
Message has 1 Reply:
 | | Re: Excel Question
|
| Thanks Steve (...) Trying to keep it simple and within one package. (...) thought about- didn't want to go there (...) neat idea! (...) again- I'm trying to stay within excel. It's wierd- I've encountered so many people in the sciences who haven't (...) (23 years ago, 5-Sep-01, to lugnet.off-topic.geek)
|
Message is in Reply To:
| | Excel Question [DAT]
|
| here's the sitiuation... I have an excel data set in the following format: obs 1am 1pm 2am 2pm 3am 3pm ... 1 x 2 x 3 x 4 x and so on up to 560 observations. This is data describing seedling germination (1am refers to germinated the first day in the (...) (23 years ago, 5-Sep-01, to lugnet.off-topic.geek)
|
4 Messages in This Thread:
 
- Entire Thread on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
This Message and its Replies on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
|
|
|
|
