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?