Excel: I want to assign an hour-of-day cost to outages based on when they happened

Question

I have an Excel spreadsheet of outage records with start and end times. I have another spreadsheet that shows how many customers typically use the system during any specific hour of a specific weekday and a cost per customer that gets routed to customer care instead of being handled by self-service. I want to use a complex formula, preferably (rather than VBA, if possible), that can look at the outage time, determine how many minutes in each hour are "outage minutes", how many customers would be impacted during that many minutes of that hour and return a final cost.

For example:

Hourly Customer Volume Table

Hour  Customers
0     50
1     150
2     500

Outage record
Outage start:  0:35 (h24:mm)
Outage end:  2:42  (h24:mm)

Correct answer the forumula(s) should deliver the final total value below:

Hour 0, 25 minutes of outage at customer impact rate of 50/hour,
Hour 1, 60 minutes of outage at customer impact rate of 150/hour,
Hour 2, 42 minutes of outage at customer impact rate of 500/hour,

Sum of (((25/60)*50)+((60/60)*150)+((42/60)*500)) = 520.83 customer impacts.

So far my best idea is to use a whole mess of formulas:

one for each hour to calculate how many minutes of the outage were in that specific hour of the day
another for each hour to multiple the outage minutes in that hour by the customer contact rate for that hour
and then a simple formula to total up all the (hourly-minutes * hourly customer contact rate) sums.

I have reasonable fluency with building formulas in Excel but I certainly am not familiar with all of the options. I've recently had good success with things like sumif/sumifs and I've built more nested if formulas than I can count.

How can I accomplish this "weighted impact cost" calculation with any reasonable level of efficiency?

Excel: I want to assign an hour-of-day cost to outages based on when they happened

Answers (1)

Related Questions