Help!!! Nested IF Statement limitation

[trenier]

Greetings,

I have a nested IF statement that I am using in an application I am building in Excel. I am using logic to get the current month and then evaluate whether the value is a number. I would like to know if there is a better way to perform what I am trying to accomplish using another function. I am trying to cocantonate the the statements together which has worked in the past but not for this particular function. Your help is greatly appreciated. See Below... The function works if i use 7 IF statements.


=IF(I3="Jan",IF(ISNUMBER(SEARCH("NA",Data!$B$355)),"NA",Data!$B$355),IF(I3="Feb",IF(ISNUMBER(SEARCH("NA",Data!$C$355)),"NA",Data!$C$355),IF(I3="Mar",IF(ISNUMBER(SEARCH("NA",Data!$D$355)),"NA",Data!$D$355),IF(I3="Apr",IF(ISNUMBER(SEARCH("NA",Data!$E$355)),"NA",Data!$E$355))))) + IF(I3="May",IF(ISNUMBER(SEARCH("NA",Data!$F$355)),"NA",Data!$F$355),IF(I3="Jun",IF(ISNUMBER(SEARCH("NA",Data!$G$355)),"NA",Data!$G$355),IF(I3="Jul",IF(ISNUMBER(SEARCH("NA",Data!$H$355)),"NA",Data!$H$355),IF(I3="Aug",IF(ISNUMBER(SEARCH("NA",Data!$I$355)),"NA",Data!$I$355),IF(I3="Sep",IF(ISNUMBER(SEARCH("NA",Data!$J$355)),"NA",Data!$J$355)))))) + IF(I3="Oct",IF(ISNUMBER(SEARCH("NA",Data!$K$355)),"NA",Data!$K$355),IF(I3="Nov",IF(ISNUMBER(SEARCH("NA",Data!$L$355)),"NA",Data!$L$355),IF(I3="Dec",IF(ISNUMBER(SEARCH("NA",Data!$M$355)),"NA",Data!$M$355))))

Cheers,

Todd

 

[markmzz]

Hi Mark,

I3 points to a cell that gets the current Month. Some of the fields have NA in them. I am open to any suggestions you have as I am new to writing functions in Excel.

Todd

Try this small modification in [COLOR=#0000ff]Vlady's formula[/COLOR]:

[COLOR=#0000ff]=INDEX(Data!$B355:$M$355,,MONTH(I3&14))[/COLOR]

Or try this small modification in my last formula

[COLOR=#0000ff]=OFFSET(Data!$B355,,MONTH(I3&14)-1)[/COLOR]

Hey Mark,

I just wanted to let you know that your function works well. I attempted another solution but this formula works with SharePoint Excel Services which is where this is going to reside. Aladin's solution worked but SharePoint Excel Services does not work with formulas with adjacent referenced cells as what was used in the Month range.

Thank you! for your help with this solution.

Regards,

Todd

Hi Todd,

I'm glad to help and thank your for the feedback.

PS: try the formulas above too.

Markmzz

 

[trenier]

Hi Mark,

I want thank you for providing this formula. It has worked well in the application I developed. Since my skills at Excel are marginal, can you explain how this formula works?

Thanks Again!

Todd

 

[markmzz]

Hi Mark,

I want thank you for providing this formula. It has worked well in the application I developed. Since my skills at Excel are marginal, can you explain how this formula works?

Thanks Again!

Todd

Hi Todd,

The formula

=OFFSET(Data!$B355,,MONTH(I3&14)-1)

Do this:

1) The part MONTH(I3&14) get the number of the month corresponding to I3 (Jan - 1, Feb - 2, ...)

2) The part =OFFSET(Data!$B355,,MONTH(I3&14)-1) offset the cell B355 the number of the month less one to the right.

If the month is Jan then offset 0 column to the right (1-1=0) - B355

If the month is Feb then offset 1 column to the right (2-1=1) - C355.

I hope that the information above helps.

Markmzz