You need to pull out four values from your table to do the pro rata calc.

Z-Card Pricing sheet - excel forum.xlsx