# Simple formula help...

#### Golfpro1286

##### New Member
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)<>

Last edited:

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### DanteAmor

##### Well-known Member
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.

Maybe:

#### Golfpro1286

##### New Member
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

##### New Member
Maybe:

AB
13%
22.50%
32.25%x
44%
52.25%
6
7
8
9
10x

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
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

##### MrExcel MVP
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.

Last edited:

#### Golfpro1286

##### New Member
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

##### MrExcel MVP
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.