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
x
x

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3%[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2.50%[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2.25%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2.25%[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(ROW(A1)=AGGREGATE(15,6,ROW($A$1:$A$5)/($A$1:$A$5=MIN($A$1:$A$5)),1),$A$10,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3.00%
[/TD]
[TD][/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.50%[/TD]
[TD][/TD]
[TD]$700[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2.25%[/TD]
[TD][/TD]
[TD]$350[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4.00%[/TD]
[TD][/TD]
[TD]$600[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2.25%[/TD]
[TD]X[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.