Nested formula trouble.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, oh mighty ones!

I'm trying to enter this formula in Excel:

=IF(B2=a,10,"IF(B2=b,8,IF(B2=c,6)))")

According to the nested formulas rule, it's well-formatted (and I'm not even
using the seven-limit). This is to calculate how many points one gets
according to each school grade, A, B or C. HOWEVER...though it doesn't give
me an actual error message, the formula result cell tells me this:

#NAME?

Can someone tell me what I'm doing wrong? Thanks...
 
Hi

Not quite right with the setup of your formula, and where you are
testing for text as opposed to numeric values, they need to be enclosed
with double quotes
Try
=IF(B2="a",10,IF(B2="b",8,IF(B2="c",6,"")))
 
Merci, Roger!!! You are a genius. I had tried the quotations before but not
in the right place. IT WORKS!!!!!! THank you so very much. Since I'm no
planning to get anything BELOW C on the class (a screenwriting class), I
won't add any more arguments to it.

There is one thing that doesn't make sense to me, though, and I'd appreciate
it if you can clarify it for me: I thought that one had to enclose in
quotations from the second to the last argument in the formula:

=IF(B2="a",10,"IF(B2="b",8,IF(B2="c",6,"))")

However, your solution shows that not only there aren't quotations before
the second IF, but there is an extra set of quotations with no partner (the
very last one):

=IF(B2="a",10,IF(B2="b",8,IF(B2="c",6,"")))

Is there any reason this illogical order works, or am I missing something
else?

Thanks in advance!
++++++++++++++++++++++++++++++++++++++++++++++++++++
 
Roger's final empty string gives a blank answer if you either have an empty
input, or any value other than a, b, or c.

If you put quotes around "IF(B2="b",8,IF(B2="c",6,"))", then you'll get that
string as an output if the input isn't a, rather than evaluating the formula
[but you'd have further problems because you've got various quote symbols
within the string, and if you put quotes within a string they'd need to be
doubled:
=IF(B2="a",10,"IF(B2=""b"",8,IF(B2=""c"",6,""))") ]
 
Thank you, David. That's a DUH moment for me, I should've thought of that,
but I had not slept for about 48 hours straight (though that shouldn't be an
excuse).

Thank you to both of you, you've helped me a lot. May you both win the next
Lotto! :)

David Biddulph said:
Roger's final empty string gives a blank answer if you either have an empty
input, or any value other than a, b, or c.

If you put quotes around "IF(B2="b",8,IF(B2="c",6,"))", then you'll get that
string as an output if the input isn't a, rather than evaluating the formula
[but you'd have further problems because you've got various quote symbols
within the string, and if you put quotes within a string they'd need to be
doubled:
=IF(B2="a",10,"IF(B2=""b"",8,IF(B2=""c"",6,""))") ]
--
David Biddulph

PowerPointless said:
Merci, Roger!!! You are a genius. I had tried the quotations before but
not
in the right place. IT WORKS!!!!!! THank you so very much. Since I'm no
planning to get anything BELOW C on the class (a screenwriting class), I
won't add any more arguments to it.

There is one thing that doesn't make sense to me, though, and I'd
appreciate
it if you can clarify it for me: I thought that one had to enclose in
quotations from the second to the last argument in the formula:

=IF(B2="a",10,"IF(B2="b",8,IF(B2="c",6,"))")

However, your solution shows that not only there aren't quotations before
the second IF, but there is an extra set of quotations with no partner
(the
very last one):

=IF(B2="a",10,IF(B2="b",8,IF(B2="c",6,"")))

Is there any reason this illogical order works, or am I missing something
else?

Thanks in advance!
++++++++++++++++++++++++++++++++++++++++++++++++++++
 
Back
Top