Replies

If the column of interest is of numeric type, that is, it houses numbers or dates, use:

=MATCH(9.99999999999999E+307,A:A)

If the column of interest is of alphanumeric type, that is, it houses text-formatted values, use:

=MATCH(REPT(“z”,50),A:A)

Aladin

Hans,

If that’s what is required, it would be more efficient to use COUNTIF instead of using VLOOKUP twice:

=IF(COUNTIF(Sheet1!$A$2:$A$5,Sheet2!A2),VLOOKUP(Sheet2!A2,Sheet1!$A$2:$B$5,2,0),Sheet2!A2)

Aladin

Jeff,

I think I forgat to include in my attachment the offset formulas that I suggested (see the edited post).

If you want to use those formulas (see the new attachment, the modified version of yours), you can’t put them underneath the data. That is, A and B must have nothing but the relevant data.

Aladin

> How’s this as an alternative to finding the smallest non-zero value?

> =IF(MIN(B:0,MIN(B:,SMALL(B:B,1+COUNTIF(B:B,0)))

That’s a good alternative to the array formula.

Aladin

Jeff,

wrt is the acronym for ‘with respect to’.

It seems to me you’ve got the formulas of my initial reply working.

I just changed the formulas that go in D2 and D3 into array-formulas to compute MIN and MAX such a way the zeroes and blank cells are ignored. In order to do that, I suggested an additional formula that goes into D1 whose result is used by the array-formulas.

I’ll try to attach a file: it contains more than I gave you. Just look at the formulas that I proposed. [ I deleted the attachment, fearing confusion. See the attachments posted later in the thread ]

Aladin

Jeff,

Last request wrt MIN & MAX calcs invites using OFFSET!

In D1 enter: =MATCH(9.99999999999999E+307,A:A)

In D2 array-enter: =MIN(IF(OFFSET(B2,0,0,D1,1),OFFSET(B2,0,0,D1,1)))

In D3 enter: =MAX(OFFSET(B2,0,0,D1,1))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Aladin

The array-formula Legare suggested should work in Excel 97: It needs to be array-entered — that is, as described to you, you need to hit control+shift+enter at the same time, not just enter.

What follows is an equivalent formula that can be entered normally:

=SUMPRODUCT((A1:A100=”apple”)*(B1:B100>1)*(LEN(C1:C100)=0))

You don’t need OFFSET per se for what you want.

Consider the following sample in A1:B7.

{“Date”,”Price”;
37258,3.4;
37258,2.8;
37317,3.4;
37470,7.8;
37379,7.8;
37440,6.4}

The funny looking numbers are really dates in the internal representation of Excel.

In D2 enter: =MIN(B: [ or the definite range: B2:B7 instead of B:B ]

In D3 enter: =MAX(B: [ or the definite range: B2:B7 instead of B:B ]

In E2 enter: =INDEX(A:A,MATCH(D2,B:B,0)) [ or the definite ranges: A2:A7 and B2:B7 instead of A:A and B:B, respectively ]

Note. MATCH will return the first date value it finds associated with MAX (or MIN) value, while it is possible that there are more dates on which the price can be at its, e.g., MAX.

I take it that you mean:

0.00

Activate the cell of interest.
Activate Format|Cells.
Choose Custom on the Number tab.
Enter in the box for Type:

[=0]””;General

Aladin

I believe it’s better to track down why you get a #VALUE! error instead of to suppress it.
If you produce lots of blanks (“”) by means of formulas, you’re bound to get #VALUE! errors. For example, if A1 contains a formula produced “” and B1 a number, =A1+B1 will end up in a #VALUE error.

If returning a 0 is not a problem for further processing that you do, by all means return a 0. If you like not to see 0 in a cell, custom format that cell as:

[=0]””;General

Fred,

The COUNTIF part in the formula in B1 is there just by habit. It’s a tie-breaker and,since it is not needed here (Ties are already handled by the rest of the formulas), it can be dropped.

And, again by habit, the first formulas in B1 and C1 contain frozen ranges. Because these two formulas are not dragged down as you observed, you can unfreeze these two with no adverse effects at all.

Aladin

Fred,

OK, it’s Sunday evening over here, so you’ll not get a single formula that can resolve the challenging problem you’ve come up with.

Here we go.

I’ll assume that A1:A6 houses your sample data.

In B1 enter: =RANK(A1,$A$1:$A$6,1)+COUNTIF($A$1:A1,A1)-1
In B2 enter: =IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),””,RANK(A2,$A$1:$A$6,1)) [ copy down till B6 ]

In C1 enter: =RANK(B1,$B$1:$B$6,1)
In C2 enter: =IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),VLOOKUP(A2,$A$1:C1,3,0),RANK(B2,$B$1:$B$6,1)) [ copy down till B6 ]

Aladin

Activate WHERE regarding the field of interest and enter as criteria:

NOT IN(“Val1″,”Val2”,…) where Val1, Val2,… are the values you want to exclude.

By the way, AND NOT “Val1” is equivalent to NOT IN(“Val1”).

Given that A1:C16 houses the following data

{“Store”,”Product”,”Sales”;
1,”A”,10;
1,”B”,2;
1,”C”,5;
2,”A”,13;
2,”B”,22;
2,”C”,24;
3,”A”,1;
3,”B”,20;
3,”C”,12;
4,”A”,14;
4,”B”,7;
4,”C”,19;
5,”A”,9;
5,”B”,4;
5,”C”,6}

in D2 array-enter: =MATCH(C2,LARGE(IF($A$2:$A$16=A2,$C$2:$C$16),ROW(INDIRECT(“1:”&COUNTIF($A$2:$A$16,A2)))),0)

in G2 array-enter: =MATCH(C2,LARGE(IF($B$2:$B$16=B2,$C$2:$C$16),ROW(INDIRECT(“1:”&COUNTIF($B$2:$B$16,B2)))),0)

You need to hit control+shift+enter at the same time, not just enter, in order to enter an array formula.

See for the “” and “””””” in the context of COUNTIF:

http://www.mrexcel.com/wwwboard/messages/15057.html

By the way, I’d suggest Roberta to use the cheapest formula which is:

=SUMIF(B2:B5,””,prices)

if there is no formula returned blanks in the relevant ranges.

By precaution, I suggested a SUMPRODUCT formula with LEN in order to cope with the possible occurrence of formula returned blanks. Even then, your

=SUM(prices)-SUMIF(B2:B5,””,prices)

should be preferred.