formula to generate an annual cost based on payment frequency

[KatIsles]

Hi guys,

I'm sure this is a simple question but it is making my brain hurt!

I have a table with four columns. Column A describes an item, Column B details a cost, Column C indicates the payment frequency and column D indicates the annual cost associated with that item.

I'm looking for a way for Column D to be populated based on the responses to Column B and C. Column C is a drop down list (using data validation), so the user can only select one of the following options:

weekly
fortnightly
monthly
quarterly
annually
bi-annually

Is there a way this can be done?

Thanks for your help!

Kat

 

[drsarao]

Welcome to MrExcel.

It would have been easier if column C had numbers as quotient for calculation.

But you can create a table of quotients and look it up for the calculations.

Excel Workbook
	A	B	C	D	E	F	G
1	Item	Cost	Frequency	Annual
2	aa	300	weekly	15,600		Frequency	Quotient
3	b	900	fortnightly	23,400		weekly	52
4	cc	12,000	monthly	144,000		fortnightly	26
5	dd	46,000	quarterly	184,000		monthly	12
6	b	86,000	annually	86,000		quarterly	4
7	cc	120,000	bi-annually	60,000		annually	1
8	dd	360	weekly	18,720		bi-annually	0.5
9	b	90,000	annually	90,000
10
Sheet2

Excel 2003

Cell Formulas
Range	Formula
D2	=B2*VLOOKUP(C2,$F$2:$G$8,2,0)

Here $F$2:$G$8 is the table which gives the multiplier to use for each frequency.

 

[KatIsles]

Thanks so much drsarao! It works perfectly! and now I understand how to use Vlookups too

Thanks again - I really appreciate your help!

Kat

 

[drsarao]

You are welcome.