Max and Min value and round up

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
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
ABCDEFGHIJKL
1Head1Head2Head3Head4Head5Head6Head7Head8Head9
2MondayGasE02-11-2020220.5221.4226.6216.1219.95
3TuesdayGasE03-11-2020219.35221228.45220.05223.5
4WednesdayGasE04-11-2020222.95226.95233212230.05
5ThursdayGasE05-11-2020231.35234236.65227.6235.5
6
7FridayGasE06-11-2020235.45235.1242.7233.05239.05opening stock247.3248
8MondayGasE09-11-2020239.85243247.3237238.3Closing stock231.25231
9TuesdayGasE10-11-2020238.8240240.4231.25237
10WednesdayGasE11-11-2020236.7234.1238.7231.4233.2
11ThursdayGasE12-11-2020234.15235242.65233.25241.45
12
13FridayGasE13-11-2020240.55241.45245238.05242.5opening stock350.75351
14MondayGasEClosing stock238.05238
15TuesdayGasE17-11-2020244.7246.25293.6242.55293.6
16WednesdayGasE18-11-2020292.3302.45350.75301339.9
17ThursdayGasE19-11-2020345.1340.35346.8312.15331.4
18
19FridayGasE20-11-2020331.7333.9361.5325.2343.6opening stock365.75366
20MondayGasE23-11-2020345.25358365.75340.95342Closing stock311.95311
21TuesdayGasE24-11-2020344.65348.8350.95337.5338.5
22WednesdayGasE25-11-2020338.75341.6341.6320325.15
23ThursdayGasE26-11-2020324.9323.5324.25311.95315.8
Sheet1
Cell Formulas
RangeFormula
K7,K19,K13K7=MAX(G7:G11)
L7,L19,L13L7=ROUNDUP(K7,0)
K8,K20,K14K8=MIN(H7:H11)
L8,L20,L14L8=ROUNDDOWN(K8,0)


please check and advice
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,642
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please Upload Desired results Also and you what to see at where?
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,642
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
VBA Code:
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

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Thanks

maabadi


you have done amazing work for me, great and

thank you very much again
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,642
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're welcome & Thanks for feedback again.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,744
Messages
5,626,621
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top