Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
User Story Point (USP) calculator for Excel.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mv="urn:schemas-microsoft-com:mac:vml" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"><sheetViews><sheetView workbookViewId="0"/></sheetViews><sheetFormatPr customHeight="1" defaultColWidth="14.43" defaultRowHeight="15.75"/><cols><col customWidth="1" min="1" max="1" width="3.0"/><col customWidth="1" min="2" max="2" width="33.43"/><col customWidth="1" min="3" max="3" width="7.0"/><col customWidth="1" min="4" max="4" width="6.14"/><col customWidth="1" min="5" max="5" width="6.0"/><col customWidth="1" min="7" max="7" width="3.0"/><col customWidth="1" min="8" max="8" width="36.0"/><col customWidth="1" min="9" max="9" width="19.43"/><col customWidth="1" min="10" max="10" width="6.14"/><col customWidth="1" min="12" max="12" width="3.0"/><col customWidth="1" min="13" max="13" width="30.57"/><col customWidth="1" min="15" max="15" width="4.57"/><col customWidth="1" min="16" max="16" width="9.14"/></cols><sheetData><row r="1" ht="45.0" customHeight="1"><c r="A1" s="1" t="s"><v>0</v></c></row><row r="2"><c r="A2" s="2"/><c r="B2" s="3" t="s"><v>1</v></c><c r="C2" s="4"/><c r="D2" s="5" t="s"><v>2</v></c><c r="E2" s="5" t="s"><v>3</v></c><c r="F2" s="6"/><c r="G2" s="2"/><c r="H2" s="3" t="s"><v>4</v></c><c r="I2" s="4"/><c r="J2" s="5" t="s"><v>2</v></c><c r="L2" s="3"/><c r="M2" s="3" t="s"><v>5</v></c><c r="N2" s="4"/><c r="P2" s="7" t="s"><v>6</v></c></row><row r="3"><c r="A3" s="8"/><c r="B3" s="9" t="s"><v>7</v></c><c r="C3" s="9"><v>4.0</v></c><c r="D3" s="10"><v>1.5</v></c><c r="E3" s="11"><f t="shared" ref="E3:E10" si="1">C3*D3</f><v>6</v></c><c r="F3" s="12"/><c r="G3" s="13" t="s"><v>8</v></c><c r="H3" s="12" t="s"><v>9</v></c><c r="I3" s="12" t="b"><v>1</v></c><c r="J3" s="14"><v>2.0</v></c><c r="L3" s="15"/><c r="M3" s="12" t="s"><v>10</v></c><c r="N3"><f>C11</f><v>0.785</v></c><c r="P3" s="16"><v>1.0</v></c></row><row r="4"><c r="A4" s="8"/><c r="B4" s="12" t="s"><v>11</v></c><c r="C4" s="12"><v>4.0</v></c><c r="D4" s="14"><v>0.5</v></c><c r="E4" s="17"><f t="shared" si="1"/><v>2</v></c><c r="F4" s="12"/><c r="G4" s="13" t="s"><v>8</v></c><c r="H4" s="12" t="s"><v>12</v></c><c r="I4" s="12" t="b"><v>1</v></c><c r="J4" s="14"><v>2.0</v></c><c r="L4" s="18"/><c r="M4" s="19" t="s"><v>13</v></c><c r="N4" s="4"><f>I16</f><v>15</v></c><c r="P4" s="20"><v>2.0</v></c></row><row r="5"><c r="A5" s="8"/><c r="B5" s="12" t="s"><v>14</v></c><c r="C5" s="12"><v>2.0</v></c><c r="D5" s="14"><v>1.0</v></c><c r="E5" s="17"><f t="shared" si="1"/><v>2</v></c><c r="F5" s="12"/><c r="G5" s="13" t="s"><v>8</v></c><c r="H5" s="12" t="s"><v>15</v></c><c r="I5" s="12" t="b"><v>1</v></c><c r="J5" s="14"><v>2.0</v></c><c r="L5" s="6"/><c r="M5" s="6" t="s"><v>16</v></c><c r="N5" s="21"><f>ROUND((N3*N4), 2)</f><v>11.78</v></c><c r="P5" s="20"><v>3.0</v></c></row><row r="6"><c r="A6" s="8"/><c r="B6" s="22" t="s"><v>17</v></c><c r="C6" s="22"><v>4.0</v></c><c r="D6" s="23"><v>1.0</v></c><c r="E6" s="24"><f t="shared" si="1"/><v>4</v></c><c r="F6" s="12"/><c r="G6" s="25" t="s"><v>18</v></c><c r="H6" s="12" t="s"><v>19</v></c><c r="I6" s="12" t="b"><v>0</v></c><c r="J6" s="14"><v>1.0</v></c><c r="N6" s="26"/><c r="P6" s="20"><v>5.0</v></c></row><row r="7"><c r="A7" s="8"/><c r="B7" s="22" t="s"><v>20</v></c><c r="C7" s="22"><v>5.0</v></c><c r="D7" s="23"><v>1.0</v></c><c r="E7" s="24"><f t="shared" si="1"/><v>5</v></c><c r="F7" s="12"/><c r="G7" s="25" t="s"><v>18</v></c><c r="H7" s="12" t="s"><v>21</v></c><c r="I7" s="12" t="b"><v>0</v></c><c r="J7" s="14"><v>1.0</v></c><c r="P7" s="20"><v>8.0</v></c></row><row r="8"><c r="A8" s="8"/><c r="B8" s="27" t="s"><v>22</v></c><c r="C8" s="27"><v>5.0</v></c><c r="D8" s="28"><v>1.5</v></c><c r="E8" s="29"><f t="shared" si="1"/><v>7.5</v></c><c r="F8" s="12"/><c r="G8" s="25" t="s"><v>18</v></c><c r="H8" s="12" t="s"><v>23</v></c><c r="I8" s="12" t="b"><v>0</v></c><c r="J8" s="14"><v>0.5</v></c><c r="L8" s="30"/><c r="M8" s="30"/><c r="P8" s="20"><v>13.0</v></c></row><row r="9"><c r="A9" s="8"/><c r="B9" s="22" t="s"><v>24</v></c><c r="C9" s="22"><v>3.0</v></c><c r="D9" s="23"><v>-1.0</v></c><c r="E9" s="24"><f t="shared" si="1"/><v>-3</v></c><c r="F9" s="12"/><c r="G9" s="25" t="s"><v>18</v></c><c r="H9" s="12" t="s"><v>25</v></c><c r="I9" s="12" t="b"><v>0</v></c><c r="J9" s="14"><v>1.5</v></c><c r="L9" s="12"/><c r="M9" s="12"/><c r="P9" s="20"><v>21.0</v></c></row><row r="10"><c r="A10" s="31"/><c r="B10" s="19" t="s"><v>26</v></c><c r="C10" s="19"><v>3.0</v></c><c r="D10" s="32"><v>-1.0</v></c><c r="E10" s="33"><f t="shared" si="1"/><v>-3</v></c><c r="F10" s="12"/><c r="G10" s="25" t="s"><v>18</v></c><c r="H10" s="12" t="s"><v>27</v></c><c r="I10" s="12" t="b"><v>0</v></c><c r="J10" s="14"><v>0.5</v></c><c r="L10" s="12"/><c r="M10" s="12"/><c r="P10" s="20"><v>34.0</v></c></row><row r="11"><c r="A11" s="6"/><c r="B11" s="6" t="s"><v>16</v></c><c r="C11" s="34"><f>1.4+(-0.03*((C3*D3)+(C4*D4)+(C5*D5)+(C6*D6)+(C7*D7)+(C8*D8)+(C9*D9)+(C10*D10)))</f><v>0.785</v></c><c r="E11" s="17"><f>sum(E3:E10)</f><v>20.5</v></c><c r="F11" s="12"/><c r="G11" s="25" t="s"><v>18</v></c><c r="H11" s="12" t="s"><v>28</v></c><c r="I11" s="12" t="b"><v>1</v></c><c r="J11" s="14"><v>1.0</v></c><c r="L11" s="3"/><c r="M11" s="3" t="s"><v>29</v></c><c r="N11" s="4"/><c r="P11" s="20"><v>55.0</v></c></row><row r="12"><c r="A12" s="35"/><c r="B12" s="14" t="s"><v>30</v></c><c r="D12" s="12" t="s"><v>31</v></c><c r="E12" s="12"/><c r="F12" s="12"/><c r="G12" s="36" t="s"><v>18</v></c><c r="H12" s="19" t="s"><v>32</v></c><c r="I12" s="19" t="b"><v>0</v></c><c r="J12" s="32"><v>1.0</v></c><c r="L12" s="15"/><c r="M12" s="12" t="s"><v>10</v></c><c r="N12"><f>C11</f><v>0.785</v></c><c r="P12" s="20"><v>89.0</v></c></row><row r="13"><c r="A13" s="35"/><c r="B13" s="14" t="s"><v>33</v></c><c r="F13" s="37"/><c r="G13" s="37"/><c r="H13" s="38" t="s"><v>34</v></c><c r="I13" s="39" t="b"><f>OR(I3,I4,I5)</f><v>1</v></c><c r="J13" s="14"><f>COUNTIF(I3:I5, TRUE)</f><v>3</v></c><c r="L13" s="18"/><c r="M13" s="19" t="s"><v>13</v></c><c r="N13" s="4"><f>I17</f><v>12</v></c><c r="P13" s="40"><v>144.0</v></c></row><row r="14"><c r="F14" s="37"/><c r="G14" s="37"/><c r="H14" s="41" t="s"><v>35</v></c><c r="I14" s="42" t="b"><f>OR(I6:I12)</f><v>1</v></c><c r="J14" s="14"><f>COUNTIF(I6:I12, TRUE)</f><v>1</v></c><c r="L14" s="6"/><c r="M14" s="6" t="s"><v>16</v></c><c r="N14" s="21"><f>ROUND((N12*N13), 2)</f><v>9.42</v></c></row><row r="15"><c r="B15" s="43" t="s"><v>36</v></c><c r="F15" s="37"/><c r="G15" s="37"/><c r="H15" s="44" t="s"><v>37</v></c><c r="I15" s="45" t="b"><f>NOT(OR(I13,I14))</f><v>0</v></c><c r="J15" s="46" t="s"><v>38</v></c><c r="N15" s="26"/></row><row r="16"><c r="B16" s="47" t="s"><v>39</v></c><c r="F16" s="6"/><c r="G16" s="6"/><c r="H16" s="48" t="s"><v>40</v></c><c r="I16" s="49"><f>IF(OR(I13, IF(J14 &gt;2, TRUE, FALSE)), 15 , IF(AND(I14, (J14 &lt; 3)), 10, 5))</f><v>15</v></c><c r="J16" s="50"><f>IF(I3,J3,0)+IF(I4,J4,0)+IF(I5,J5,0)+IF(I6,J6,0)+IF(I7,J7,0)+IF(I8,J8,0)+IF(I9,J9,0)+IF(I10,J10,0)+IF(I11,J11,0)+IF(I12,J12,0)</f><v>7</v></c><c r="M16" s="51" t="s"><v>41</v></c><c r="N16" s="52"><f t="array" ref="N16">INDEX(
FIBO,IF(
N14 &gt;= (
INDEX(FIBO,(MATCH(N14,FIBO,1)),1) + INDEX(FIBO,(MATCH(N14,FIBO,1)+1),1)
)/2,
MATCH(N14,FIBO,1)+1,
MATCH(N14,FIBO,1)
),1
)</f><v>8</v></c></row><row r="17"><c r="F17" s="12"/><c r="G17" s="12"/><c r="H17" s="48" t="s"><v>42</v></c><c r="I17" s="49"><f>5+IF(I3,J3,0)+IF(I4,J4,0)+IF(I5,J5,0)+IF(I6,J6,0)+IF(I7,J7,0)+IF(I8,J8,0)+IF(I9,J9,0)+IF(I9,J10,0)+IF(I10,J11,0)+IF(I11,J12,0)+IF(I12,J12,0)</f><v>12</v></c><c r="M17" s="51" t="s"><v>43</v></c><c r="N17" s="52"><f t="array" ref="N17">INDEX(
FIBO,IF(
N14 &gt;= (
INDEX(FIBO,(MATCH(N14,FIBO,1)),1)
)/2,
MATCH(N14,FIBO,1)+1,
MATCH(N14,FIBO,1)
),1
)</f><v>13</v></c></row><row r="18"><c r="H18" s="53" t="s"><v>44</v></c></row><row r="19"><c r="E19" s="12"/><c r="H19" s="53" t="s"><v>45</v></c><c r="M19" s="47" t="s"><v>46</v></c></row><row r="20" ht="16.5" customHeight="1"><c r="H20" s="54" t="s"><v>47</v></c><c r="M20" s="47" t="s"><v>48</v></c></row><row r="21"><c r="E21" s="35"/><c r="H21" s="55"/><c r="I21" s="55"/></row><row r="22"><c r="E22" s="35"/></row><row r="24"><c r="M24" s="12"/><c r="N24" s="12"/><c r="O24" s="12"/><c r="P24" s="12"/></row><row r="26"><c r="M26" s="12"/></row><row r="27"><c r="M27" s="12"/></row><row r="28"><c r="M28" s="12"/></row><row r="29"><c r="M29" s="12"/></row><row r="30"><c r="M30" s="12"/></row><row r="31"><c r="M31" s="12"/></row><row r="32"><c r="M32" s="12"/></row><row r="33"><c r="M33" s="12"/></row></sheetData><mergeCells count="10"><mergeCell ref="H18:I18"/><mergeCell ref="M20:N20"/><mergeCell ref="M19:N19"/><mergeCell ref="H20:I20"/><mergeCell ref="B16:E16"/><mergeCell ref="B15:E15"/><mergeCell ref="A1:N1"/><mergeCell ref="N5:N6"/><mergeCell ref="N14:N15"/><mergeCell ref="H19:I19"/></mergeCells><dataValidations><dataValidation type="list" allowBlank="1" sqref="I3:I12"><formula1>&quot;TRUE,FALSE&quot;</formula1></dataValidation><dataValidation type="list" allowBlank="1" sqref="C3:C10"><formula1>&quot;0,1,2,3,4,5&quot;</formula1></dataValidation></dataValidations><drawing r:id="rId1"/></worksheet>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.