Create formulas based on drop-down data

[jonem100]

I have, what I thought, to be a simple sheet to program. I am trying to create a formula to calculate the total annual amount based on a dollar amount entered in one cell and a pay frequency selected from a drop down in the adjacent cell.

Ex: C2 = $10
D2 = Monthly (selected form drop-down box which contains monthly, quarterly, semi-annually, annually)
**E2 = I want this cell to contain $120, based on this example, so I need it to be written so that C2*12 IF D2=Monthly. And so on so that the total will be $40 if C2*4 IF D2=Quarterly, $20 if C2*2 IF D2=Semi-annual, and $10 if C2*1 IF D2=Annual.


Thanks - Matt

[wallyeye]

If you build a table like this:

Weekly 52
Bi-Weekly 26
Monthly 12
Quarterly 4
Semi-Annually 2
Annually 1

name the first column FreqDesc and the second FreqQty, this formula will return the number:

=INDEX(FreqQty,MATCH(D2,FreqDesc,0))

Where D2 has your selected interval. Then just multiply this as needed:

=C2*Index(FreqQty, Match(D2,FreqDesc,0))

[Cutter]

Try

=C2*LOOKUP(D2,{"Annually","Monthly","Quarterly","Semi-annually"},{1,12,4,2})

[jonem100]

Try

=C2*LOOKUP(D2,{"Annually","Monthly","Quarterly","Semi-annually"},{1,12,4,2})

Perfect.....Thanks!!!

[Cutter]

You're welcome.