Simple formula help...

[Golfpro1286]

I am not sure if it because it is Monday, but I am having trouble with a formula that I thought would be pretty easy.

Here is a simplified version of what I am trying to accomplish:
I have two columns (A1:B5) and a special cell A10 (ifgreater than 0). Column A is filled in manually with % values (i.e. 3.00%,2.50%, etc.) and I want the value from A10 to automatically show (just once)into the row of Column B that associates with the lowest % value in Column A.My initial formula (B1 example below) was easy enough:
<min(a2:a5)),$a$10,0)< span=""><min(a2:a5)),$a$10,0)
<min(a2:a5)),$a$10,0)
<min(a2:a5)),$a$10,0)

This works fine unless there are two or more % values that arethe lowest in the Column A set. If there are multiples of the lowest values, itsets them all to 0. If I adjust the formula to <= it sets them all to the A10value.

Any help would be appreciated.

Thanks!



</min(a2:a5)),$a$10,0)


</min(a2:a5)),$a$10,0)


</min(a2:a5)),$a$10,0)
</min(a2:a5)),$a$10,0)<>

 

[DanteAmor]

Your formula is not complete, you must put a space before and after the signs > or <
Also if you could copy the excel data, paste here along with putting the expected result.

 

[Eric W]

Maybe:

Book1
	A	B
1	3%
2	2.50%
3	2.25%	x
4	4%
5	2.25%
6
7
8
9
10	x
Sheet5

Cell Formulas
Range	Formula
B1	=IF(ROW(A1)=AGGREGATE(15,6,ROW($A$1:$A$5)/($A$1:$A$5=MIN($A$1:$A$5)),1),$A$10,"")

 

[Golfpro1286]

I am not sure why, but it wouldn't let me post my original code, tried to post it in a reply and it wouldn't work here either.

 

[Golfpro1286]

Maybe:

	A	B
1	3%
2	2.50%
3	2.25%	x
4	4%
5	2.25%
6
7
8
9
10	x

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B1 =IF(ROW(A1)=AGGREGATE(15,6,ROW($A$1:$A$5)/($A$1:$A$5=MIN($A$1:$A$5)),1),$A$10,"") <tbody> </tbody>

<tbody>
</tbody>

This does seem to be working. My example was simplified, and I didn't think it mattered, but now I need it to account for one other thing. There is also a Column C with $ Amounts that needs to be the tie-breaker (highest $) if there are multiple % values that are the same.

	Column A	Column B	Column C
1	3.00%		$500
2	2.50%		$700
3	2.25%		$350
4	4.00%		$600
5	2.25%	X	$400
6
7
8
9
10	X

<tbody>
</tbody>

Using your example and hypothetical data from column C, in this scenario B5 would need to be the cell since C5 is greater than C3. How would I do this?

 

[Eric W]

Try in B1:

=IF(A1=MIN($A$1:$A$5),IF(C1=AGGREGATE(14,6,$C$1:$C$5/($A$1:$A$5=MIN($A$1:$A$5)),1),$A$10,""),"")

This can be simplified a bit if you have the MAXIFS function (available in Excel 365). Also, this formula will return multiple values of A10 if there are ties in A and C. I can probably get around that and just select the first one, but I might need to know the ranges of data you might have in A and C.

 

[Golfpro1286]

Try in B1:

=IF(A1=MIN($A$1:$A$5),IF(C1=AGGREGATE(14,6,$C$1:$C$5/($A$1:$A$5=MIN($A$1:$A$5)),1),$A$10,""),"")

This can be simplified a bit if you have the MAXIFS function (available in Excel 365). Also, this formula will return multiple values of A10 if there are ties in A and C. I can probably get around that and just select the first one, but I might need to know the ranges of data you might have in A and C.

That worked nicely, but like you said it produces multiple values of A10 if there are ties in A and C. A workaround would be great if possible. The range in Column A would be limited to 0%-100% while ranges in Column C could be any number (in dollar format) greater than 0.

 

[Eric W]

Try in B1:

=IF(ROW(A3)=AGGREGATE(15,6,ROW($A$1:$A$5)/(($A$1:$A$5=MIN($A$1:$A$5))*($C$1:$C$5=AGGREGATE(14,6,$C$1:$C$5/($A$1:$A$5=MIN($A$1:$A$5)),1))),1),$A$10,"")

Turned out I didn't need to know the ranges. You didn't say anything about MAXIFS. If you have that, I could simplify this ungainly formula a bit.