Skip to content

Instantly share code, notes, and snippets.

@JohnLBevan

JohnLBevan/Get-VstsCost.ps1

Last active May 25, 2018 11:13
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save JohnLBevan/9c7a01db5038764db7021d74058911c0 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save JohnLBevan/9c7a01db5038764db7021d74058911c0 to your computer and use it in GitHub Desktop.
Download ZIP
Excel Spreadsheet Formulas to Calculate Pricing for N users based on tiers from https://www.visualstudio.com/team-services/pricing / additional info from https://marketplace.visualstudio.com/items?itemName=ms.vss-vstsuser
Raw
Get-VstsCost.ps1
Function Get-VstsCost {
[CmdletBinding()]
Param (
[Parameter(Mandatory = $true, ValueFromPipeline = $true)]
[ValidateRange(0,1000)]
[Int]$NoOfUsers
)
Begin {
# data from: https://www.visualstudio.com/team-services/pricing/
$Tiers = @{
0 = 0 #just there to help our logic where there are <5 users
5 = 0
10 = 30
20 = 110
50 = 350
100 = 750
200 = 1350
1000 = 6150
1001 = 6150 #outside range; required by logic for when there are 1000 users
}
$PSStandardMembers = [System.Management.Automation.PSMemberInfo[]]@(
(New-Object -TypeName 'System.Management.Automation.PSPropertySet' -ArgumentList (
'DefaultDisplayPropertySet',
[string[]]('TotalCost','AveragePerUserCost')
))
)
}
Process {
$result = [pscustomobject]@{
NoOfUsers = $NoOfUsers
PreviousTierNoOfUsers = [int](($Tiers.Keys | %{[int]$_} | ?{$_ -le $NoOfUsers}) | Measure-Object -Maximum | Select-Object -ExpandProperty Maximum)
NextTierNoOfUsers = [int](@($Tiers.Keys | %{[int]$_} | ?{$_ -gt $NoOfUsers}) | Measure-Object -Minimum | Select-Object -ExpandProperty Minimum)
}
$result | Add-Member -MemberType NoteProperty -Name PreviousTierCost -Value ($Tiers[$result.PreviousTierNoOfUsers])
$result | Add-Member -MemberType NoteProperty -Name NextTierCost -Value ($Tiers[$result.NextTierNoOfUsers])
$result | Add-Member -MemberType NoteProperty -Name CostPerUserForCurrentTier -Value (($result.NextTierCost-$result.PreviousTierCost) / ($result.NextTierNoOfUsers - $result.PreviousTierNoOfUsers))
$result | Add-Member -MemberType NoteProperty -Name NoOfUsersForCurrentTier -Value ($NoOfUsers - $result.PreviousTierNoOfUsers)
$result | Add-Member -MemberType NoteProperty -Name TotalCost -Value ($result.PreviousTierCost + ($result.CostPerUserForCurrentTier * $result.NoOfUsersForCurrentTier))
$result | Add-Member -MemberType NoteProperty -Name AveragePerUserCost -Value ($result.TotalCost / $NoOfUsers)
$result.PSObject.TypeNames.Insert(0,'VSTSCostInfo')
$result | Add-Member -MemberType MemberSet -Name PSStandardMembers -Value $PSStandardMembers
$result
}
}
#example:
#Get-VstsCost 12 #| select *
Raw
sheet1.xml
<?xml version="1.0" encoding="UTF-8"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">
<dimension ref="A1:K42" />
<sheetViews>
<sheetView tabSelected="1" workbookViewId="0">
<pane xSplit="1" ySplit="1" topLeftCell="B2" activePane="bottomRight" state="frozen" />
<selection pane="topRight" activeCell="B1" sqref="B1" />
<selection pane="bottomLeft" activeCell="A2" sqref="A2" />
<selection pane="bottomRight" activeCell="M17" sqref="M17" />
</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="14.4" x14ac:dyDescent="0.3" />
<cols>
<col min="1" max="1" width="8.44140625" style="1" bestFit="1" customWidth="1" />
<col min="2" max="2" width="19.88671875" style="1" bestFit="1" customWidth="1" />
<col min="3" max="3" width="16.44140625" style="1" bestFit="1" customWidth="1" />
<col min="4" max="4" width="9.33203125" style="1" bestFit="1" customWidth="1" />
<col min="5" max="5" width="12.109375" style="1" bestFit="1" customWidth="1" />
<col min="6" max="6" width="14.109375" style="1" customWidth="1" />
<col min="7" max="7" width="16" style="1" customWidth="1" />
<col min="8" max="16384" width="8.88671875" style="1" />
</cols>
<sheetData>
<row r="1" spans="1:11" s="2" customFormat="1" x14ac:dyDescent="0.3">
<c r="A1" s="2" t="s">
<v>0</v>
</c>
<c r="B1" s="2" t="s">
<v>1</v>
</c>
<c r="C1" s="2" t="s">
<v>2</v>
</c>
<c r="D1" s="2" t="s">
<v>3</v>
</c>
<c r="E1" s="2" t="s">
<v>4</v>
</c>
<c r="F1" s="2" t="s">
<v>5</v>
</c>
<c r="G1" s="2" t="s">
<v>6</v>
</c>
<c r="H1" s="2" t="s">
<v>7</v>
</c>
<c r="I1" s="2" t="s">
<v>10</v>
</c>
<c r="J1" s="2" t="s">
<v>8</v>
</c>
<c r="K1" s="2" t="s">
<v>9</v>
</c>
</row>
<row r="2" spans="1:11" x14ac:dyDescent="0.3">
<c r="A2" s="1">
<v>1</v>
</c>
<c r="B2" s="1">
<f t="array" ref="B2">MAX(IF(A2&gt;CostTierUsers,CostTierUsers))</f>
<v>0</v>
</c>
<c r="C2" s="1">
<f t="array" ref="C2">MIN(IF(A2&lt;=CostTierUsers,CostTierUsers))</f>
<v>5</v>
</c>
<c r="D2" s="1">
<f>IFERROR(VLOOKUP(B2,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E2" s="1">
<f>IFERROR(VLOOKUP(C2,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="F2" s="1">
<f>(E2-D2)/(C2-B2)</f>
<v>0</v>
</c>
<c r="G2" s="1">
<f>A2-B2</f>
<v>1</v>
</c>
<c r="H2" s="1">
<f>G2*F2+D2</f>
<v>0</v>
</c>
<c r="I2" s="1">
<f>H2/A2</f>
<v>0</v>
</c>
<c r="J2" s="1">
<f>H2*0.67</f>
<v>0</v>
</c>
<c r="K2" s="1">
<f>I2*0.67</f>
<v>0</v>
</c>
</row>
<row r="3" spans="1:11" x14ac:dyDescent="0.3">
<c r="A3" s="1">
<f>A2+1</f>
<v>2</v>
</c>
<c r="B3" s="1">
<f t="array" ref="B3">MAX(IF(A3&gt;CostTierUsers,CostTierUsers))</f>
<v>0</v>
</c>
<c r="C3" s="1">
<f t="array" ref="C3">MIN(IF(CostTierUsers&gt;=A3,CostTierUsers))</f>
<v>5</v>
</c>
<c r="D3" s="1">
<f>IFERROR(VLOOKUP(B3,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E3" s="1">
<f>IFERROR(VLOOKUP(C3,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="F3" s="1">
<f t="shared" ref="F3:F42" si="0">(E3-D3)/(C3-B3)</f>
<v>0</v>
</c>
<c r="G3" s="1">
<f t="shared" ref="G3:G42" si="1">A3-B3</f>
<v>2</v>
</c>
<c r="H3" s="1">
<f t="shared" ref="H3:H42" si="2">G3*F3+D3</f>
<v>0</v>
</c>
<c r="I3" s="1">
<f t="shared" ref="I3:I42" si="3">H3/A3</f>
<v>0</v>
</c>
<c r="J3" s="1">
<f t="shared" ref="J3:J42" si="4">H3*0.67</f>
<v>0</v>
</c>
<c r="K3" s="1">
<f t="shared" ref="K3:K42" si="5">I3*0.67</f>
<v>0</v>
</c>
</row>
<row r="4" spans="1:11" x14ac:dyDescent="0.3">
<c r="A4" s="1">
<f t="shared" ref="A4:A24" si="6">A3+1</f>
<v>3</v>
</c>
<c r="B4" s="1">
<f t="array" ref="B4">MAX(IF(A4&gt;CostTierUsers,CostTierUsers))</f>
<v>0</v>
</c>
<c r="C4" s="1">
<f t="array" ref="C4">MIN(IF(CostTierUsers&gt;=A4,CostTierUsers))</f>
<v>5</v>
</c>
<c r="D4" s="1">
<f>IFERROR(VLOOKUP(B4,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E4" s="1">
<f>IFERROR(VLOOKUP(C4,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="F4" s="1">
<f t="shared" si="0" />
<v>0</v>
</c>
<c r="G4" s="1">
<f t="shared" si="1" />
<v>3</v>
</c>
<c r="H4" s="1">
<f t="shared" si="2" />
<v>0</v>
</c>
<c r="I4" s="1">
<f t="shared" si="3" />
<v>0</v>
</c>
<c r="J4" s="1">
<f t="shared" si="4" />
<v>0</v>
</c>
<c r="K4" s="1">
<f t="shared" si="5" />
<v>0</v>
</c>
</row>
<row r="5" spans="1:11" x14ac:dyDescent="0.3">
<c r="A5" s="1">
<f t="shared" si="6" />
<v>4</v>
</c>
<c r="B5" s="1">
<f t="array" ref="B5">MAX(IF(A5&gt;CostTierUsers,CostTierUsers))</f>
<v>0</v>
</c>
<c r="C5" s="1">
<f t="array" ref="C5">MIN(IF(CostTierUsers&gt;=A5,CostTierUsers))</f>
<v>5</v>
</c>
<c r="D5" s="1">
<f>IFERROR(VLOOKUP(B5,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E5" s="1">
<f>IFERROR(VLOOKUP(C5,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="F5" s="1">
<f t="shared" si="0" />
<v>0</v>
</c>
<c r="G5" s="1">
<f t="shared" si="1" />
<v>4</v>
</c>
<c r="H5" s="1">
<f t="shared" si="2" />
<v>0</v>
</c>
<c r="I5" s="1">
<f t="shared" si="3" />
<v>0</v>
</c>
<c r="J5" s="1">
<f t="shared" si="4" />
<v>0</v>
</c>
<c r="K5" s="1">
<f t="shared" si="5" />
<v>0</v>
</c>
</row>
<row r="6" spans="1:11" x14ac:dyDescent="0.3">
<c r="A6" s="1">
<f t="shared" si="6" />
<v>5</v>
</c>
<c r="B6" s="1">
<f t="array" ref="B6">MAX(IF(A6&gt;CostTierUsers,CostTierUsers))</f>
<v>0</v>
</c>
<c r="C6" s="1">
<f t="array" ref="C6">MIN(IF(CostTierUsers&gt;=A6,CostTierUsers))</f>
<v>5</v>
</c>
<c r="D6" s="1">
<f>IFERROR(VLOOKUP(B6,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E6" s="1">
<f>IFERROR(VLOOKUP(C6,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="F6" s="1">
<f t="shared" si="0" />
<v>0</v>
</c>
<c r="G6" s="1">
<f t="shared" si="1" />
<v>5</v>
</c>
<c r="H6" s="1">
<f t="shared" si="2" />
<v>0</v>
</c>
<c r="I6" s="1">
<f t="shared" si="3" />
<v>0</v>
</c>
<c r="J6" s="1">
<f t="shared" si="4" />
<v>0</v>
</c>
<c r="K6" s="1">
<f t="shared" si="5" />
<v>0</v>
</c>
</row>
<row r="7" spans="1:11" x14ac:dyDescent="0.3">
<c r="A7" s="1">
<f t="shared" si="6" />
<v>6</v>
</c>
<c r="B7" s="1">
<f t="array" ref="B7">MAX(IF(A7&gt;CostTierUsers,CostTierUsers))</f>
<v>5</v>
</c>
<c r="C7" s="1">
<f t="array" ref="C7">MIN(IF(CostTierUsers&gt;=A7,CostTierUsers))</f>
<v>10</v>
</c>
<c r="D7" s="1">
<f>IFERROR(VLOOKUP(B7,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E7" s="1">
<f>IFERROR(VLOOKUP(C7,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="F7" s="1">
<f t="shared" si="0" />
<v>6</v>
</c>
<c r="G7" s="1">
<f t="shared" si="1" />
<v>1</v>
</c>
<c r="H7" s="1">
<f t="shared" si="2" />
<v>6</v>
</c>
<c r="I7" s="1">
<f t="shared" si="3" />
<v>1</v>
</c>
<c r="J7" s="1">
<f t="shared" si="4" />
<v>4.0200000000000005</v>
</c>
<c r="K7" s="1">
<f t="shared" si="5" />
<v>0.67</v>
</c>
</row>
<row r="8" spans="1:11" x14ac:dyDescent="0.3">
<c r="A8" s="1">
<f t="shared" si="6" />
<v>7</v>
</c>
<c r="B8" s="1">
<f t="array" ref="B8">MAX(IF(A8&gt;CostTierUsers,CostTierUsers))</f>
<v>5</v>
</c>
<c r="C8" s="1">
<f t="array" ref="C8">MIN(IF(CostTierUsers&gt;=A8,CostTierUsers))</f>
<v>10</v>
</c>
<c r="D8" s="1">
<f>IFERROR(VLOOKUP(B8,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E8" s="1">
<f>IFERROR(VLOOKUP(C8,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="F8" s="1">
<f t="shared" si="0" />
<v>6</v>
</c>
<c r="G8" s="1">
<f t="shared" si="1" />
<v>2</v>
</c>
<c r="H8" s="1">
<f t="shared" si="2" />
<v>12</v>
</c>
<c r="I8" s="1">
<f t="shared" si="3" />
<v>1.7142857142857142</v>
</c>
<c r="J8" s="1">
<f t="shared" si="4" />
<v>8.0400000000000009</v>
</c>
<c r="K8" s="1">
<f t="shared" si="5" />
<v>1.1485714285714286</v>
</c>
</row>
<row r="9" spans="1:11" x14ac:dyDescent="0.3">
<c r="A9" s="1">
<f t="shared" si="6" />
<v>8</v>
</c>
<c r="B9" s="1">
<f t="array" ref="B9">MAX(IF(A9&gt;CostTierUsers,CostTierUsers))</f>
<v>5</v>
</c>
<c r="C9" s="1">
<f t="array" ref="C9">MIN(IF(CostTierUsers&gt;=A9,CostTierUsers))</f>
<v>10</v>
</c>
<c r="D9" s="1">
<f>IFERROR(VLOOKUP(B9,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E9" s="1">
<f>IFERROR(VLOOKUP(C9,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="F9" s="1">
<f t="shared" si="0" />
<v>6</v>
</c>
<c r="G9" s="1">
<f t="shared" si="1" />
<v>3</v>
</c>
<c r="H9" s="1">
<f t="shared" si="2" />
<v>18</v>
</c>
<c r="I9" s="1">
<f t="shared" si="3" />
<v>2.25</v>
</c>
<c r="J9" s="1">
<f t="shared" si="4" />
<v>12.06</v>
</c>
<c r="K9" s="1">
<f t="shared" si="5" />
<v>1.5075000000000001</v>
</c>
</row>
<row r="10" spans="1:11" x14ac:dyDescent="0.3">
<c r="A10" s="1">
<f t="shared" si="6" />
<v>9</v>
</c>
<c r="B10" s="1">
<f t="array" ref="B10">MAX(IF(A10&gt;CostTierUsers,CostTierUsers))</f>
<v>5</v>
</c>
<c r="C10" s="1">
<f t="array" ref="C10">MIN(IF(CostTierUsers&gt;=A10,CostTierUsers))</f>
<v>10</v>
</c>
<c r="D10" s="1">
<f>IFERROR(VLOOKUP(B10,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E10" s="1">
<f>IFERROR(VLOOKUP(C10,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="F10" s="1">
<f t="shared" si="0" />
<v>6</v>
</c>
<c r="G10" s="1">
<f t="shared" si="1" />
<v>4</v>
</c>
<c r="H10" s="1">
<f t="shared" si="2" />
<v>24</v>
</c>
<c r="I10" s="1">
<f t="shared" si="3" />
<v>2.6666666666666665</v>
</c>
<c r="J10" s="1">
<f t="shared" si="4" />
<v>16.080000000000002</v>
</c>
<c r="K10" s="1">
<f t="shared" si="5" />
<v>1.7866666666666666</v>
</c>
</row>
<row r="11" spans="1:11" x14ac:dyDescent="0.3">
<c r="A11" s="1">
<f t="shared" si="6" />
<v>10</v>
</c>
<c r="B11" s="1">
<f t="array" ref="B11">MAX(IF(A11&gt;CostTierUsers,CostTierUsers))</f>
<v>5</v>
</c>
<c r="C11" s="1">
<f t="array" ref="C11">MIN(IF(CostTierUsers&gt;=A11,CostTierUsers))</f>
<v>10</v>
</c>
<c r="D11" s="1">
<f>IFERROR(VLOOKUP(B11,CostTier,2,FALSE),0)</f>
<v>0</v>
</c>
<c r="E11" s="1">
<f>IFERROR(VLOOKUP(C11,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="F11" s="1">
<f t="shared" si="0" />
<v>6</v>
</c>
<c r="G11" s="1">
<f t="shared" si="1" />
<v>5</v>
</c>
<c r="H11" s="1">
<f t="shared" si="2" />
<v>30</v>
</c>
<c r="I11" s="1">
<f t="shared" si="3" />
<v>3</v>
</c>
<c r="J11" s="1">
<f t="shared" si="4" />
<v>20.100000000000001</v>
</c>
<c r="K11" s="1">
<f t="shared" si="5" />
<v>2.0100000000000002</v>
</c>
</row>
<row r="12" spans="1:11" x14ac:dyDescent="0.3">
<c r="A12" s="1">
<f t="shared" si="6" />
<v>11</v>
</c>
<c r="B12" s="1">
<f t="array" ref="B12">MAX(IF(A12&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C12" s="1">
<f t="array" ref="C12">MIN(IF(CostTierUsers&gt;=A12,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D12" s="1">
<f>IFERROR(VLOOKUP(B12,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E12" s="1">
<f>IFERROR(VLOOKUP(C12,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F12" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G12" s="1">
<f t="shared" si="1" />
<v>1</v>
</c>
<c r="H12" s="1">
<f t="shared" si="2" />
<v>38</v>
</c>
<c r="I12" s="1">
<f t="shared" si="3" />
<v>3.4545454545454546</v>
</c>
<c r="J12" s="1">
<f t="shared" si="4" />
<v>25.46</v>
</c>
<c r="K12" s="1">
<f t="shared" si="5" />
<v>2.3145454545454549</v>
</c>
</row>
<row r="13" spans="1:11" x14ac:dyDescent="0.3">
<c r="A13" s="1">
<f t="shared" si="6" />
<v>12</v>
</c>
<c r="B13" s="1">
<f t="array" ref="B13">MAX(IF(A13&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C13" s="1">
<f t="array" ref="C13">MIN(IF(CostTierUsers&gt;=A13,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D13" s="1">
<f>IFERROR(VLOOKUP(B13,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E13" s="1">
<f>IFERROR(VLOOKUP(C13,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F13" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G13" s="1">
<f t="shared" si="1" />
<v>2</v>
</c>
<c r="H13" s="1">
<f t="shared" si="2" />
<v>46</v>
</c>
<c r="I13" s="1">
<f t="shared" si="3" />
<v>3.8333333333333335</v>
</c>
<c r="J13" s="1">
<f t="shared" si="4" />
<v>30.82</v>
</c>
<c r="K13" s="1">
<f t="shared" si="5" />
<v>2.5683333333333338</v>
</c>
</row>
<row r="14" spans="1:11" x14ac:dyDescent="0.3">
<c r="A14" s="1">
<f t="shared" si="6" />
<v>13</v>
</c>
<c r="B14" s="1">
<f t="array" ref="B14">MAX(IF(A14&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C14" s="1">
<f t="array" ref="C14">MIN(IF(CostTierUsers&gt;=A14,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D14" s="1">
<f>IFERROR(VLOOKUP(B14,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E14" s="1">
<f>IFERROR(VLOOKUP(C14,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F14" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G14" s="1">
<f t="shared" si="1" />
<v>3</v>
</c>
<c r="H14" s="1">
<f t="shared" si="2" />
<v>54</v>
</c>
<c r="I14" s="1">
<f t="shared" si="3" />
<v>4.1538461538461542</v>
</c>
<c r="J14" s="1">
<f t="shared" si="4" />
<v>36.18</v>
</c>
<c r="K14" s="1">
<f t="shared" si="5" />
<v>2.7830769230769237</v>
</c>
</row>
<row r="15" spans="1:11" x14ac:dyDescent="0.3">
<c r="A15" s="1">
<f t="shared" si="6" />
<v>14</v>
</c>
<c r="B15" s="1">
<f t="array" ref="B15">MAX(IF(A15&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C15" s="1">
<f t="array" ref="C15">MIN(IF(CostTierUsers&gt;=A15,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D15" s="1">
<f>IFERROR(VLOOKUP(B15,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E15" s="1">
<f>IFERROR(VLOOKUP(C15,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F15" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G15" s="1">
<f t="shared" si="1" />
<v>4</v>
</c>
<c r="H15" s="1">
<f t="shared" si="2" />
<v>62</v>
</c>
<c r="I15" s="1">
<f t="shared" si="3" />
<v>4.4285714285714288</v>
</c>
<c r="J15" s="1">
<f t="shared" si="4" />
<v>41.54</v>
</c>
<c r="K15" s="1">
<f t="shared" si="5" />
<v>2.9671428571428575</v>
</c>
</row>
<row r="16" spans="1:11" x14ac:dyDescent="0.3">
<c r="A16" s="1">
<f t="shared" si="6" />
<v>15</v>
</c>
<c r="B16" s="1">
<f t="array" ref="B16">MAX(IF(A16&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C16" s="1">
<f t="array" ref="C16">MIN(IF(CostTierUsers&gt;=A16,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D16" s="1">
<f>IFERROR(VLOOKUP(B16,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E16" s="1">
<f>IFERROR(VLOOKUP(C16,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F16" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G16" s="1">
<f t="shared" si="1" />
<v>5</v>
</c>
<c r="H16" s="1">
<f t="shared" si="2" />
<v>70</v>
</c>
<c r="I16" s="1">
<f t="shared" si="3" />
<v>4.666666666666667</v>
</c>
<c r="J16" s="1">
<f t="shared" si="4" />
<v>46.900000000000006</v>
</c>
<c r="K16" s="1">
<f t="shared" si="5" />
<v>3.1266666666666669</v>
</c>
</row>
<row r="17" spans="1:11" x14ac:dyDescent="0.3">
<c r="A17" s="1">
<f t="shared" si="6" />
<v>16</v>
</c>
<c r="B17" s="1">
<f t="array" ref="B17">MAX(IF(A17&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C17" s="1">
<f t="array" ref="C17">MIN(IF(CostTierUsers&gt;=A17,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D17" s="1">
<f>IFERROR(VLOOKUP(B17,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E17" s="1">
<f>IFERROR(VLOOKUP(C17,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F17" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G17" s="1">
<f t="shared" si="1" />
<v>6</v>
</c>
<c r="H17" s="1">
<f t="shared" si="2" />
<v>78</v>
</c>
<c r="I17" s="1">
<f t="shared" si="3" />
<v>4.875</v>
</c>
<c r="J17" s="1">
<f t="shared" si="4" />
<v>52.260000000000005</v>
</c>
<c r="K17" s="1">
<f t="shared" si="5" />
<v>3.2662500000000003</v>
</c>
</row>
<row r="18" spans="1:11" x14ac:dyDescent="0.3">
<c r="A18" s="1">
<f t="shared" si="6" />
<v>17</v>
</c>
<c r="B18" s="1">
<f t="array" ref="B18">MAX(IF(A18&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C18" s="1">
<f t="array" ref="C18">MIN(IF(CostTierUsers&gt;=A18,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D18" s="1">
<f>IFERROR(VLOOKUP(B18,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E18" s="1">
<f>IFERROR(VLOOKUP(C18,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F18" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G18" s="1">
<f t="shared" si="1" />
<v>7</v>
</c>
<c r="H18" s="1">
<f t="shared" si="2" />
<v>86</v>
</c>
<c r="I18" s="1">
<f t="shared" si="3" />
<v>5.0588235294117645</v>
</c>
<c r="J18" s="1">
<f t="shared" si="4" />
<v>57.620000000000005</v>
</c>
<c r="K18" s="1">
<f t="shared" si="5" />
<v>3.3894117647058826</v>
</c>
</row>
<row r="19" spans="1:11" x14ac:dyDescent="0.3">
<c r="A19" s="1">
<f t="shared" si="6" />
<v>18</v>
</c>
<c r="B19" s="1">
<f t="array" ref="B19">MAX(IF(A19&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C19" s="1">
<f t="array" ref="C19">MIN(IF(CostTierUsers&gt;=A19,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D19" s="1">
<f>IFERROR(VLOOKUP(B19,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E19" s="1">
<f>IFERROR(VLOOKUP(C19,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F19" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G19" s="1">
<f t="shared" si="1" />
<v>8</v>
</c>
<c r="H19" s="1">
<f t="shared" si="2" />
<v>94</v>
</c>
<c r="I19" s="1">
<f t="shared" si="3" />
<v>5.2222222222222223</v>
</c>
<c r="J19" s="1">
<f t="shared" si="4" />
<v>62.980000000000004</v>
</c>
<c r="K19" s="1">
<f t="shared" si="5" />
<v>3.4988888888888892</v>
</c>
</row>
<row r="20" spans="1:11" x14ac:dyDescent="0.3">
<c r="A20" s="1">
<f t="shared" si="6" />
<v>19</v>
</c>
<c r="B20" s="1">
<f t="array" ref="B20">MAX(IF(A20&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C20" s="1">
<f t="array" ref="C20">MIN(IF(CostTierUsers&gt;=A20,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D20" s="1">
<f>IFERROR(VLOOKUP(B20,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E20" s="1">
<f>IFERROR(VLOOKUP(C20,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F20" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G20" s="1">
<f t="shared" si="1" />
<v>9</v>
</c>
<c r="H20" s="1">
<f t="shared" si="2" />
<v>102</v>
</c>
<c r="I20" s="1">
<f t="shared" si="3" />
<v>5.3684210526315788</v>
</c>
<c r="J20" s="1">
<f t="shared" si="4" />
<v>68.34</v>
</c>
<c r="K20" s="1">
<f t="shared" si="5" />
<v>3.5968421052631578</v>
</c>
</row>
<row r="21" spans="1:11" x14ac:dyDescent="0.3">
<c r="A21" s="1">
<f t="shared" si="6" />
<v>20</v>
</c>
<c r="B21" s="1">
<f t="array" ref="B21">MAX(IF(A21&gt;CostTierUsers,CostTierUsers))</f>
<v>10</v>
</c>
<c r="C21" s="1">
<f t="array" ref="C21">MIN(IF(CostTierUsers&gt;=A21,CostTierUsers))</f>
<v>20</v>
</c>
<c r="D21" s="1">
<f>IFERROR(VLOOKUP(B21,CostTier,2,FALSE),0)</f>
<v>30</v>
</c>
<c r="E21" s="1">
<f>IFERROR(VLOOKUP(C21,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="F21" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G21" s="1">
<f t="shared" si="1" />
<v>10</v>
</c>
<c r="H21" s="1">
<f t="shared" si="2" />
<v>110</v>
</c>
<c r="I21" s="1">
<f t="shared" si="3" />
<v>5.5</v>
</c>
<c r="J21" s="1">
<f t="shared" si="4" />
<v>73.7</v>
</c>
<c r="K21" s="1">
<f t="shared" si="5" />
<v>3.6850000000000001</v>
</c>
</row>
<row r="22" spans="1:11" x14ac:dyDescent="0.3">
<c r="A22" s="1">
<f t="shared" si="6" />
<v>21</v>
</c>
<c r="B22" s="1">
<f t="array" ref="B22">MAX(IF(A22&gt;CostTierUsers,CostTierUsers))</f>
<v>20</v>
</c>
<c r="C22" s="1">
<f t="array" ref="C22">MIN(IF(CostTierUsers&gt;=A22,CostTierUsers))</f>
<v>50</v>
</c>
<c r="D22" s="1">
<f>IFERROR(VLOOKUP(B22,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="E22" s="1">
<f>IFERROR(VLOOKUP(C22,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F22" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G22" s="1">
<f t="shared" si="1" />
<v>1</v>
</c>
<c r="H22" s="1">
<f t="shared" si="2" />
<v>118</v>
</c>
<c r="I22" s="1">
<f t="shared" si="3" />
<v>5.6190476190476186</v>
</c>
<c r="J22" s="1">
<f t="shared" si="4" />
<v>79.06</v>
</c>
<c r="K22" s="1">
<f t="shared" si="5" />
<v>3.7647619047619045</v>
</c>
</row>
<row r="23" spans="1:11" x14ac:dyDescent="0.3">
<c r="A23" s="1">
<f t="shared" si="6" />
<v>22</v>
</c>
<c r="B23" s="1">
<f t="array" ref="B23">MAX(IF(A23&gt;CostTierUsers,CostTierUsers))</f>
<v>20</v>
</c>
<c r="C23" s="1">
<f t="array" ref="C23">MIN(IF(CostTierUsers&gt;=A23,CostTierUsers))</f>
<v>50</v>
</c>
<c r="D23" s="1">
<f>IFERROR(VLOOKUP(B23,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="E23" s="1">
<f>IFERROR(VLOOKUP(C23,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F23" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G23" s="1">
<f t="shared" si="1" />
<v>2</v>
</c>
<c r="H23" s="1">
<f t="shared" si="2" />
<v>126</v>
</c>
<c r="I23" s="1">
<f t="shared" si="3" />
<v>5.7272727272727275</v>
</c>
<c r="J23" s="1">
<f t="shared" si="4" />
<v>84.42</v>
</c>
<c r="K23" s="1">
<f t="shared" si="5" />
<v>3.8372727272727278</v>
</c>
</row>
<row r="24" spans="1:11" x14ac:dyDescent="0.3">
<c r="A24" s="1">
<f t="shared" si="6" />
<v>23</v>
</c>
<c r="B24" s="1">
<f t="array" ref="B24">MAX(IF(A24&gt;CostTierUsers,CostTierUsers))</f>
<v>20</v>
</c>
<c r="C24" s="1">
<f t="array" ref="C24">MIN(IF(CostTierUsers&gt;=A24,CostTierUsers))</f>
<v>50</v>
</c>
<c r="D24" s="1">
<f>IFERROR(VLOOKUP(B24,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="E24" s="1">
<f>IFERROR(VLOOKUP(C24,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F24" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G24" s="1">
<f t="shared" si="1" />
<v>3</v>
</c>
<c r="H24" s="1">
<f t="shared" si="2" />
<v>134</v>
</c>
<c r="I24" s="1">
<f t="shared" si="3" />
<v>5.8260869565217392</v>
</c>
<c r="J24" s="1">
<f t="shared" si="4" />
<v>89.78</v>
</c>
<c r="K24" s="1">
<f t="shared" si="5" />
<v>3.9034782608695657</v>
</c>
</row>
<row r="25" spans="1:11" x14ac:dyDescent="0.3">
<c r="A25" s="1">
<v>48</v>
</c>
<c r="B25" s="1">
<f t="array" ref="B25">MAX(IF(A25&gt;CostTierUsers,CostTierUsers))</f>
<v>20</v>
</c>
<c r="C25" s="1">
<f t="array" ref="C25">MIN(IF(CostTierUsers&gt;=A25,CostTierUsers))</f>
<v>50</v>
</c>
<c r="D25" s="1">
<f>IFERROR(VLOOKUP(B25,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="E25" s="1">
<f>IFERROR(VLOOKUP(C25,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F25" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G25" s="1">
<f t="shared" si="1" />
<v>28</v>
</c>
<c r="H25" s="1">
<f t="shared" si="2" />
<v>334</v>
</c>
<c r="I25" s="1">
<f t="shared" si="3" />
<v>6.958333333333333</v>
</c>
<c r="J25" s="1">
<f t="shared" si="4" />
<v>223.78</v>
</c>
<c r="K25" s="1">
<f t="shared" si="5" />
<v>4.6620833333333334</v>
</c>
</row>
<row r="26" spans="1:11" x14ac:dyDescent="0.3">
<c r="A26" s="1">
<f>A25+1</f>
<v>49</v>
</c>
<c r="B26" s="1">
<f t="array" ref="B26">MAX(IF(A26&gt;CostTierUsers,CostTierUsers))</f>
<v>20</v>
</c>
<c r="C26" s="1">
<f t="array" ref="C26">MIN(IF(CostTierUsers&gt;=A26,CostTierUsers))</f>
<v>50</v>
</c>
<c r="D26" s="1">
<f>IFERROR(VLOOKUP(B26,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="E26" s="1">
<f>IFERROR(VLOOKUP(C26,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F26" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G26" s="1">
<f t="shared" si="1" />
<v>29</v>
</c>
<c r="H26" s="1">
<f t="shared" si="2" />
<v>342</v>
</c>
<c r="I26" s="1">
<f t="shared" si="3" />
<v>6.9795918367346941</v>
</c>
<c r="J26" s="1">
<f t="shared" si="4" />
<v>229.14000000000001</v>
</c>
<c r="K26" s="1">
<f t="shared" si="5" />
<v>4.6763265306122452</v>
</c>
</row>
<row r="27" spans="1:11" x14ac:dyDescent="0.3">
<c r="A27" s="1">
<f t="shared" ref="A27:A29" si="7">A26+1</f>
<v>50</v>
</c>
<c r="B27" s="1">
<f t="array" ref="B27">MAX(IF(A27&gt;CostTierUsers,CostTierUsers))</f>
<v>20</v>
</c>
<c r="C27" s="1">
<f t="array" ref="C27">MIN(IF(CostTierUsers&gt;=A27,CostTierUsers))</f>
<v>50</v>
</c>
<c r="D27" s="1">
<f>IFERROR(VLOOKUP(B27,CostTier,2,FALSE),0)</f>
<v>110</v>
</c>
<c r="E27" s="1">
<f>IFERROR(VLOOKUP(C27,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F27" s="1">
<f t="shared" si="0" />
<v>8</v>
</c>
<c r="G27" s="1">
<f t="shared" si="1" />
<v>30</v>
</c>
<c r="H27" s="1">
<f t="shared" si="2" />
<v>350</v>
</c>
<c r="I27" s="1">
<f t="shared" si="3" />
<v>7</v>
</c>
<c r="J27" s="1">
<f t="shared" si="4" />
<v>234.5</v>
</c>
<c r="K27" s="1">
<f t="shared" si="5" />
<v>4.6900000000000004</v>
</c>
</row>
<row r="28" spans="1:11" x14ac:dyDescent="0.3">
<c r="A28" s="1">
<f t="shared" si="7" />
<v>51</v>
</c>
<c r="B28" s="1">
<f t="array" ref="B28">MAX(IF(A28&gt;CostTierUsers,CostTierUsers))</f>
<v>50</v>
</c>
<c r="C28" s="1">
<f t="array" ref="C28">MIN(IF(CostTierUsers&gt;=A28,CostTierUsers))</f>
<v>100</v>
</c>
<c r="D28" s="1">
<f>IFERROR(VLOOKUP(B28,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E28" s="1">
<f>IFERROR(VLOOKUP(C28,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F28" s="1">
<f t="shared" si="0" />
<v>0</v>
</c>
<c r="G28" s="1">
<f t="shared" si="1" />
<v>1</v>
</c>
<c r="H28" s="1">
<f t="shared" si="2" />
<v>350</v>
</c>
<c r="I28" s="1">
<f t="shared" si="3" />
<v>6.8627450980392153</v>
</c>
<c r="J28" s="1">
<f t="shared" si="4" />
<v>234.5</v>
</c>
<c r="K28" s="1">
<f t="shared" si="5" />
<v>4.5980392156862742</v>
</c>
</row>
<row r="29" spans="1:11" x14ac:dyDescent="0.3">
<c r="A29" s="1">
<f t="shared" si="7" />
<v>52</v>
</c>
<c r="B29" s="1">
<f t="array" ref="B29">MAX(IF(A29&gt;CostTierUsers,CostTierUsers))</f>
<v>50</v>
</c>
<c r="C29" s="1">
<f t="array" ref="C29">MIN(IF(CostTierUsers&gt;=A29,CostTierUsers))</f>
<v>100</v>
</c>
<c r="D29" s="1">
<f>IFERROR(VLOOKUP(B29,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E29" s="1">
<f>IFERROR(VLOOKUP(C29,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F29" s="1">
<f t="shared" si="0" />
<v>0</v>
</c>
<c r="G29" s="1">
<f t="shared" si="1" />
<v>2</v>
</c>
<c r="H29" s="1">
<f t="shared" si="2" />
<v>350</v>
</c>
<c r="I29" s="1">
<f t="shared" si="3" />
<v>6.7307692307692308</v>
</c>
<c r="J29" s="1">
<f t="shared" si="4" />
<v>234.5</v>
</c>
<c r="K29" s="1">
<f t="shared" si="5" />
<v>4.509615384615385</v>
</c>
</row>
<row r="30" spans="1:11" x14ac:dyDescent="0.3">
<c r="A30" s="1">
<v>98</v>
</c>
<c r="B30" s="1">
<f t="array" ref="B30">MAX(IF(A30&gt;CostTierUsers,CostTierUsers))</f>
<v>50</v>
</c>
<c r="C30" s="1">
<f t="array" ref="C30">MIN(IF(CostTierUsers&gt;=A30,CostTierUsers))</f>
<v>100</v>
</c>
<c r="D30" s="1">
<f>IFERROR(VLOOKUP(B30,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E30" s="1">
<f>IFERROR(VLOOKUP(C30,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F30" s="1">
<f t="shared" si="0" />
<v>0</v>
</c>
<c r="G30" s="1">
<f t="shared" si="1" />
<v>48</v>
</c>
<c r="H30" s="1">
<f t="shared" si="2" />
<v>350</v>
</c>
<c r="I30" s="1">
<f t="shared" si="3" />
<v>3.5714285714285716</v>
</c>
<c r="J30" s="1">
<f t="shared" si="4" />
<v>234.5</v>
</c>
<c r="K30" s="1">
<f t="shared" si="5" />
<v>2.3928571428571432</v>
</c>
</row>
<row r="31" spans="1:11" x14ac:dyDescent="0.3">
<c r="A31" s="1">
<f>A30+1</f>
<v>99</v>
</c>
<c r="B31" s="1">
<f t="array" ref="B31">MAX(IF(A31&gt;CostTierUsers,CostTierUsers))</f>
<v>50</v>
</c>
<c r="C31" s="1">
<f t="array" ref="C31">MIN(IF(CostTierUsers&gt;=A31,CostTierUsers))</f>
<v>100</v>
</c>
<c r="D31" s="1">
<f>IFERROR(VLOOKUP(B31,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E31" s="1">
<f>IFERROR(VLOOKUP(C31,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F31" s="1">
<f t="shared" si="0" />
<v>0</v>
</c>
<c r="G31" s="1">
<f t="shared" si="1" />
<v>49</v>
</c>
<c r="H31" s="1">
<f t="shared" si="2" />
<v>350</v>
</c>
<c r="I31" s="1">
<f t="shared" si="3" />
<v>3.5353535353535355</v>
</c>
<c r="J31" s="1">
<f t="shared" si="4" />
<v>234.5</v>
</c>
<c r="K31" s="1">
<f t="shared" si="5" />
<v>2.368686868686869</v>
</c>
</row>
<row r="32" spans="1:11" x14ac:dyDescent="0.3">
<c r="A32" s="1">
<f t="shared" ref="A32:A34" si="8">A31+1</f>
<v>100</v>
</c>
<c r="B32" s="1">
<f t="array" ref="B32">MAX(IF(A32&gt;CostTierUsers,CostTierUsers))</f>
<v>50</v>
</c>
<c r="C32" s="1">
<f t="array" ref="C32">MIN(IF(CostTierUsers&gt;=A32,CostTierUsers))</f>
<v>100</v>
</c>
<c r="D32" s="1">
<f>IFERROR(VLOOKUP(B32,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E32" s="1">
<f>IFERROR(VLOOKUP(C32,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="F32" s="1">
<f t="shared" si="0" />
<v>0</v>
</c>
<c r="G32" s="1">
<f t="shared" si="1" />
<v>50</v>
</c>
<c r="H32" s="1">
<f t="shared" si="2" />
<v>350</v>
</c>
<c r="I32" s="1">
<f t="shared" si="3" />
<v>3.5</v>
</c>
<c r="J32" s="1">
<f t="shared" si="4" />
<v>234.5</v>
</c>
<c r="K32" s="1">
<f t="shared" si="5" />
<v>2.3450000000000002</v>
</c>
</row>
<row r="33" spans="1:11" x14ac:dyDescent="0.3">
<c r="A33" s="1">
<f t="shared" si="8" />
<v>101</v>
</c>
<c r="B33" s="1">
<f t="array" ref="B33">MAX(IF(A33&gt;CostTierUsers,CostTierUsers))</f>
<v>100</v>
</c>
<c r="C33" s="1">
<f t="array" ref="C33">MIN(IF(CostTierUsers&gt;=A33,CostTierUsers))</f>
<v>200</v>
</c>
<c r="D33" s="1">
<f>IFERROR(VLOOKUP(B33,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E33" s="1">
<f>IFERROR(VLOOKUP(C33,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="F33" s="1">
<f t="shared" si="0" />
<v>4</v>
</c>
<c r="G33" s="1">
<f t="shared" si="1" />
<v>1</v>
</c>
<c r="H33" s="1">
<f t="shared" si="2" />
<v>354</v>
</c>
<c r="I33" s="1">
<f t="shared" si="3" />
<v>3.504950495049505</v>
</c>
<c r="J33" s="1">
<f t="shared" si="4" />
<v>237.18</v>
</c>
<c r="K33" s="1">
<f t="shared" si="5" />
<v>2.3483168316831686</v>
</c>
</row>
<row r="34" spans="1:11" x14ac:dyDescent="0.3">
<c r="A34" s="1">
<f t="shared" si="8" />
<v>102</v>
</c>
<c r="B34" s="1">
<f t="array" ref="B34">MAX(IF(A34&gt;CostTierUsers,CostTierUsers))</f>
<v>100</v>
</c>
<c r="C34" s="1">
<f t="array" ref="C34">MIN(IF(CostTierUsers&gt;=A34,CostTierUsers))</f>
<v>200</v>
</c>
<c r="D34" s="1">
<f>IFERROR(VLOOKUP(B34,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E34" s="1">
<f>IFERROR(VLOOKUP(C34,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="F34" s="1">
<f t="shared" si="0" />
<v>4</v>
</c>
<c r="G34" s="1">
<f t="shared" si="1" />
<v>2</v>
</c>
<c r="H34" s="1">
<f t="shared" si="2" />
<v>358</v>
</c>
<c r="I34" s="1">
<f t="shared" si="3" />
<v>3.5098039215686274</v>
</c>
<c r="J34" s="1">
<f t="shared" si="4" />
<v>239.86</v>
</c>
<c r="K34" s="1">
<f t="shared" si="5" />
<v>2.3515686274509804</v>
</c>
</row>
<row r="35" spans="1:11" x14ac:dyDescent="0.3">
<c r="A35" s="1">
<v>198</v>
</c>
<c r="B35" s="1">
<f t="array" ref="B35">MAX(IF(A35&gt;CostTierUsers,CostTierUsers))</f>
<v>100</v>
</c>
<c r="C35" s="1">
<f t="array" ref="C35">MIN(IF(CostTierUsers&gt;=A35,CostTierUsers))</f>
<v>200</v>
</c>
<c r="D35" s="1">
<f>IFERROR(VLOOKUP(B35,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E35" s="1">
<f>IFERROR(VLOOKUP(C35,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="F35" s="1">
<f t="shared" si="0" />
<v>4</v>
</c>
<c r="G35" s="1">
<f t="shared" si="1" />
<v>98</v>
</c>
<c r="H35" s="1">
<f t="shared" si="2" />
<v>742</v>
</c>
<c r="I35" s="1">
<f t="shared" si="3" />
<v>3.7474747474747474</v>
</c>
<c r="J35" s="1">
<f t="shared" si="4" />
<v>497.14000000000004</v>
</c>
<c r="K35" s="1">
<f t="shared" si="5" />
<v>2.510808080808081</v>
</c>
</row>
<row r="36" spans="1:11" x14ac:dyDescent="0.3">
<c r="A36" s="1">
<f>A35+1</f>
<v>199</v>
</c>
<c r="B36" s="1">
<f t="array" ref="B36">MAX(IF(A36&gt;CostTierUsers,CostTierUsers))</f>
<v>100</v>
</c>
<c r="C36" s="1">
<f t="array" ref="C36">MIN(IF(CostTierUsers&gt;=A36,CostTierUsers))</f>
<v>200</v>
</c>
<c r="D36" s="1">
<f>IFERROR(VLOOKUP(B36,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E36" s="1">
<f>IFERROR(VLOOKUP(C36,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="F36" s="1">
<f t="shared" si="0" />
<v>4</v>
</c>
<c r="G36" s="1">
<f t="shared" si="1" />
<v>99</v>
</c>
<c r="H36" s="1">
<f t="shared" si="2" />
<v>746</v>
</c>
<c r="I36" s="1">
<f t="shared" si="3" />
<v>3.7487437185929648</v>
</c>
<c r="J36" s="1">
<f t="shared" si="4" />
<v>499.82000000000005</v>
</c>
<c r="K36" s="1">
<f t="shared" si="5" />
<v>2.5116582914572865</v>
</c>
</row>
<row r="37" spans="1:11" x14ac:dyDescent="0.3">
<c r="A37" s="1">
<f t="shared" ref="A37:A39" si="9">A36+1</f>
<v>200</v>
</c>
<c r="B37" s="1">
<f t="array" ref="B37">MAX(IF(A37&gt;CostTierUsers,CostTierUsers))</f>
<v>100</v>
</c>
<c r="C37" s="1">
<f t="array" ref="C37">MIN(IF(CostTierUsers&gt;=A37,CostTierUsers))</f>
<v>200</v>
</c>
<c r="D37" s="1">
<f>IFERROR(VLOOKUP(B37,CostTier,2,FALSE),0)</f>
<v>350</v>
</c>
<c r="E37" s="1">
<f>IFERROR(VLOOKUP(C37,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="F37" s="1">
<f t="shared" si="0" />
<v>4</v>
</c>
<c r="G37" s="1">
<f t="shared" si="1" />
<v>100</v>
</c>
<c r="H37" s="1">
<f t="shared" si="2" />
<v>750</v>
</c>
<c r="I37" s="1">
<f t="shared" si="3" />
<v>3.75</v>
</c>
<c r="J37" s="1">
<f t="shared" si="4" />
<v>502.50000000000006</v>
</c>
<c r="K37" s="1">
<f t="shared" si="5" />
<v>2.5125000000000002</v>
</c>
</row>
<row r="38" spans="1:11" x14ac:dyDescent="0.3">
<c r="A38" s="1">
<f t="shared" si="9" />
<v>201</v>
</c>
<c r="B38" s="1">
<f t="array" ref="B38">MAX(IF(A38&gt;CostTierUsers,CostTierUsers))</f>
<v>200</v>
</c>
<c r="C38" s="1">
<f t="array" ref="C38">MIN(IF(CostTierUsers&gt;=A38,CostTierUsers))</f>
<v>1000</v>
</c>
<c r="D38" s="1">
<f>IFERROR(VLOOKUP(B38,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="E38" s="1">
<f>IFERROR(VLOOKUP(C38,CostTier,2,FALSE),0)</f>
<v>1350</v>
</c>
<c r="F38" s="1">
<f t="shared" si="0" />
<v>0.75</v>
</c>
<c r="G38" s="1">
<f t="shared" si="1" />
<v>1</v>
</c>
<c r="H38" s="1">
<f t="shared" si="2" />
<v>750.75</v>
</c>
<c r="I38" s="1">
<f t="shared" si="3" />
<v>3.7350746268656718</v>
</c>
<c r="J38" s="1">
<f t="shared" si="4" />
<v>503.00250000000005</v>
</c>
<c r="K38" s="1">
<f t="shared" si="5" />
<v>2.5025000000000004</v>
</c>
</row>
<row r="39" spans="1:11" x14ac:dyDescent="0.3">
<c r="A39" s="1">
<f t="shared" si="9" />
<v>202</v>
</c>
<c r="B39" s="1">
<f t="array" ref="B39">MAX(IF(A39&gt;CostTierUsers,CostTierUsers))</f>
<v>200</v>
</c>
<c r="C39" s="1">
<f t="array" ref="C39">MIN(IF(CostTierUsers&gt;=A39,CostTierUsers))</f>
<v>1000</v>
</c>
<c r="D39" s="1">
<f>IFERROR(VLOOKUP(B39,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="E39" s="1">
<f>IFERROR(VLOOKUP(C39,CostTier,2,FALSE),0)</f>
<v>1350</v>
</c>
<c r="F39" s="1">
<f t="shared" si="0" />
<v>0.75</v>
</c>
<c r="G39" s="1">
<f t="shared" si="1" />
<v>2</v>
</c>
<c r="H39" s="1">
<f t="shared" si="2" />
<v>751.5</v>
</c>
<c r="I39" s="1">
<f t="shared" si="3" />
<v>3.7202970297029703</v>
</c>
<c r="J39" s="1">
<f t="shared" si="4" />
<v>503.50500000000005</v>
</c>
<c r="K39" s="1">
<f t="shared" si="5" />
<v>2.49259900990099</v>
</c>
</row>
<row r="40" spans="1:11" x14ac:dyDescent="0.3">
<c r="A40" s="1">
<v>998</v>
</c>
<c r="B40" s="1">
<f t="array" ref="B40">MAX(IF(A40&gt;CostTierUsers,CostTierUsers))</f>
<v>200</v>
</c>
<c r="C40" s="1">
<f t="array" ref="C40">MIN(IF(CostTierUsers&gt;=A40,CostTierUsers))</f>
<v>1000</v>
</c>
<c r="D40" s="1">
<f>IFERROR(VLOOKUP(B40,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="E40" s="1">
<f>IFERROR(VLOOKUP(C40,CostTier,2,FALSE),0)</f>
<v>1350</v>
</c>
<c r="F40" s="1">
<f t="shared" si="0" />
<v>0.75</v>
</c>
<c r="G40" s="1">
<f t="shared" si="1" />
<v>798</v>
</c>
<c r="H40" s="1">
<f t="shared" si="2" />
<v>1348.5</v>
</c>
<c r="I40" s="1">
<f t="shared" si="3" />
<v>1.3512024048096192</v>
</c>
<c r="J40" s="1">
<f t="shared" si="4" />
<v>903.495</v>
</c>
<c r="K40" s="1">
<f t="shared" si="5" />
<v>0.90530561122244491</v>
</c>
</row>
<row r="41" spans="1:11" x14ac:dyDescent="0.3">
<c r="A41" s="1">
<f>A40+1</f>
<v>999</v>
</c>
<c r="B41" s="1">
<f t="array" ref="B41">MAX(IF(A41&gt;CostTierUsers,CostTierUsers))</f>
<v>200</v>
</c>
<c r="C41" s="1">
<f t="array" ref="C41">MIN(IF(CostTierUsers&gt;=A41,CostTierUsers))</f>
<v>1000</v>
</c>
<c r="D41" s="1">
<f>IFERROR(VLOOKUP(B41,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="E41" s="1">
<f>IFERROR(VLOOKUP(C41,CostTier,2,FALSE),0)</f>
<v>1350</v>
</c>
<c r="F41" s="1">
<f t="shared" si="0" />
<v>0.75</v>
</c>
<c r="G41" s="1">
<f t="shared" si="1" />
<v>799</v>
</c>
<c r="H41" s="1">
<f t="shared" si="2" />
<v>1349.25</v>
</c>
<c r="I41" s="1">
<f t="shared" si="3" />
<v>1.3506006006006006</v>
</c>
<c r="J41" s="1">
<f t="shared" si="4" />
<v>903.99750000000006</v>
</c>
<c r="K41" s="1">
<f t="shared" si="5" />
<v>0.90490240240240249</v>
</c>
</row>
<row r="42" spans="1:11" x14ac:dyDescent="0.3">
<c r="A42" s="1">
<f t="shared" ref="A42" si="10">A41+1</f>
<v>1000</v>
</c>
<c r="B42" s="1">
<f t="array" ref="B42">MAX(IF(A42&gt;CostTierUsers,CostTierUsers))</f>
<v>200</v>
</c>
<c r="C42" s="1">
<f t="array" ref="C42">MIN(IF(CostTierUsers&gt;=A42,CostTierUsers))</f>
<v>1000</v>
</c>
<c r="D42" s="1">
<f>IFERROR(VLOOKUP(B42,CostTier,2,FALSE),0)</f>
<v>750</v>
</c>
<c r="E42" s="1">
<f>IFERROR(VLOOKUP(C42,CostTier,2,FALSE),0)</f>
<v>1350</v>
</c>
<c r="F42" s="1">
<f t="shared" si="0" />
<v>0.75</v>
</c>
<c r="G42" s="1">
<f t="shared" si="1" />
<v>800</v>
</c>
<c r="H42" s="1">
<f t="shared" si="2" />
<v>1350</v>
</c>
<c r="I42" s="1">
<f t="shared" si="3" />
<v>1.35</v>
</c>
<c r="J42" s="1">
<f t="shared" si="4" />
<v>904.5</v>
</c>
<c r="K42" s="1">
<f t="shared" si="5" />
<v>0.90450000000000008</v>
</c>
</row>
</sheetData>
<conditionalFormatting sqref="F1:F1048576">
<cfRule type="colorScale" priority="5">
<colorScale>
<cfvo type="min" />
<cfvo type="percentile" val="50" />
<cfvo type="max" />
<color rgb="FF63BE7B" />
<color rgb="FFFCFCFF" />
<color rgb="FFF8696B" />
</colorScale>
</cfRule>
</conditionalFormatting>
<conditionalFormatting sqref="H1:H1048576">
<cfRule type="colorScale" priority="4">
<colorScale>
<cfvo type="min" />
<cfvo type="percentile" val="50" />
<cfvo type="max" />
<color rgb="FF63BE7B" />
<color rgb="FFFCFCFF" />
<color rgb="FFF8696B" />
</colorScale>
</cfRule>
</conditionalFormatting>
<conditionalFormatting sqref="I1:I1048576">
<cfRule type="colorScale" priority="3">
<colorScale>
<cfvo type="min" />
<cfvo type="percentile" val="50" />
<cfvo type="max" />
<color rgb="FF63BE7B" />
<color rgb="FFFCFCFF" />
<color rgb="FFF8696B" />
</colorScale>
</cfRule>
</conditionalFormatting>
<conditionalFormatting sqref="J1:J1048576">
<cfRule type="colorScale" priority="2">
<colorScale>
<cfvo type="min" />
<cfvo type="percentile" val="50" />
<cfvo type="max" />
<color rgb="FF63BE7B" />
<color rgb="FFFCFCFF" />
<color rgb="FFF8696B" />
</colorScale>
</cfRule>
</conditionalFormatting>
<conditionalFormatting sqref="K1:K1048576">
<cfRule type="colorScale" priority="1">
<colorScale>
<cfvo type="min" />
<cfvo type="percentile" val="50" />
<cfvo type="max" />
<color rgb="FF63BE7B" />
<color rgb="FFFCFCFF" />
<color rgb="FFF8696B" />
</colorScale>
</cfRule>
</conditionalFormatting>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />
<pageSetup paperSize="9" orientation="portrait" r:id="rId1" />
</worksheet>
Raw
sheet2.xml
<?xml version="1.0" encoding="UTF-8"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">
<dimension ref="A1:B7" />
<sheetViews>
<sheetView workbookViewId="0">
<selection activeCell="A5" sqref="A5" />
</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="14.4" x14ac:dyDescent="0.3" />
<sheetData>
<row r="1" spans="1:2" x14ac:dyDescent="0.3">
<c r="A1">
<v>5</v>
</c>
<c r="B1">
<v>0</v>
</c>
</row>
<row r="2" spans="1:2" x14ac:dyDescent="0.3">
<c r="A2">
<v>10</v>
</c>
<c r="B2">
<v>30</v>
</c>
</row>
<row r="3" spans="1:2" x14ac:dyDescent="0.3">
<c r="A3">
<v>20</v>
</c>
<c r="B3">
<v>110</v>
</c>
</row>
<row r="4" spans="1:2" x14ac:dyDescent="0.3">
<c r="A4">
<v>50</v>
</c>
<c r="B4">
<v>350</v>
</c>
</row>
<row r="5" spans="1:2" x14ac:dyDescent="0.3">
<c r="A5">
<v>100</v>
</c>
<c r="B5">
<v>350</v>
</c>
</row>
<row r="6" spans="1:2" x14ac:dyDescent="0.3">
<c r="A6">
<v>200</v>
</c>
<c r="B6">
<v>750</v>
</c>
</row>
<row r="7" spans="1:2" x14ac:dyDescent="0.3">
<c r="A7">
<v>1000</v>
</c>
<c r="B7">
<v>1350</v>
</c>
</row>
</sheetData>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />
</worksheet>
Raw
workbook.xml
<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" mc:Ignorable="x15">
<fileVersion appName="xl" lastEdited="7" lowestEdited="7" rupBuild="18431" />
<workbookPr defaultThemeVersion="166925" />
<mc:AlternateContent>
<mc:Choice Requires="x15">
<x15ac:absPath xmlns:x15ac="http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac" url="C:\Temp\DeleteDaily\" />
</mc:Choice>
</mc:AlternateContent>
<bookViews>
<workbookView xWindow="0" yWindow="0" windowWidth="23040" windowHeight="8940" />
</bookViews>
<sheets>
<sheet name="Sheet1" sheetId="1" r:id="rId1" />
<sheet name="Sheet2" sheetId="2" r:id="rId2" />
</sheets>
<definedNames>
<definedName name="CostTier">Sheet2!$A$1:$B$7</definedName>
<definedName name="CostTierMaxUsers">Sheet2!$A$7</definedName>
<definedName name="CostTierUsers">Sheet2!$A$1:$A$7</definedName>
</definedNames>
<calcPr calcId="171027" />
<extLst>
<ext uri="{140A7094-0E35-4892-8432-C4D2E57EDEB5}">
<x15:workbookPr chartTrackingRefBase="1" />
</ext>
</extLst>
</workbook>
@JohnLBevan

JohnLBevan commented May 25, 2018

Copy link
Copy Markdown
Author

This figures out the cost per user for VSTS licenses based on the tier information at https://www.visualstudio.com/team-services/pricing/. i.e. the information here says that it's $110 for 20 users; but doesn't say how much it is for 19...

For 19 users it's $30 for 10 users, plus 9 * $8 for those up to the 20 mark. That $8 is calculated as the difference between the tiers divided by the number of users; i.e. ($110 - $30) / (20 - 10) => $80/10 => $8.

Similarly for 198 users we get ($1350 - $750) / (200 - 100) => $600/100 => $6. 198 = 100 + 98. So we pay $750 + (98 * $6) = $750 + $588 = $1338.

@JohnLBevan

JohnLBevan commented May 25, 2018

Copy link
Copy Markdown
Author

Added a powershell version for anyone wanting to see the logic more clearly. This version has some hidden properties in the result object which can be viewed to see the workings behind the end results.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment