Excel formual IF statement Help

[Cerealkiller]

I am trying to make this IF statement work

=IF(A4="",0,IF((A4="F"),B4,IF((A4="D"),B4,IF((A4="T"),B4,IF((A4="S"),C4,IF(
(A4="M"),C4,IF((A4="L"),C4,IF((A4="H"),C4,IF((A4="G"),C4,IF((A4="C"),C4,"ERR")
)))))))

 

[Ron Rosenfeld]

I am trying to make this IF statement work

=IF(A4="",0,IF((A4="F"),B4,IF((A4="D"),B4,IF((A4="T"),B4,IF((A4="S"),C4,IF(
(A4="M"),C4,IF((A4="L"),C4,IF((A4="H"),C4,IF((A4="G"),C4,IF((A4="C"),C4,"ERR")
)))))))

It is helpful if you tell us what the problem is. Knowing that you apparently
cannot make this work is not very useful information when you don't tell us
either the version of Excel you are using, or what your function is doing
incorrectly.

Your formula, as written, does not have enough close parentheses at the end.

=IF(A4="",0,IF((A4="F"),B4,IF((A4="D"),B4,IF((A4="T"),B4,IF((A4="S"),C4,IF(
(A4="M"),C4,IF((A4="L"),C4,IF((A4="H"),C4,IF((A4="G"),C4,IF((A4="C"),C4,"ERR")
)))))))))

seems to work perfectly OK on my computer. But I am using Excel 2007 which is
not limited to seven levels of nesting as are earlier versions of Excel. Also,
Excel should have corrected your formula.

Could it be that you are using an earlier version? If so, you will be limited
to seven nesting levels. That being the case, your formula can be simplified:

=IF(A4="",0,IF(OR(A4={"F","D","T"}),B4,
IF(OR(A4={"S","M","L","H","G","C"}),C4,"ERR")))

But if there is some other problem that you are concerned with, you'll need to
provide more detail.
--ron

 

[Cerealkiller]

Thank you very much for you rapid response and it works very well. I am new
to OfficeKB so thank you for answering my question. I was not sure on how to
appropriately list questions and what information to add, but in the future I
will. I really do appreciate your help and hopefully if I have any other
questions or problems I would gladly accept any advice you can provide.

 

[JLatham]

You've got a rather unique/special circumstance here, and I think this is the
solution
=IF(A4="",0,IF(ISERR(FIND(A4,"FDTSMLHGC")),"ERR",IF(FIND(A4,"FDTSMLHGC")<4,B4,C4)))

Mickey's right in stating that pre-2007, only 7 levels of nesting are
supported, but because in this setup you're only interested in evaluating the
contents of A4 and making the choice on that, and because 3 of those tests
result in a choice of B4, and 6 other results end with a choice of C4, we can
set it up as I've done above.

 

[Cerealkiller]

Thank you for you alternate solution.

You've got a rather unique/special circumstance here, and I think this is the
solution
=IF(A4="",0,IF(ISERR(FIND(A4,"FDTSMLHGC")),"ERR",IF(FIND(A4,"FDTSMLHGC")<4,B4,C4)))

Mickey's right in stating that pre-2007, only 7 levels of nesting are
supported, but because in this setup you're only interested in evaluating the
contents of A4 and making the choice on that, and because 3 of those tests
result in a choice of B4, and 6 other results end with a choice of C4, we can
set it up as I've done above.

I am trying to make this IF statement work

[quoted text clipped - 3 lines]

.

 

[Cerealkiller]

Hi Ron, Thank you for you help I also have these following statements that I
am having trouble with.
I have Excel 2000 so these problems are more than 7 levels is there a way to
make them shorter. I did make a vlookup for them but if I have to move the
cell then I have to re reference that vlookup so I figured a formula would be
easier if it is possible. Thank you for any help that you can provide.



=IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF(
(E4="M"),0,IF((E4="L"),-1,IF((E4="H"),-2,IF((E4="G"),-4,IF((E4="C"),-8,"ERR"))
))))))))

=IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF(
(E4="M"),0,IF((E4="L"),1,IF((E4="H"),2,IF((E4="G"),4,IF((E4="C"),8,"ERR"))))))
))))

 

[Ron Rosenfeld]

Hi Ron, Thank you for you help I also have these following statements that I
am having trouble with.
I have Excel 2000 so these problems are more than 7 levels is there a way to
make them shorter. I did make a vlookup for them but if I have to move the
cell then I have to re reference that vlookup so I figured a formula would be
easier if it is possible. Thank you for any help that you can provide.



=IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF(
(E4="M"),0,IF((E4="L"),-1,IF((E4="H"),-2,IF((E4="G"),-4,IF((E4="C"),-8,"ERR"))
))))))))

=IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF(
(E4="M"),0,IF((E4="L"),1,IF((E4="H"),2,IF((E4="G"),4,IF((E4="C"),8,"ERR"))))))
))))

Vlookup will be a better solution for this.

Just reference the table as an absolute reference, or NAME it.
--ron

 

[Ron Rosenfeld]

Thank you very much for you rapid response and it works very well. I am new
to OfficeKB so thank you for answering my question. I was not sure on how to
appropriately list questions and what information to add, but in the future I
will. I really do appreciate your help and hopefully if I have any other
questions or problems I would gladly accept any advice you can provide.

Glad it worked. Thanks for the feedback.
--ron

 

[Iriemon]

Try this:

=IF(A4="",0,IF(OR(A4="F",A4="D",A4="T"),B4,IF(OR(A4="S",A4="M",A4="L",A4="H",A4="G",IFA4="C"),C4,"ERR")))

Irie!