Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Conditional Minimum value

Hi,

Can somebody help me with a conditional minimum value function. I am looking for a function that will help me establish minimum value, above a certain threshold. similar to sumif or countif but than a minif

all suggestions greatly appreciated.

Eric


Post your answer or comment

comments powered by Disqus
I have a bunch of rows. I need the unique row that has a minimum value in column D, but also satisfies some conditions on column C and G.

example:

C D G
1 A 2 B
2 C 1 A
3 A 1 B
4 A 3 A

Column C has to have a value of "A", column G has to have a value of "B".

In this case, row 3 would be returned since column D has the minimum value between the rows that satisfy the conditions specified

Hi all,

I am trying to get a formula with a vlookup which will enable me to find the minimum value for a specific code. I would also like that the formula exclude the results which are less than 100?

My formula is the following:

=MIN((VLOOKUP(G3,A:B,2,FALSE)))

How can I add a condition? i,e exclud from the result the figures which are bellow 100

I have attached my excel spreadsheet as well (I think it will make more sense)

Hi,

Is there a way to calculate a conditional minimum? I have a range of 1000+ values and I need to find the minimum, but the minimum has to be greater than a specified minimum threshold. Thanks!

I want to find the minimum value in a column of values (say A1:A400), but only if the value is "enabled" to be considered per a second column of 0-or-1 values (say B1:B400). I know that I can write a VBA macro to do it or create a 3rd column which assigns an arbitrarily high value for values not to be considered and use the MIN function on that column of values, but I was wondering if there was a way to use worksheet functions to do it without creating a 3rd column of values.

Can anyone think of a way to do it?
Thanks,
Steve

Ok, so here is my data:
	NO	NO
8	Inf.	6	8	4	1	3	8
What I want to achieve is to find the minimum value in the bottom row, however columns that have "YES" in the top row should be ignored when calculating the minimum, (so in the example above the minimum in the second row would be 3, as the 1 in the BLUE column is ignored).

In addition to this, some, all, or none of the bottom row could have the text "Inf." instead of a value. In my data this represents 'Infinite' so can effectively be ignored.

Finally, to highlight an issue I have had, if all of the bottom row are "Inf." then running MIN() against the bottom row will return 0, however in this situation that is obviously misleading.

Thanks all, I hope to learn something here

Here is my situation: I have a set of date in columns A and B. Let's say that the date in column A is 5, 8, 6, 6, 4, and the data in column B is 6, 4, 8, 4, 5. What I want to do is set up a conditional or other formula that will highlight the minimum value in each column, but ONLY if it is the lone (unique) minimum, so in this example I would want the "4" cell value highlighted in column A, but not in Column B since it appears twice in that column. If anyone has any advice on how I can achieve this I would greatly appreciate it. Thanks.

Drew

Greetings,

I have two columns in my data table that look something like below:

Column A Column B 1 Y 2 Y 3 4 Y 5
I would like to highlight the row with the minimum value in Column A where the Column B is not "Y".

In the example above, the row with "3" should be highlighted.

I've tried combinations of IF and MIN in my formula for condition formatting but to no avail. Can someone point me in the right direction?

Thanks!

Hi All, I have an excel sheet containing a few columns, one column contains details which are duplicate and another columns contains values, all i want to do is pick the unique records with minimum values and highlight them. I tried to do conditional formating, but after sort option it showed highlights for the new rows. I mean the formating stayed but I moved the values. I'll try to explain it on below example:

Let's take two columns. Column A displays some items from a several producents. Column B contains prices. Let's say there are 200 products from 50 sellers. I need to find 200 minimum values.

Column A Column B
abc £3,40
def £4,25
ghi £5,00
jkl £3,95
def £4,05
abc £3,42
......

I have 10k rows and doing this by hand it takes a lot of time.
I want to have highlighted in Column B prices £3,40 the cheapest price for abc product and £4,05 the cheapest def.
It's about 200 minimum prices.
So the price must dependent on details based in Column A.

That was the first part, more important.

The next thing: i would like to have attached notes or comment to highlighted cells with all the information from four Columns, (A,C,D,E).

Cell B2 - highlighted £3,40 and attached note "A1,C1,D1,E1".
Cell B6 - highlighted £4,05 and ...

If anybody knows how to do it please let me know.

Hello,

I am trying to use the spinner bar in a financial model and need it's minimum value to be based on what is put in another cell.

Basically, if the other cell's value is 10, the minumum value of the spinner bar should be 20% less than 10 (hence 8).

Cell C5 has the value 10. Cell D5 is linked = C5 initially and has the spinner bar linked to it. D5 should not be allowed to go lower than 8.
If C5 has the value 20, Cell D5 should not be allowed to go below 16.

The end result is that a user should be able to tweak the amount using a spinner bar but the amount it can go down by is based on 20% below his original input.
Also, Cell C5 should get locked as soon as the user enters an amount.

Thanks

Hi,

Feeling difficulty in forming the sequence of the matter...
I want to highlight a perticular row, as long as a gap or empty cell does not comes, corresponding to Minimum value of a coloumn range which must be defined by the user by box message.

Min Value coloumn c 71.5 10.9 4.4 288.0 430.0 189.2 4.01 q d 41.6 -1.9 -18.1 298.4 430.0 198.0 5.93 e e 35.6 -2.6 -20.4 293.7 430.0 194.0 6.14 r f NA NA NA NA NA NA NA t g 123.2 51.3 -14.9 297.7 430.0 197.4 2.38 y h NA NA NA NA NA NA NA u i NA NA NA NA NA NA NA i j NA NA NA NA NA NA NA o k NA NA NA NA NA NA NA p
this is the job I need to do. Range of minimum value coloumn must be defined by the user.
Thanks in advance...

Hello guys,

I'm working on interactive charts (so what else is new?) that need to have their minimum values on their axes automatically adjusted (we don't want a ZERO) depending on the smallest value of the active dataset because otherwise, all the series will be so close to one another, it won't make sense to do the charts in the first place. The data points depending on the selected data range could vary from a range of 5 to 10, to 25 and 40, etc. Also, there are times when the value axis will display percent values. Any idea how to determine what the lowest value is in the selected dataset or range? Or if you have any other ideas on how to implement this, please feel free to let me know.

FYI the chart is interactive because the user can select what data to view through radio buttons and a drop down list box.

This is probably an easy one that I have been racking my brain for ages on.
If I have a column with manhole numbers and relevant invert levels I want in the third column to chose the minimum value of these if the manhole comes up again. ie.
col1 col2 col3
MANHOLE INVERT MIN INVERT LEVEL
F2 100 x
F3 96 95
F3 95 95

I have tried the following but I didn't pick the minimum. IF(ISBLANK(Col1),"",MIN(VLOOKUP(col1,col1:col3,3,FALSE)))

Ta in advance

Hi everyone,

how can i have a minimum value to a cell i.e you have to enter 6 digits

my current code stops someone entering a letter and the max length is set in the vb properties but now im lost!!

my code:

Private Sub GlobalAccount1_Change()
Dim TbCheck

If TbCheck = IsNumeric(GlobalAccount1) Then
MsgBox "This is an invalid entry! Please delete and retype a number "
End If
TbCheck = IsNumeric(GlobalAccount1)
End Sub

Hi
I am trying to find the minimum value between two dates and price data that is on a different sheet. I have attached a sample workbook that contains the data and the requirements.

Your assistance is much appreciated

regards
SUJU

Hello All,

I am new to the forums, and somewhat new to using VBA.

What I would like to do: On sheets "TeamAvg" and "IndividualAvg" with given data in B3:K118, I need help creating a macro that will highlight(format the color/style) the cell containing the minimum value for each row of data(rows 3-118). Some rows are likely to have blank cells, and some rows may have two cells of the same value which happen to be the minimum value(s).

The data in the range of B3:K118 is constantly being updated through another macro I created to input averages. I just need the minimum value of the averages in each row highlighted.

Any help/suggestions is greatly appreciated.

Thanks in advance!

-Kyle

Hi,

I have a chart where I set lower and upper threshold lines by use of two spinners. It goes awry if you set the upper limit below the lower limit. Is there a way to set the minimum value of one spinner so that it can't go below the maximum value of the second one?

If there's another way to do this, I'd be grateful for suggestions.

Thanks

Dave

Hello, I have a 10x10 array that represents different cities that a travelling saleperson can travel to. Rows are cities designated as i values, columns are the same cities and represented by j values. I need to use a For, Next loop to determine the shortest distance (lowest value) in a given column. The i (row) that contained the lowest value is the first city to be visted and a boolean is entered for that j=i column, showing that the city has been visited. When pulling the minimum values from the column I need to ignore 0 values where the distance is between a city and itself. I'm having trouble coming up with a loop that takes identifies the i row with the lowest value that also ignores previously visited cities and takes the boolean into account. Maybe my Excel spreadhseet will clear up what I'm trying to do, please let me know if you can help! The distances were generated using RANDBETWEEN(1,100).

I am trying to compare multiple values within a row and report the column header of minimum value in an adjacent column.
I am able to do this using embedded IF's with multiple columns, but here is the crux: when there is #N/A in a column, my formula doesn't know what to do with it, and just reports #N/A.

For some context, I am trying to compare the prices of various items between 3+ sellers. I am trying to use Excel help me identify which seller is cheapest for each product. Some items have prices for all the products, some for only 2, or only 1 of the products. (So you can probably see how this could get complex using a series of IF statements.

Here is a sample of how it SHOULD workTrying to get the column titled "LOWEST" (in BOLD) to yield the store names -- #N/A should not appear in the LOWEST cell)
Item Local Staples Amazon LOWEST Paper #N/A $4.56 $4.77 Staples Ink $16.95 $14.99 $13.25 Amazon Pencils $6.08 $5.15 $6.68 Staples Binders $9.99 #N/A $5.98 Amazon Stapler93xbc $20.06 #N/A #N/A Local ToiletPaper $23.99 $25.97 #N/A Local

Hi all,

I'm trying to figure out how to find a minimum value between two unique data points. My dataset is below:

0
0
0
1
3
2
6
0
0
8
2
4
0
0

So the problem is, the dataset is much longer than this. I need to find multiple values. Each time the dataset goes from "0" to a non-zero value I need it to start looking for the minimum value until the next "0" comes up in the dataset. I need it to repeat the same steps for the next set of values after the "0" values change to a non-zero value again.

So for this example I would have to answers: "1" for the first set of non-zero values and "2" for the next set. If I could have the values input next to the last value in each set would also be nice but not necessary.

Thanks in advance for your help. I hope this is possible.

Luke

Hi,

This is my first post in Ozgrid (though I have read the forums for resolving my earlier doubts).

As a sample, I have attached an excel with the data.
I am trying to find maximum value of Profits in each year 2008, 2009 & 2010 (Columns B, C & D) for each unique item (Column A) from various sheets (Company-A & Company-B).
E.g., for Item number 165, the maximum profit in 2010 is 738 and minimum profit is -34477 and so on.
I would need the results in a separate sheet for each unique Item no.

So far, I have only been able to find an approach by filtering column A for each item value, then using subtotal formula to display the maximum/minimum values of columns B, C & D in cells and then copy using vba them to a new sheet.
I am not familiar with Pivot tables.
Can anyone please guide whether there is an easy way out which I am not aware of?

Advance thanks

EDIT: Dear all, Sorry for the cross post. The same post is posted at Mrexcel & Excelforum. Thanks AAE for the guidance.

I have a simple table attached that i need to manipulate as such...
For the values in column A I need to determine the minimum value corresponding to that row and return the row value in the header row (row 1). So for Sweden the formula would return Temple and for an input of Houston the return output would be Australia. Any help would be appreciated.

Hi,

has any of you happened to make a function that finds the minimum NONZERO value in a data range?
(i.e. all data are numbers, all of them are positive, but some of them are zero. The function should take the excel range as input, and return the minimum value above zero).

Thanks!

I have a column (A1:A10) of values formatted as currency with a £ symbol.

£3.00
£4.00
£5.00
£6.00
£7.00
£1.00
£9.00
£11.00
£112.00
£12.00

I want the cell with the minimum value to be set to bold. The Application.Min method always finds the correct cell, I check this with a MsgBox output but when I search for this value using the .Find method it doesn't find it.

I tried changing the parameter: LookAt:=xlWhole to LookAt:=xlPart. When I do this it does find the cell but of course if I put a similar value in the preceding cell which also contains a matching part of the number I'm really looking for, it finds that instead.

I have some very simple code to do this attached to a button as follows:


	VB:
	
 CommandButton1_Click() 
    Dim rng       As Range 
    Dim minVal   As Variant 
    Dim target   As Variant 
     
    Set rng = Sheets("Sheet1").Range("A1:A10") 
     
    minVal = Application.Min(rng) 
    MsgBox minVal 
     
    Set target = Sheets("Sheet1").Range("A1:A10").Find(What:=minVal, LookIn:=xlValues, LookAt:=xlWhole) 
    If Not target Is Nothing Then 
        target.Font.Bold = True 
    Else 
        MsgBox "target not Found" 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can I use the .Find method to select this cell?

Thank you

Hi,

I would like to write a macro which would turn off particular Pivot Table Values based on a minimum value criteria which is in a particular cell.

For example say I had a Pivot Table with one column representing percentage values and would like to turn off all values which are less than a specfied value in cell D4 i.e 60%.

How can I achieve this

Any help is greatly appreciated


No luck finding an answer? You could always try Google.