Search results

Hi Jasmin, Just so that I understand, you said that you have a fourth column 'Z' that plays into this project? The Z column has a value in it, and if it passes a Low or High threshold, you wish to delete rows in the second table in your original post? I'll second Narayan's point, that I'm...

Hi Jasmin, Assumptions: 1. The header row is in row 1, X is in cell A1, Y is in cell B2, etc. 2. The data starts in row 2. 3. You cannot insert a helper column to concatenate the data. I have taken your small sample and made this formula work...

Luke and Narayan, I think the main difficulty lies in the fact that I haven't explained myself well enough. What I am trying to do is access a range from a closed workbook, by using a named range (in the name manager) "test" that =C:[book.xls]a1:c3 From that named range I can make an array...

Hrmph. It doesn't seem to work for me. Maybe this is something that cannot be done using native formulas. And I was all set to blather on about the utility of SUMPRODUCT... Thank you Luke for your help.

Thank you Luke. That is exactly what I thought too. What I wondered was how I could get the equivalent of a ROW() formula to tell me that "frank" was on the second "row" of the array. Given two arrays {"bob","steve","larry";"larry","frank","steve";"steve","larry","bob"}. and...

Let's say that I have a named range TEST, that refers to an array. The contents of the array is {"bob","steve","larry";"larry","frank","steve";"steve","larry","bob"} In cell A1 is "frank". What would be the formula I would need to determine the "row" position of "frank" (2)? How would I...

Hi Lo Baan, I have a feeling I could help, but I wonder if you could answer some questions: - is the current month workbook saved using the same name as the prior month, or do they have differing names, like "[standard workbook name] August 2011.xls" and "[standard workbook name] September...

=VALUE(REPLACE(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00")),1,CHOOSE(RIGHT(A1*100)+1,0,5,5,5,5,5,9,9,9,9))) You're right, I was copy pasting from B12 instead of B1. Phew! Nothing like having an active pool of formula testers on hand! I'm glad I was able to (sort of) help!

Hmm. Your test data seems to work with the last formula. Is it a text vs. number format thing?

My test pool was quite small. How is this one? =VALUE(REPLACE(TEXT(A12,"0.00"),LEN(TEXT(A12,"0.00")),1,CHOOSE(RIGHT(A12*100)+1,0,5,5,5,5,5,9,9,9,9)))

Humph. Well, this version gets rid of the IF in favour of a double unary solution: =VALUE(REPLACE(A1,LEN(A1),1,CHOOSE((--(RIGHT(TEXT(A1,"0.00"),1)="0")*10+RIGHT(TEXT(A1,"0.00"),1)),5,5,5,5,5,9,9,9,9,0))) My mistake for being too enthusiastic about CHOOSE! :)

I've wanted to find a way to use the CHOOSE function for a while -- thank you for providing me an example! =VALUE(REPLACE(A1,LEN(A1),1,CHOOSE(RIGHT(A1),5,5,5,5,5,9,9,9,9,0))) Is this what you need?

Hi Kamarlon, I think I know what you're trying to do. Try this: 1. Create a named range called "current". In the refers to, enter =OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A$3:$A$24),1) 2. Create named range called "prior". In the refers to, enter...

Hi persol, I apologize if I'm stating the obvious but if you have Excel 2007 or later, you could use the Remove Duplicates tool in Data Tools. Mr.Excel had a youtube cast on the feature here: http://www.youtube.com/watch?v=JK-KtWscBX4 He also has a page on his site concerning other...

{=STDEV(IF(($A$1:$M$15>0)*($A$1:$M$15<121),$A$1:$M$15))} You're right, the zero values would foul it up. Add "($A$1:$M$15>0)*" to your formula -- it worked for me.

Oh, I thought that it did work -- it gave me the MIN for each unique Unit ID. But you have to press Ctrl-Shift-Enter after copy/pasting the formula in the formula bar, as suggested by Luke, or you will only get the MIN of the entire array, as you have found. Good luck!

I've read a bit about this and it looks like the SUMPRODUCT/MIN formula does not typically work. I was going to suggest the MIN/IF array formula as it worked for me. {=MIN(IF(($D$9:$D$24=D9),$C$9:$C$24))} Reference http://www.mrexcel.com/forum/showthread.php?t=13192