FaizanRoshan
Board Regular
- Joined
- Jun 11, 2015
- Messages
- 54
Hi Everyone. i'm facing some trouble how to manage this because i'm new in macro. here is details what i want to do. is very know about this please help me .
1) Use “Column I” in “adt4-16 - 4-22” tab to calculate the total number of minutes per data item.
1a) You can convert the text format of ‘1h30m’ to ‘90’ by using this equation (update cell name):
=IF( ISERROR( LEFT(I6,FIND("h",I6)-1)),0, LEFT(I6,FIND("h",I6)-1))*60 + IF(ISERROR(RIGHT(LEFT(I6,FIND("m",I6)-1),2)),0,RIGHT(LEFT(I6,FIND("m",I6)-1),2))
2) Calculate data and fill in “Discharge Time Dashboard” tab cells as labeled.
2a) “<8HR Count of Data Items” is only the count of all data items that are less than 8 hours or 480 minutes
2b) “Average of <8HRs (mins)” is the average number of minutes all data items that is less than 8 hours or 480 minutes
2c) “<8HRs Savings” is calculated by: Baseline cell in $C$3 minus “Average of <8HRs (mins).” The result of this, multiplied by Cost/Hour in $D$1, multiplied by “<8HR Count of Data Items.”
2d) “<5HR Count of Data Items” is only the count of all data items that are less than 5 hours or 300 minutes
2e) “Average of <5HRs (mins)” is the average number of minutes for all data items that is less than 5 hours or 300 minutes
2f) “<5HRs Savings“ is exactly the same at 2c, “<8HRs Savings,” except it is for the <5hr data
2g) “Avg Discharge (mins)” is the average number of minutes for all data items
2h) “Count of All Data Items” is a count of all data items
2i) “All Data Savings” is exactly the same at 2c, “<8HRs Savings,” except it is for All data
3) A new tab of data will be added at each time period, whatever that is, day/week/month/year. A user will creates new tab and inserts all data for that time period (format exactly like existing) into that tab.
4) “Discharge Time Dashboard” needs to populate the dashboard with appropriate calculated data for each time period one after another. May need to insert a button to execute calculation, but open to creative ideas. No need to remove old data-tabs. 3 weeks of data (tabs) have been populated for example. In a regular time period, these data tabs would have been inserted weekly and a new line for the dashboard on “Discharge Time Dashboard” tab would populate.
If you experience please help me to solve this As much as you can.
Advance thanks for you help
1) Use “Column I” in “adt4-16 - 4-22” tab to calculate the total number of minutes per data item.
1a) You can convert the text format of ‘1h30m’ to ‘90’ by using this equation (update cell name):
=IF( ISERROR( LEFT(I6,FIND("h",I6)-1)),0, LEFT(I6,FIND("h",I6)-1))*60 + IF(ISERROR(RIGHT(LEFT(I6,FIND("m",I6)-1),2)),0,RIGHT(LEFT(I6,FIND("m",I6)-1),2))
2) Calculate data and fill in “Discharge Time Dashboard” tab cells as labeled.
2a) “<8HR Count of Data Items” is only the count of all data items that are less than 8 hours or 480 minutes
2b) “Average of <8HRs (mins)” is the average number of minutes all data items that is less than 8 hours or 480 minutes
2c) “<8HRs Savings” is calculated by: Baseline cell in $C$3 minus “Average of <8HRs (mins).” The result of this, multiplied by Cost/Hour in $D$1, multiplied by “<8HR Count of Data Items.”
2d) “<5HR Count of Data Items” is only the count of all data items that are less than 5 hours or 300 minutes
2e) “Average of <5HRs (mins)” is the average number of minutes for all data items that is less than 5 hours or 300 minutes
2f) “<5HRs Savings“ is exactly the same at 2c, “<8HRs Savings,” except it is for the <5hr data
2g) “Avg Discharge (mins)” is the average number of minutes for all data items
2h) “Count of All Data Items” is a count of all data items
2i) “All Data Savings” is exactly the same at 2c, “<8HRs Savings,” except it is for All data
3) A new tab of data will be added at each time period, whatever that is, day/week/month/year. A user will creates new tab and inserts all data for that time period (format exactly like existing) into that tab.
4) “Discharge Time Dashboard” needs to populate the dashboard with appropriate calculated data for each time period one after another. May need to insert a button to execute calculation, but open to creative ideas. No need to remove old data-tabs. 3 weeks of data (tabs) have been populated for example. In a regular time period, these data tabs would have been inserted weekly and a new line for the dashboard on “Discharge Time Dashboard” tab would populate.
If you experience please help me to solve this As much as you can.
Advance thanks for you help
