SUMPPRODUCT with tiered pricing not calculating correctly

[jadams80]

I can't quite seem to get this to calculate correctly, any help would be appreciated:

Formula-

=SUMPRODUCT(--(B3>{0,2000,50000,100000,500000,1000000,3000000,5000000}),--(B3-{0,2000,50000,100000,500000,1000000,3000000,5000000}),{0.0033,0,-0.00011,-0.00016,-0.00006,-0.00011,-0.00016,-0.00016})


kWh 586,670.34

kWh Tier Tax Rate per kWh
first 2,000 $0.00330 SUMPRODUCT Calculation
next 48,000 $0.00319 1793.91
next 50,000 $0.00303
next 400,000 $0.00297
next 500,000 $0.00286
next 2,000,000 $0.00270
next 2,000,000 $0.00254

Total tax should be $1,747.10

[JohnTopley]

Try

Please Login or Register  to view this content.