I am making a scenario in excel where Electric vehicles are being charged in a given time range. i have a neighborhood loads and the load of EV at specific times. the cable capacity is 50 kW (means the combined load of EV and household should not exceed this value). is there a formula, which checks if the combined load of EV and household is below 50 kW, then leaves it unchanged. if it is more than 50KW then shifts the EV load to next row and checks for the condition again if satisfied it is less then 50kw it adds it here otherwise shift to the next row until the condition is satisfied.
I have attached the sample excel file for clarification. more text in the file explains the case
picture below shows the data in the Excel file EV scenario
EV scenario
any help would be appreciated
Best,
https://1drv.ms/x/s!ArYn8TW3_a3d3n6MbEjcWsfkdB3q
I see that there is a way to check if the entered time is ok and propose for the next slot, but the user need to manually change the charging time. "checks if the combined load of EV and household is below 50 kW" > ok, "then leaves it unchanged" > Need macro to do it. In this solution, it will propose the next time slot, but user will need to re-enter the new time manually, and it will re-calculate/re-check the loading.
+-----[Steps]-----+
Assume column F & G as the only input, and all EV is 11kW. I'm proposing :
For the 1st EV : Enter the start time > Check the total loading (for 4 slots) from the time selected > if total load < 50kW, display "OK". Else, display "Select new time".
for the 2nd EV : Enter the start time > Check the total loading (for 4 slots) from the time selected PLUS loading from all previous EV > if total load < 50kW, display "OK". Else, display "Select new time".
for the 3rd EV : Enter the start time > Check the total loading (for 4 slots) from the time selected PLUS loading from all previous EV > if total load < 50kW, display "OK". Else, display "Select new time".
for the 4th EV : Enter the start time > Check the total loading (for 4 slots) from the time selected PLUS loading from all previous EV > if total load < 50kW, display "OK". Else, display "Please select next time slot".
and so on.
For each "Please select next time slot" notice, a proposed time will be displayed.
+-----[simple notes]-----+
For all these to work, need quite some work.. but it is principally straightforward. God willing. (:
attached file : https://yadi.sk/i/ssbPQaZ13ZQSpb
Note : - Column O to Column AH contain all the formulas. Just unhide it to see the details.
- linking the file data to external file/links can make the time cannot be propertily resolved in match() function. Alternatively, you may replace the time column with running numbers instead. eg. 12:00 > 1, 12:15 > 2, 15:45 > 63 and so on.
Hope that helps. (:
