Return a formula

[babyg]

I have a worksheet that contains 4 columns. Column A is a $ amount, Column B is the frequency (ie hourly, weekly, monthly, annually), Column C is the written out version of the formula used to calculate the total, and Column D is the total amount. I am looking for a way to populate Column C based on the data entered. So if the frequency was monthly it would show the vaule of column A times 12, or if annually it would show the value of column A times 1.

Example:

A B C D
$800 Monthly 800 x 12 $9600

 

[T. Valko]

I have a worksheet that contains 4 columns. Column A is a $ amount, Column B is the frequency (ie hourly, weekly, monthly, annually), Column C is the written out version of the formula used to calculate the total, and Column D is the total amount. I am looking for a way to populate Column C based on the data entered. So if the frequency was monthly it would show the vaule of column A times 12, or if annually it would show the value of column A times 1.

Example:

A B C D
$800 Monthly 800 x 12 $9600

If Monthly is x 12 then what are the other multipliers?

 

[babyg]

Annually is value of column A x 1
Hourly is value of column A x 40 x 52
Weekly is value of column A x 52

 

[T. Valko]

Annually is value of column A x 1
Hourly is value of column A x 40 x 52
Weekly is value of column A x 52

Try this...

Book1
	A	B	C	D
2	800	Monthly	800 x 12	9600
3	10	Hourly	10 x 40 x 52	20800
4	17	Weekly	17 x 52	884
5	25	Annually	25 x 1	300
6	_	_	_	_
7	_	_	_	_
8	_	_	_	_
9	_	_	_	_
10	Annually	1	_	_
11	Hourly	2080	_	_
12	Monthly	12	_	_
13	Weekly	52	_	_
Sheet1

This formula entered in D2 and copied down:

=A2*IF(B2="annually",12,IF(B2="hourly",2080,IF(B2="weekly",52,IF(B2="monthly",12))))

Or, you could create a 2 column table as shown and use this formula:

=A2*IF(COUNTIF(A$10:A$13,B2),VLOOKUP(B2,A$10:B$13,2,0))

 

[babyg]

Is there also a formula that can be used to fill in the contents of column C as shown?

 

[T. Valko]

Is there also a formula that can be used to fill in the contents of column C as shown?

If you use the lookup table...

Book1
	A	B	C	D
2	800	Monthly	800 x 12	9600
3	10	Hourly	10 x 2080	20800
4	17	Weekly	17 x 52	884
5	25	Annually	25 x 1	300
6	_	_	_	_
7	_	_	_	_
8	_	_	_	_
9	_	_	_	_
10	Annually	1	_	_
11	Hourly	2080	_	_
12	Monthly	12	_	_
13	Weekly	52	_	_
Sheet1

This formula entered in C2 and copied down:

=A2&" x "&VLOOKUP(B2,A$10:B$13,2,0)

 

[babyg]

Great. Thanks so much for your help

 

[T. Valko]

Great. Thanks so much for your help

You're welcome!