Simple formula help...

Golfpro1286

New Member
Joined
Aug 22, 2018
Messages
30
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:

Some videos you may like

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
Joined
Dec 3, 2018
Messages
11,714
Office Version
2007
Platform
Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
Maybe:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">3%</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">2.50%</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2.25%</td><td style=";">x</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">4%</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2.25%</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">x</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red">A1</font>)=AGGREGATE(<font color="Red">15,6,ROW(<font color="Green">$A$1:$A$5</font>)/(<font color="Green">$A$1:$A$5=MIN(<font color="Purple">$A$1:$A$5</font>)</font>),1</font>),$A$10,""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Golfpro1286

New Member
Joined
Aug 22, 2018
Messages
30
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
Joined
Aug 22, 2018
Messages
30
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 AColumn BColumn C
13.00%
$500
22.50%$700
32.25%$350
44.00%$600
52.25%X$400
6
7
8
9
10X

<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
Joined
Aug 18, 2015
Messages
9,751
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
Joined
Aug 22, 2018
Messages
30
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
Joined
Aug 18, 2015
Messages
9,751
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,096
Messages
5,466,631
Members
406,493
Latest member
Hazem Hassan

This Week's Hot Topics

Top