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?