Basic and not-so basic excel questions

May 26, 2010 3:08 AM

Excelfilter: Okay, I'm confident that this can be done in Excel, but I don't know how. Basically, I have a set of samples for which a value (size) was measured at various days. I need some formulas to tell me when the first and last values were measured and what those values were (for each sample).

So the data set currently looks something like this, where values equal size for samples A, B, C etc. at time points 7, 14,..days.

d 7 14 21 28
A 1 2 4 -
B 5 6 8 6
C 5 - 8 16
D - 4 8 -

What I would like to have Excel automatically determine for me is:
first time point measured (E tp M)
last time point measured (L tp M)
size at earliest timepoint (size at E tp)
size at last timepoint (size at E tp)

So for the above data set, the values would be:

__EtpM_LtpM_sizeEtp_sizeLtp
A 7 21 1 4
B 7 28 5 6
C 7 28 5 16
D 14 21 4 8

And bonus points for anyone who can tell me if there's a way of determining whether, for each sample, the values increased with every subsequent time point or whether there was a drop at one or more timepoints (i.e. a yes/no output on whether the sample always showed positive growth).

Any and all help would be greatly appreciated!

posted by kisch mokusch to Computers & Internet (9 answers total)

Best answer: Someone who knows excel better than me will probably come along with a one liner, but I always end up splitting stuff out into arrays.

If we say your spreadsheet is laid out with labels in the first row and first column, so your data for A is in B2:B5, create a new set of formulas that look like:

=IF( B2 <> "", COLUMN(B2)-1, "" ) =IF( B3 <> "", COLUMN(B3)-1, "" )
(assuming your empty values are really empty cells, if they are "-" just change the condition).

You can use autocomplete to fill them all in so you only actually type the first one.

If you do that for all of your data you get a set of cells the same size, but only containing the column index of non-blank data (mine ended up with the first data set in B8:E8)

A	1	2	3	
B	1	2	3	4
C	1		3	4
D		2	3	

Then it's dead easy to get things like the min value (sizeetp) by indexing back into your original data using the min column value: =INDEX(B2:E2,1,MIN(B8:E8)), or the first timepoint (etp): =INDEX($B$1:$E$1,1,MIN(B8:E8))

For doing your "did the size keep ascending" problem I would approach it in a similar way - create another set of data the same size as the original but just consisting of =IF( curval >= nextval, 1, 0 ) which would give you an array with 1s wherever the growth was not increasing.

On preview I could have explained better, hopefully you get the idea.
posted by samj at 4:18 AM on May 26, 2010 [1 favorite]

Best answer: The straightforward solution is nested IFs.

Like this to find your "size at last timepoint" for the values from C2 to F2:
=IF(ISNUMBER(F2),F2, IF(ISNUMBER(E2),E2, IF(ISNUMBER(D2),D2, IF(ISNUMBER(C2),C2,NA()))))

Or this for "first timepoint measured" in C2 to F2, assuming the times are the column headers in C1 to F1:
=IF(ISNUMBER(C2),C$1, IF(ISNUMBER(D2),D$1, IF(ISNUMBER(E2),E$1, IF(ISNUMBER(F2),F$1,NA()))))
From those you can get the other two by flipping order or the $1.

For the monotonic increasing thing you probably want extra columns as samj suggests. The problem of course is the blank columns.

If you define columns K-N, with:
K2=IF(ISNUMBER(C2),RANK(C2,$C2:$F2),10)
L2=IF(ISNUMBER(D2),RANK(D2,$C2:$F2),K2) [drag right to M2,N2...]
M2=IF(ISNUMBER(E2),RANK(E2,$C2:$F2),L2)
N2=IF(ISNUMBER(F2),RANK(F2,$C2:$F2),M2)

Then I think K2 should be monotonic decreasing (and nonsparse) <> C2:F2 is monotonic decreasing. The flip from increasing to decreasing is because that's how RANK rolls baby. The 10 in K2 is just something >= the highest possible rank.

Then you have:
O2=AND(COUNTIF(C2:F2,">0")>1,K2>=L2,L2>=M2,M2>=N2)

Then O2 should be true if C2:F2 has more than one test value and those values are monotonic increasing. You can change/remove the COUNTIF depending on if you want data sets with 0-1 data points to count as monotonic increasing.
posted by fleacircus at 5:06 AM on May 26, 2010 [1 favorite]

Response by poster: Thank you for the reply samj. And I'm happy with anything that works!

yes, the non-values were empty cells, not actually "-". I just had the "-" because the numbers kept being crammed together in preview.

The formula you gave me:

=IF( B2 <> "", COLUMN(B2)-1, "" ) =IF( B3 <> "", COLUMN(B3)-1, "" )

just gives me TRUE or FALSE, not 1s, 2s and the like

When I manually created the B8:E8 array that you describe, your other formulas worked beautifully. But I can't get the B8:E8 array.

I could get past that by using nested IFs (I hadn't heard of them until tonight) to get a reference value for the other formulas:

=IF(B2>0,1,IF(C2>0,2,IF(D2>0,3,IF(E2>0,4))))
for the minimum value and the reverse
=IF(E2>0,4,IF(D2>0,3,IF(C2>0,2,IF(B2>0,1))))
for the maximum value

But as I understand you can only stretch such a formula to 7 nested IFs, and I have 9 :-(


fleacircus, thank you for your response. Your ranking system rocks! But I have the same issue re: nested IFs.

Is there a solution for larger data sets?
posted by kisch mokusch at 5:34 AM on May 26, 2010

Response by poster: 9 time points, that is.
posted by kisch mokusch at 5:34 AM on May 26, 2010

You might have formatted those cells to show TRUE and FALSE I guess.

To debug try just sticking the column formula in without the if around it:
=COLUMN(B2)-1
that should always give you the column number, not a TRUE or a FALSE.

If all else fails I can upload the example somewhere, but it's probably better to puzzle things out.
posted by samj at 6:03 AM on May 26, 2010

I believe Excel 2007 allows 64 nested ifs.
posted by ChrisHartley at 6:05 AM on May 26, 2010

Best answer: Ha ha Excel sucks so bad.

Okay, you can get the column number of the first value this way:
G13=MIN(IF(ISNUMBER(C13),1,100),IF(ISNUMBER(D13),2,100),IF(ISNUMBER(E13),3,100),IF(ISNUMBER(F13),4,100), [...])

And the column number for the last value this way:
H13=MAX(IF(ISNUMBER(C13),1,0),IF(ISNUMBER(D13),2,0),IF(ISNUMBER(E13),3,0),IF(ISNUMBER(F13),4,0), [...])

I just put in values for 4 columns, you'll have to extend. The 100 and 0 are dummy indices to make the non-number cells not show up as MIN or MAX unless there are no entries.

Then you can use OFFSET. For example first size/first time/last size/last time:
=IF(G13>0,OFFSET(C13,0,G13-1),NA())
=IF(G13>0,OFFSET(C$1,0,G13-1),NA())
=IF(H13<1> =IF(H13<100,OFFSET(C$1,0,H13,-1),NA())
posted by fleacircus at 6:16 AM on May 26, 2010 [1 favorite]

Last line munged by HTML, but you get the idea.
posted by fleacircus at 6:18 AM on May 26, 2010

Response by poster: Don't worry samj. I'm an idiot. I had put
=IF( B2 <> "", COLUMN(B2)-1, "" ) =IF( B3 <> "", COLUMN(B3)-1, "" )
all into one cell, not realising they were two formulas for two cells.

=IF( B2 <> "", COLUMN(B2)-1, "" )
alone worked fine!

Unfortunately, as fleacircus pointed out, the blank columns ruin the =IF( curval >= nextval, 1, 0 ) formula :-( But I have fleacircus' RANK formulae for that :-)

I have Excel 2008 on my other (newer) computer, so I probably shouldn't have been nutting this out on my old one (Excel vX)! Just tried nesting 9 IFs on that computer and had no problems!

And fleacircus, thanks again. Yes, I got the idea and they worked perfectly!

I think I can consider all problems solved. You are Excel gods and have made my life so much easier!
posted by kisch mokusch at 6:59 AM on May 26, 2010