IF Formula Error

[AFLeddie]

Hi there,

I'm looking to use an IF function to calculate values and keep getting an error that there are too many arguments. Basically I have a list with frequencies (Weekly, Monthly, Annually, Semi-Monthly, etc.) and need to calculate a yearly number based on that frequency. The problem is there are five or six frequencies. I have one formula that is working but only capturing 2 frequencies:

=IF(C2="Monthly",(D2*12),(IF(C2="Weekly",((D2*4)*12))))

I need something that will encompass all frequencies in the formula.

Any help would be great!

 

[AFLeddie]

Better yet, why doesnt this work:

=IF(C2="Monthly",(D2*12),(IF(C2="Weekly",((D2*4)*12))),(IF(C2="Annual",(D2*1))))

The frequencies that I would need are Weekly, Semi-Monthly, Monthly, Quarterly, Semi-Annually and Annually - which is less than 7. I thought you can have up to seven arguments in a formula...

 

[aravindhan_31]

too Many brackets...

try this:

IF(C1="Weekly",D1*4*12,IF(C1="Monthly",D1*12,IF(C1="Annual",D1*1)))

 

[Oaktree]

Try:

=D2*VLOOKUP(C2,{"Weekly",48;"Semi-Monthly",24;"Monthly",12;"Quarterly",4;"Semi-Annually",2;"Annually",1},2,0)

 

[T. Valko]

Better yet, why doesnt this work:

=IF(C2="Monthly",(D2*12),(IF(C2="Weekly",((D2*4)*12))),(IF(C2="Annual",(D2*1))))

The frequencies that I would need are Weekly, Semi-Monthly, Monthly, Quarterly, Semi-Annually and Annually - which is less than 7. I thought you can have up to seven arguments in a formula...

You're getting all jacked up with those parentheses!

Try something like this:

=IF(C2="Monthly",D2*12,IF(C2="Weekly",D2*48,IF(C2="Annual",D2,"")))

 

[AFLeddie]

I've got that to work so thank you, but how do I add more frequencies to it. I still get a "Too Many Arguments" error. Ideally, I would want this to work:

=IF(C2="Weekly",D2*4*12,IF(C2="Monthly",D2*12,IF(C2="Annual",D2*1),IF(C2="Quarterly",D2*4),IF(C2="Semi Monthly",D2*24)))

 

[AFLeddie]

Nice on the responses!

@Oaktree, your solution seems to be working even though I'm unfamilar with {} on VLOOKUPS.

Thanks!!!

 

[T. Valko]

I've got that to work so thank you, but how do I add more frequencies to it. I still get a "Too Many Arguments" error. Ideally, I would want this to work:

=IF(C2="Weekly",D2*4*12,IF(C2="Monthly",D2*12,IF(C2="Annual",D2*1),IF(C2="Quarterly",D2*4),IF(C2="Semi Monthly",D2*24)))

Try it like this...

=IF(C2="Weekly",D2*48,IF(C2="Monthly",D2*12,IF(C2="Annual",D2,IF(C2="Quarterly",D2*4,IF(C2="Semi Monthly",D2*24,"")))))

A couple of points of interest...

D2*4*12 can be more simply expressed as D2*48
D2*1 can be more simply expressed as D2

 

[Oaktree]

The { }s are just a way to store an array in a cell.

For example, you could use {"First", 100; "Second", 200} in your formula instead of the range A1:B2 as below if you didn't want to have the table on your spreadsheet.

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">First</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Second</td><td style="text-align: right;;">200</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[AFLeddie]

Great Work!!

Thank you all for your help on this!