te')); return $arr; } /* 遍历用户所有主题 * @param $uid 用户ID * @param int $page 页数 * @param int $pagesize 每页记录条数 * @param bool $desc 排序方式 TRUE降序 FALSE升序 * @param string $key 返回的数组用那一列的值作为 key * @param array $col 查询哪些列 */ function thread_tid_find_by_uid($uid, $page = 1, $pagesize = 1000, $desc = TRUE, $key = 'tid', $col = array()) { if (empty($uid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('uid' => $uid), array('tid' => $orderby), $page, $pagesize, $key, $col); return $arr; } // 遍历栏目下tid 支持数组 $fid = array(1,2,3) function thread_tid_find_by_fid($fid, $page = 1, $pagesize = 1000, $desc = TRUE) { if (empty($fid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('fid' => $fid), array('tid' => $orderby), $page, $pagesize, 'tid', array('tid', 'verify_date')); return $arr; } function thread_tid_delete($tid) { if (empty($tid)) return FALSE; $r = thread_tid__delete(array('tid' => $tid)); return $r; } function thread_tid_count() { $n = thread_tid__count(); return $n; } // 统计用户主题数 大数量下严谨使用非主键统计 function thread_uid_count($uid) { $n = thread_tid__count(array('uid' => $uid)); return $n; } // 统计栏目主题数 大数量下严谨使用非主键统计 function thread_fid_count($fid) { $n = thread_tid__count(array('fid' => $fid)); return $n; } ?>excel - Writing a formula that looks at multiple open tickets against systems - Stack Overflow

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>excel - Writing a formula that looks at multiple open tickets against systems - Stack Overflow

    excel - Writing a formula that looks at multiple open tickets against systems - Stack Overflow

    programmeradmin5浏览0评论

    I am trying to write a formula that looks at a register of tickets that calculates the length of time the system was down if multiple tickets were open at the same time.

    For example Ticket 1 for System 1 was open on 16/01/2025 and closed 20/01/2025 Ticket 2 for System 1 was opened on the 17/01/2025 and closed 20/01/2025

    The downtime for the system would be 4 days as that is the difference between the opening date on the oldest ticket and the closing date on the newest however the formula I have is summing the 4 days for ticket 1 and the 3 days for ticket 2 and saying the system was down for 7 days.

       =IF(AU22="","",IF(COUNTIFS('Main Log'!D2:D2000, Dashboard!AU22, 'Main Log'!B2:B2000, "Open")>1,SUMIFS('Main Log'!K2:K2000, 'Main Log'!D2:D2000, Dashboard!AU22,'Main Log'!C2:C2000, MIN('Main Log'!C2:C2000),'Main Log'!C2:C2000, MAX('Main Log'!C2:C2000)),SUMIFS('Main Log'!K2:K2000,'Main Log'!D2:D2000, Dashboard!AU22)
)

    )

    I have edited in some sample data, in the example below the downtime for System 1 should be the difference between 23/01/2025 and 17/02/2025 as they are the unique days. The formula however is telling me System 1 was down for 40 days.

    Ticket Number Ticket Status System Ticket Start Date Ticket Resolution Date System Install Date Age of System at Time of Ticket Raised (Days) Ticket Resolution Time (Days)
    10208 Closed System 1 23/01/2025 12/02/2025 09/04/2024 212 20
    10368 Open System 2 14/02/2025 19/03/2025 28/09/2021 1235 33
    10242 Open System 1 28/01/2025 17/02/2025 09/04/2024 1466 20

    I am trying to write a formula that looks at a register of tickets that calculates the length of time the system was down if multiple tickets were open at the same time.

    For example Ticket 1 for System 1 was open on 16/01/2025 and closed 20/01/2025 Ticket 2 for System 1 was opened on the 17/01/2025 and closed 20/01/2025

    The downtime for the system would be 4 days as that is the difference between the opening date on the oldest ticket and the closing date on the newest however the formula I have is summing the 4 days for ticket 1 and the 3 days for ticket 2 and saying the system was down for 7 days.

       =IF(AU22="","",IF(COUNTIFS('Main Log'!D2:D2000, Dashboard!AU22, 'Main Log'!B2:B2000, "Open")>1,SUMIFS('Main Log'!K2:K2000, 'Main Log'!D2:D2000, Dashboard!AU22,'Main Log'!C2:C2000, MIN('Main Log'!C2:C2000),'Main Log'!C2:C2000, MAX('Main Log'!C2:C2000)),SUMIFS('Main Log'!K2:K2000,'Main Log'!D2:D2000, Dashboard!AU22)
)

    )

    I have edited in some sample data, in the example below the downtime for System 1 should be the difference between 23/01/2025 and 17/02/2025 as they are the unique days. The formula however is telling me System 1 was down for 40 days.

    Ticket Number Ticket Status System Ticket Start Date Ticket Resolution Date System Install Date Age of System at Time of Ticket Raised (Days) Ticket Resolution Time (Days)
    10208 Closed System 1 23/01/2025 12/02/2025 09/04/2024 212 20
    10368 Open System 2 14/02/2025 19/03/2025 28/09/2021 1235 33
    10242 Open System 1 28/01/2025 17/02/2025 09/04/2024 1466 20

    I have managed to tie myself in a knot with this so any help would be appreciated.

    • excel
    • formula
    Share Improve this question edited 2 days ago Strexxin asked 2 days ago StrexxinStrexxin 295 bronze badges 4
    • So, say ticket 2 was closed on 21/01/25, would that mean 5 days? – Excellor Commented 2 days ago
    • Yes exactly, the formula above however would say 8 as it would sum the total time the two tickets were open instead of only calculating the unique days – Strexxin Commented 2 days ago
    • Can you edit in a few rows of sample data as Markdown table – Excellor Commented 2 days ago
    • 1 I have edited in some sample data for you – Strexxin Commented 2 days ago
    Add a comment  | 

    1 Answer 1

    Reset to default 0

    With Excel 365:

    =LET(_min,MINIFS($E$2:E5,$E$2:E5,"<"&F5,$F$2:F5,">"&MIN($F$2:F5),$D$2:D5,$K$1),
_max,MAXIFS($F$2:F5,$F$2:F5,MAXIFS($F$2:F5,$D$2:D5,$K$1)),
_max-_min)

    Without:

    =(MAXIFS($F$2:F5,$F$2:F5,MAXIFS($F$2:F5,$D$2:D5,$K$1))-MINIFS($E$2:E5,$E$2:E5,"<"&F5,$F$2:F5,">"&MIN($F$2:F5),$D$2:D5,$K$1))

    Well they're somewhat the same, but IMO LET() makes it eassier to edit your formula.

    .

    So what it does:

    The _min argument looks for the lowest value, that is lower than your last value, and is part of system 1;

    the _max argument looks for the highest value, that's part of system 1;

    then it's simple substraction.

    K1 is input of system, K2 and K3 are output according to the above formula.

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论