|Hi Debby |
V1 V2 V3 V4 V5
Capacity 30 30 24 30 24
Charging Rate 3 6 3 6 3
Input Data 6 10 12 18 12 Total Units 5
Time V1 V2 V3 V4 V5 Demand Supply
0:00 6 10 12 18 12 21 6
1:00 4.8 8.8 10.8 16.8 10.8 21 7
2:00 3.4 7.4 9.4 15.4 9.4 21 23
3:00 0.4 4.4 6.4 12.4 6.4 12
In the given example, V1-V5 represents the units (total- 5no’s) that needs to be calculated by the system based on the demand and supply. The capacity and maximum charging rate for those units are given in the first two lines and those values are constant.
The ‘supply’ column refers to the maximum available quantity to provide to those 5 units.
The “Demand” column is the sum of individual units demand limited to its charging rate. i.e. The system is able to charge the units upto its maximum charging rate.
Condition: Demand = ∑ Min (V1-V5, Charging rate)
For time 00:00, the data for those units are taken from ‘Input Data’ row. The user will give this data manually for the first row only.
For 01:00, the row has to check for the Demand Vs Supply Condition for each cell (from previous row) and to give the output (which is going to be the input to next row).
1. Calculate the demand for the first row limited to its charging rate. (Demand - 21)
2. Check whether the demand is lesser or higher than Supply. (Supply – 6; Demand > Supply)
3. If Demand is lesser or equal to Supply, meet the demand. (i.e- Row2 = Row1 - Demand)
4. If Demand is higher than Supply, divide the supply by total number of units and meet the partial demand. (i.e- Row2 = Row1 – (Demand=Supply/No. of Units))
For 02:00, same as above procedure.
For 03:00, same as above procedure.
I’m did those calculations manually in excel spread sheet writing formulas in individual cells. Now I need to write the macro to do this calculation automatically, but I’m not good in programming. So I’m looking for some help to do this calculation using macros.
I had written macros for the conditions for those calculations. But I’m able to execute for only one step not able to use it consecutively (not able to get Row3 output and as well as use as input for next row)