# Excel Formula for AWS Egress pricing

I'm trying to put together a formula that will calculate the cost of egress from an AWS region to the internet. It doesn't have to live update so it's basically just a tiered pricing model.

I found a forum post that suggested trying the `SUMPRODUCT` function in excel and I tried it, but I'm getting different results from the pricing calculator.

The formula I'm trying to model is:

```+-----------------------------------------------+---------------+
| Data Transfer OUT From Amazon EC2 To Internet |               |
+-----------------------------------------------+---------------+
| Up to 1 GB / Month                            | \$0.00 per GB  |
| Next 9.999 TB / Month                         | \$0.09 per GB  |
| Next 40 TB / Month                            | \$0.085 per GB |
| Next 100 TB / Month                           | \$0.07 per GB  |
| Greater than 150 TB / Month                   | \$0.05 per GB  |
+-----------------------------------------------+---------------+
```

The `SUMPRODUCT` formula I tried so far is:

``````=SUMPRODUCT(--(H2>{0,1000000000,10000000000000,410000000000000,1410000000000000,2910000000000000}),--(H2-{0,1000000000,10000000000000,410000000000000,1410000000000000,2910000000000000}),{0,0.00000000009,-0.000000000005,-0.000000000015,-0.00000000002,-0.00000000002})
``````

Where cell H2 contains the number of bytes being egressed.

However, this reports an incorrect result for large values. For example, with an input of 822031 GB (8.22031e+14 bytes) my formula gives a result of \$63,742.09058 where the simple monthly calculator gives a result of \$44,992.66; which is a fairly significant difference.

Is there an easier way to model this pricing scheme in Excel?
Can anyone spot the error in my `SUMPRODUCT` logic?

For now I've decided to go with this formula:

``````=IF(L4>1000000000,
IF(L4>10000000000000,
IF(L4>50000000000000,
IF(L4>150000000000000,
SUM((L4-(1000000000+9999000000000+40000000000000+10000000000000))*.00000000005, 10000000000000*.00000000007, 40000000000000*.000000000085, 9999000000000*.00000000009),
SUM((L4-(1000000000+9999000000000+40000000000000))*.00000000007, 40000000000000*.000000000085, 9999000000000*.00000000009)
),
SUM((L4-(1000000000+9999000000000))*.000000000085, 9999000000000*.00000000009)
),
(L4-1000000000)*.00000000009
),
0
)
``````

It's still not giving me the exact same numbers as the simple monthly calculator, but it's much closer. Using this formula with 8.22031e+14 as input, I get \$43,101.46 which is much closer to the \$44,992.66 expected result.