Create monthly and yearly costs based on 2 columns

[armyofda12mnkeys]

Hello,

I have a Excel sheet like below for my subscriptions and want to figure out Monthly and Total Yearly costs. Some services are paid 12months  in advance vs 6 and some are just monthly. I guess I could create another column that just lists the monthly cost and add those but thought it would be interesting to see how can make a dynamic formula that is calculated based on the value of the time_type field.

so for total costs it would look like this in a scripting lang pretend for yearly and monthly costs:

var yearly_total = 0;

foreach( item in items )

  if (item->time_type=='1month') { item_yearly_cost = item->cost * 12; }

  else if (item->time_type=='6months') { item_yearly_cost = item->cost * 2; }

  else if (item->time_type=='12months') { item_yearly_cost = item->cost * 1; } //  ' * 1' not needed, just being explicit

  yearly_total += item_yearly_cost;
}

var monthly_total = 0;

foreach( item in items )

  if (item->time_type=='1month') { item_yearly_cost = item->cost / 1; } //  '/ 1' not needed, just being explicit

  else if (item->time_type=='6months') { item_monthly_cost = item->cost / 6; }

  else if (item->time_type=='12months') { item_monthly_cost = item->cost / 12; }

  monthly_total+= item_monthly_cost ;
}

cost | time_type
4.00 1month
5.00 1month
10.59 1month
14.83 1month
9.99 1month
119.00 12months
1.99 12months
290 6months
59.70 6months
93.11 6months

[#2]

@armyofda12mnkeys 

You're making it much more complicated than necessary. There are no doubt multiple solutions, but here's the one I'd use for simplicity's sake. Just create a small table of conversion factors and use VLOOKUP to determine which is the relevant one.  I've attached the actual file, but here's an image as well.