correct answer to a thread

[martindwilson]

ok so op posts a question
how to nest if?

in excel 2007 i have integers from 1 to 4 in cell a1 and want to return 1=a 2=b 3=c 4=d and over 4 "out of range"
i have so far =if(a1=1,"a",(IF(a1=2,"b")),if((IF(a1=3,"c")),(IF(a1=4,"d")),"out of range")) but i get an error message of to many arguements
how do i fix it?

several replies later he has been given these answers
1

Please Login or Register  to view this content.

2

Please Login or Register  to view this content.

3

Please Login or Register  to view this content.

4

Please Login or Register  to view this content.

5

Please Login or Register  to view this content.

6

Please Login or Register  to view this content.

and maybe a few more

so which is the correct answer?

[zbor]

As always, it's not question when they return correct answer (probably all of them). It's more about what to get if someone enter <1 number or for example character...

[martindwilson]

ah thats where i disagree ,you see too many threads where the ops original question is not answered and people just assume they want one that works
the only correct answer from those is
=IF(A1=1,"a",IF(A1=2,"b",IF(A1=3,"c",IF(A1=4,"d","out of range"))))
the rest are alternative methods to achieve the same results
the question was about nesting IF not about how to convert 1 to A. valid to post the others as suggestions tho

[zbor]

Sometimes op ask for 'nesting IF's' for example, because he doesn't know other approach.
Doesn't mean that is best approach, thou.

[martindwilson]

no it means you haven't read the question properly

but i get an error message of to many arguments
how do i fix it?

how do we know this isn't just a simplified version of something else?. imo we should always answer the question as posted if possible and then suggest alternatives not just leap in with some flashy function.
like

well you nest ifs in this fashion

=IF(A1=1,"a",IF(A1=2,"b",IF(A1=3,"c",IF(A1=4,"d","out of range"))))
but shorter alternative to this could be

=IFERROR(CHOOSE(A1,"a","b","c","d"),"out of range")

[FDibbins]

Martin, you neglected to add if those other suggestions were presented as alternatives, or as a "use this" reply?

Fundamentally, I agree with you, the question was "fix my nested IF's" and formula 1 does exactly that. If the others were suggested as alternatives, thats great too. I sometimes offer alternatives to what the OP wants, merely as a way of showing other (maybe better?) options (and maybe a bit of training)

[zbor]

When someone ask for question (and I've seen them) that solution is VLOOKUP function, and he ask for 10 IF functions you will write 10 IF's as solution and offer VLOOKUP or point him to VLOOKUP right away?

I would go for 2nd way... But yes, sometimes I (or we) overmake our solutions. (If there is a word overmake :D )

[blue.chio]

...Fundamentally, I agree with you, the question was "fix my nested IF's" and formula 1 does exactly that. If the others were suggested as alternatives, thats great too. I sometimes offer alternatives to what the OP wants, merely as a way of showing other (maybe better?) options (and maybe a bit of training)

I really appreciate other solutions offered, as a newbie... just to learn!

[martindwilson]

now thats a different question now if as you say someone tries to nest umpteen ifs when a vlookup or similar is the fix then by all means suggest it and i agree that posting a 20 nested if statement would be silly.
fd it was just an example nothing to stop that it should be encouraged, but i feel the original q should be answered by the first reply or even ask if that's what's really desired not just blindly post some try this array formula type of answer.
try another
how do i get over the 7 ifs limitation in excel 2003?
now i don't know why someone wouldn't use a vlookup but it is doable so my answer would show that
i

[FDibbins]

Blue, I have seen quite a few times when an alternative is suggested (by me or others) that the OP comes back with comments ranging from...oh wow, I didnt know you could do that...to...ummm duh, why didnt I think of that...

@Martin, yes I figured it was just an example, and as I said, I fundamentally agree with you - answer the question 1st - then suggest options

[blue.chio]

FD, yeah...or never offers a feedback or comes back! k, I do not want to talk too much off topic! Thanks and I am sorry!

[zbor]

Right, but also I always ask myself: What if he want to extend to 5 more numbers? What if, as I mention b4, somebody put letter in A1? etc....
I still think that buletproof solution is better than just fixing IF.
If it can keep it simple, as you gave other solutions.

I'll keep in mind this when I'll answer once some similliar question with multiple approaches.
And I don't like IF's

[martindwilson]

talk of topic as much as you want,this is just a discussion i'm not fussed.
i suppose i just get peeved when a). i answer the question as posted then b)20 posts later someone pops in an answer that i knew from the start was an alternative
and the op completely ignores or acknowledges that their original q was answered in post#2
i hate ifs too lets not answer any post that has an outside chance of using IF anywhere in it

[FDibbins]

=IF("only it was that easy","use vlookup","use another if")

[JosephP]

answering a question and providing a solution are occasionally the same thing; which is better will always be a topic of discussion ;-)

[martindwilson]

well here's a typical example it just happened
while i;m typing up an answer BANG a formula given but no advice!
http://www.excelforum.com/excel-form...25#post3341225

[FDibbins]

pretty typical there though, Martin

[JBeaucaire]

I never answer the question posted if I suspect the OP's own question is misleading them. I presume they simply are unaware of the myriad other tools available to them, once i understand as much as possible of what they're trying to calculate, I offer the best formula I can to accomplish that, regardless of how far that is from their originally phrased question.

Common:

1) Massive Nested IF replaced by easier to read/maintain lookup formula.
2) Introduction of whole new functions they were previously unaware of... possibly version-centric.
3) VBA questions frequently given a simple formula answer instead
4) Formula questions frequently lead to OP being introduced to VBA.


For me the goal is of greater importance than the question.

[FDibbins]

I think to Martin's point though, Jerry, Im sure you would not just, out of the blue, offer something completely different (giving then the results they want) without 1st explaining - even briefly - that you suggestion is a possibly simpler alternative for them to consider?

[JBeaucaire]

Honestly, I have done that on many occasions.

"Ok, try this instead, see if it gives you the results you want:

=somefunnyformula"



Possible responses of have ranged from "Wow, that's so much easier...", "Never thought of that", "Can you explain how that works", "Thanks, that does the trick"....

It is pretty rare that an OP insists on their original approach.

[FDibbins]

yes, but you *did* say..."try this instead", not just =somefunnyformula

[snb]

@MD

Language nor communication is digital.
Phrasing a question is 1, phrasing an intention is 2, bearing in mind that for many OP's English/American is not their native language is 3.
Reading language only digitally is neglecting the essence of language/communication.

So:

=CHAR(MIN(B15,5)+96)

[Philb1]

When I ask a question, I like to see a fix (if it's fixable) for the actual question I asked & I love it when someone shows a different way of doing the same thing as well. That way I learn.

[TMS]

There are many occasions when I have simply made the formula or code work, without trying to enhance, replace or substitute it. And sometimes, someone else comes along with an/the alternative.

Other times I have offered alternatives ... Mostly if I don't think the original is practical or viable.

And sometimes I do both. Kind of depends how much time I have, how easy it is to offer alternatives, how interesting the problem is ... and if the OP has provided a sample workbook to test the options.

Was that the correct answer to the thread?

[blue.chio]

In my opinion, definitely, yes!
I like to see alternatives, I can see other vision, I can choose...if I can make the difference between them. One solution can be better than other, or uses many resources, or takes too much time even if the result is the same!
But I think, it's just a thought, the key is in the OP's hand, he/she must say "Thank you" to all.
are there too many variables? lol

[martindwilson]

Kind of depends how much time I have

i suppose that has to be considered!