Minimum IF

[Jive]

I have a colum of values, from which i need to find the minimum. However i
only want it to refer to those which have a specifc value in the adjacent
column. i.e.

A B
1 3 1
2 2 1
3 5 2
4 6 2
5 4 3
6 1 1

Minimum for "B=1" 1
Minimum for "B=2" 5
Minimum for "B=3" 4

Thanks in advance

 

[Pete_UK]

Put the value of B that you are interested in in cell C1, and this array*
formula in D1:

=MIN(IF(B$1:B$6=C1,A$1:A$6,10E10))

You can change the ranges if you have more data.

* As this is an array formula, then once you have typed it in, or
subsequently amend it, you will need to commit it with CTR-SHIFT-ENTER (CSE)
rather than the usual ENTER. If you do this correctly then Excel will wrap
curly braces { } around the formula when viewed in the formula bar - you
should not type these yourself.

Change the value in C1 to see the minimum change, or put other values in C2
downwards and copy the formula down to suit.

Hope this helps.

Pete

 

[Conan Kelly]

Jive,

I think a pivot table would work nicely for this.

Put B in the Row area of the pivot table.
Put A in the Data area, then set the properties to find the minimum.

Look up pivot tables in XL's Help to learn how to use them. If you still
need help, write back for detailed instructions.

HTH,

Conan

 

[Jive]

The sheet is a lot larger than my small sample and i now have something like
1000 arrows when i trace dependents from each "minimum" value. Will no doubt
increase calculation time but thank you.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

The sheet is a lot larger than my small sample and i now have something like
1000 arrows when i trace dependents from each "minimum" value. Will no doubt
increase calculation time but thank you.








- Show quoted text -