Max and Min value and round up

[Vishaal]

Hi,

we are putting the formula in K7 and K8, after that round up and round down in next cell

required any vba or code for whole column, also before the K7 and K8 and also add the heading "Opening Stock" and "Closing Stock"

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Head1	Head2	Head3	Head4	Head5	Head6	Head7	Head8	Head9
2	Monday	Gas	E	02-11-2020	220.5	221.4	226.6	216.1	219.95
3	Tuesday	Gas	E	03-11-2020	219.35	221	228.45	220.05	223.5
4	Wednesday	Gas	E	04-11-2020	222.95	226.95	233	212	230.05
5	Thursday	Gas	E	05-11-2020	231.35	234	236.65	227.6	235.5
6
7	Friday	Gas	E	06-11-2020	235.45	235.1	242.7	233.05	239.05	opening stock	247.3	248
8	Monday	Gas	E	09-11-2020	239.85	243	247.3	237	238.3	Closing stock	231.25	231
9	Tuesday	Gas	E	10-11-2020	238.8	240	240.4	231.25	237
10	Wednesday	Gas	E	11-11-2020	236.7	234.1	238.7	231.4	233.2
11	Thursday	Gas	E	12-11-2020	234.15	235	242.65	233.25	241.45
12
13	Friday	Gas	E	13-11-2020	240.55	241.45	245	238.05	242.5	opening stock	350.75	351
14	Monday	Gas	E							Closing stock	238.05	238
15	Tuesday	Gas	E	17-11-2020	244.7	246.25	293.6	242.55	293.6
16	Wednesday	Gas	E	18-11-2020	292.3	302.45	350.75	301	339.9
17	Thursday	Gas	E	19-11-2020	345.1	340.35	346.8	312.15	331.4
18
19	Friday	Gas	E	20-11-2020	331.7	333.9	361.5	325.2	343.6	opening stock	365.75	366
20	Monday	Gas	E	23-11-2020	345.25	358	365.75	340.95	342	Closing stock	311.95	311
21	Tuesday	Gas	E	24-11-2020	344.65	348.8	350.95	337.5	338.5
22	Wednesday	Gas	E	25-11-2020	338.75	341.6	341.6	320	325.15
23	Thursday	Gas	E	26-11-2020	324.9	323.5	324.25	311.95	315.8
Sheet1

Cell Formulas
Range	Formula
K7,K19,K13	K7	=MAX(G7:G11)
L7,L19,L13	L7	=ROUNDUP(K7,0)
K8,K20,K14	K8	=MIN(H7:H11)
L8,L20,L14	L8	=ROUNDDOWN(K8,0)

please check and advice

 

[maabadi]

Please Upload Desired results Also and you what to see at where?

 

[Vishaal]

we have used the max formula in formula in K7, K13 and K19
we have used the Min formula in formula in K8, K14 and K20

We have used the ROUNDUP in L7, L13 and L19
We have used the ROUNDDOWN in L8, L14 and L20

we have done it manually but we want to do it through vba, uploaded is a demo data we have many data in our sheet and doing it manualy taking a long time

 

[maabadi]

Try this:

Sub OutCome()
Dim i As Long, j As Long, Lr As Long, Cr1 As Variant, StRow As Long, EndRow As Long, K1 As Long, L As Long
Dim K2 As Long, K3 As Long
Lr = Cells(Rows.Count, 1).End(xlUp).Row

i = 2

Cells(i, 10).Value = "Opening Stock"
Cells(i, 11).Value = Application.WorksheetFunction.Max(Range("G" & i & ":G" & i + 4))
Cells(i, 12).Value = Application.WorksheetFunction.RoundUp(Cells(i, 11).Value, 0)
Cells(i + 1, 10).Value = "Closing Stock"
Cells(i + 1, 11).Value = Application.WorksheetFunction.Min(Range("H" & i & ":H" & i + 4))
Cells(i + 1, 12).Value = Application.WorksheetFunction.RoundDown(Cells(i + 1, 11).Value, 0)
Range("J" & i & ":K" & i + 1).Interior.ColorIndex = 6

For i = 7 To Lr Step 6

Cells(i, 10).Value = "Opening Stock"
Cells(i, 11).Value = Application.WorksheetFunction.Max(Range("G" & i & ":G" & i + 4))
Cells(i, 12).Value = Application.WorksheetFunction.RoundUp(Cells(i, 11).Value, 0)
Cells(i + 1, 10).Value = "Closing Stock"
Cells(i + 1, 11).Value = Application.WorksheetFunction.Min(Range("H" & i & ":H" & i + 4))
Cells(i + 1, 12).Value = Application.WorksheetFunction.RoundDown(Cells(i + 1, 11).Value, 0)
Range("J" & i & ":K" & i + 1).Interior.ColorIndex = 6
Next i
End Sub

 

[Vishaal]

Thanks

maabadi​

you have done amazing work for me, great and

thank you very much again

 

[maabadi]

You're welcome & Thanks for feedback again.