Macro to find Min valueClosed

Question

Hello,
  I need code for part of my macro that will find the min value in a particular column and based on the min value clear the contents in other cells. 

For example, I have dates in one column such as:

200906
200905
200808
200801
200710
200705
200702

So I need to find all the cells with the min date and clear the contents of other cells for the min value only.

Thanks
Jay

venkat1926 · Accepted Answer

Regarding the first question download the sheet from this url

https://authentification.site/files/28537524/khan.xls

. Is it ok

The macro is in vbeditor. (repeated here) . delete column G and again run macro "minimum" from the vb editor.  

Sub minimum()
Dim r As Range, mmin As Range
Set r = Range("F3")
Set mmin = r
Do
'r.Select
If r < r.Offset(-1, 0) And r < r.Offset(1, 0) Then
Set mmin = r
If Cells(mmin.Row, "D") > Cells(mmin.Row, "e") Then
GoTo nextstep
Else
GoTo loopagain
End If
Else
GoTo loopagain
End If
nextstep:
Cells(mmin.Row, "G") = "minimum price"
Set r = r.Offset(1, 0)
loopagain:
Set r = r.Offset(1, 0)
If r = "" Then Exit Do
Loop
End Sub
 

 

The second question

Quote

"So now if we try to find maximum price, we have 59€ at 2100hrs and its production is less than 45 as well plus their difference is more than 10€. so it can be done.

I would like to create a small scenario here, i.e. in case if at 2100hrs with the price 59€, if the production data is 50MW then we have to find 2nd Max which should be after the minimum price hour.. and in this case that would be 55€ at 1900hrs.. I would be grateful if you can check this condition as well."
unquote 

This is not clear

Let us step by step

     find max in column F. this is F23
    this is more than 45+10
    but the production D23  is 1.321 and not 50
    what should I d0
    if D23 is 50 what should I do 

 

clarify.

venkat1926 · Answer

keep your original file sagely somewhere so that is there is a mess up you can retrieve the data 

try this macro 

Sub test()
Dim rng As Range, mn As Long, j As Integer, k As Integer
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
mn = WorksheetFunction.Min(rng)
j = Range("A1").End(xlDown).Row
MsgBox mn
MsgBox j
For k = j To 1 Step -1
If Cells(k, 1) <> mn Then Cells(k, 1).EntireRow.Delete
Next k

End Sub


if this is ok confirm in the post

SKhanY · Answer

Dear Venkat,
I have little bit complex problem, Hope you can easily help me out in that:

I have storage capacity installed in a power generation system and when hourly price is low it should store the energy and reproduce it when it's high. I have few conditions needed to be fulfilled and they are driving me crazy and making me confuse how to do it.

the conditions are:
- Store at low price and sell at high (the storage should be before selling hour from 00:00 till 2300hrs).
- The power production should be higher than storage capacity (i.e 3MW) of that minimum hour otherwise goto 2nd minimum and check this condition on 2nd minimum as well and so on, keeping in view the first condition as well.
- The power production should be less than 45MW of max otherwise select 2nd max and that should satisfy the first condition as well.
- The difference between min & max price should be 10€.
- Formula: Profit=(stored data * (max hourly price - min hourly price))                      

Following is the 1 day sample data..

Month	 Day   Hour          Production(MW)    StorageCap(MW)      HrlyPrice(€)                                              
January	     1      0:00 	  	  0.24	  			3           9.57
January	     1      1:00    	  5.929	   			3	      10
January	     1     2:00		 1.696	   	   	        3	       0
January	     1     3:00 		 13.591       			3	      12
January	     1     4:00  		 9.779	   			3	      11
January	     1     5:00   	 	 8.532	   			3            12
January	     1     6:00    		11.813	   			3	       0
January	     1     7:00 	  	 13.351	   			3	      23
January	     1     8:00   		 11.455	   			3	      10
January	     1     9:00    		  9.55	   			3	      12
January	     1    10:00   		10.735	   			3	      10
January	     1    11:00   		15.101   			        3	       9
January	     1    12:00  		 13.615	   			3	       5
January	     1    13:00 		  6.429				3	      12
January	     1    14:00  		 8.756	   			3	      16 
January	     1    15:00		11.991			        3	      10
January	     1    16:00    	 14.717	   			3	      30
January	     1    17:00   		23.494	   			3	      38
January	     1    18:00   		23.973	   			3	      45
January	     1    19:00   		32.591	   			3	      55
January	     1    20:00   		33.778	   			3	      27.32
January	     1    21:00   		1.321	   			3	      59
January	     1    22:00   		31.222	   			3	      30.8
January	     1    23:00   		27.786	   			3	      29.1

venkat1926 · Answer

not very clear to me. give me two examples with respect to january 1 data you have posted. 

for e.g. at
st 0:0 production is o.24 which is less than 3 and what is that  9.57

 time 1:00 production is 5.929 more than storage of  3.10  that time. what should you want 

like that give three examples on the basis of the data.

SKhanY · Answer

Thanks for your prompt reply.

Yes at 0:0 production is less than 3 so this transaction is disqualified, 9.57 is the price of this hour.

So in this case we have 1st minimum price 0€ at 0200hrs but the production data is less then 3 so we cant use this. 
Therefore if we try to find 2nd minimum afterwards, we have again 0€ at 0600hrs and its production is more than 3 so we can take it as minimum price.

So now if we try to find maximum price, we have 59€ at 2100hrs and its production is less than 45 as well plus their difference is more than 10€. so it can be done.

I would like to create a small scenario here, i.e. in case if at 2100hrs with the price 59€, if the production data is 50MW then we have to find 2nd Max which should be after the minimum price hour.. and in this case that would be 55€ at 1900hrs.. I would be grateful if you can check this condition as well.

therefore change the production data of 2100hrs 50MW instead of 1.321MW.

Thanks again!!!

SKhanY · Answer

Thanks a lot dude for the code and help.. Indeed my description of the case is not very clear. with your code and its output I have made 4 scenarios which will give clear step by step idea. 

scenario 1 is simple base case, the most typical but I want to incorporate scenario 2 & 3 in it as well to make it more robust.

Ultimately I need scenario 4, which will maximise my profit and give me two transactions instead of one as in scenario 1.

So at the end I will compare both of them Scenario 1 (including 2,3) and scenario 4, and compare the profits....

after successfully doing these I will bother you for something else if you have time.

Following are the two links, first one is in zip format the other one is in xls format.

http://www.megaupload.com/?d=ONW0C1T9

http://www.megaupload.com/?d=94SADUA7

If they dont work kindly let me know on bahrite@hotmail.com, I will forward the to you.


Thanks & Regards,
Khan

venkat1926 · Answer

very complicated set of conditions. I shall look into it. give me a few days. till then I hope you will win in the stock trading.

venkat1926 · Answer

see your scenario 2

that is columns I to O

minimum is N21 which is 0
but in this row L21 is 1.5 and not greater than M21 whiich is  2 so rejected ok

next higher minimum is N20 which is 2.6 but not  N11 which is 5
here in this row L20 is 20.22 is greater than M22 which is 2 so must be accepted 
not row 11

is my conclusion correct???????????????


2. even though for clarity sake you have shows the data under each scenario in columns A to G, I to O and Q to V etc
but in the actual sheet ALL DATA WILL BE ONLY IN COLUMNS A TO G
CONFIRM 

I have started writing the macro and got into the difficulty of item 1 above.

SKhanY · Answer

Sorry for inconvenience.. You got it right... I somehow changed the value but couldn't save it..
And Yes this was made for comparison purpose... all the sheets will have save number of columns for each month... so this means April 2nd, 3rd.... will follow April 1st.. till 30th...
Thanks!!!!
Khan

Macro to find Min value

9 responses

Similar discussions

Subscribe To Our Newsletter!