tldr give solution kthxbye

[Speshul]

This has been bothering me over the last few months. No one wants to learn anymore?

When I come across a "my formula doesn't work" thread, I will generally try to post an explanation as to why the OP's formula didn't work, instead of just posting something like

Please Login or Register  to view this content.

Sometimes I don't even post a solution at all, I just explain how the formula is supposed to work, show the OP exactly where in the formula the mistake is, and what needs to be done to correct it. It seems like the OP's usually just doesn't care why it doesn't work, doesn't want to learn how to avoid the mistake in the future, or whatever. It's all just tl;dr. Solution plz.

Another part that is irritating about that is the "=formula(), +rep me kthxbye" guy is the one who get's the thanks, after I had done battle with Color/Bold/Code tags for 15 minutes to post an easily understood explanation of why the OP's formula didn't work in the first place. Not to mention editing the thread 8 times in the process (which was especially fun last week with the Edit problems the forum was experiencing!).

I understand people come here for solutions, but why doesn't anyone seem to care to learn anymore? I hate the 'if it works, don't fix it' attitude.

If it works, you find out why, and figure out if you can make it work better.


Though I will admit I give snarky sarcastic and short responses when the poster is particularly unhelpful in asking a question that can be understood.

[:) Sixthsense :)]

May be the OP's might be running out of time to complete the work, that may be the one of the reason they are not intended to do the investigation and coming here to get quick solution

[Whizbang]

For me, my peeve is a person who posts a question and never returns. I answered your question and posted an example workbook and everything! Why aren't you checking back on this thread to see my totally awesome solution that will open your mind to the wonders and powers of Excel?!

Seriously, sometimes I go through my subscribed threads where I am the last poster and check when the OP was last online to see if they could potentially have seen my response and just not responded in turn. Quite often they haven't. Why? If it was important enough to post a question, it is important enough to check back the next day... Unless they expected an answer in 5 minutes?

[Speshul]

Agreed, though I've noticed that a lot of questions actually do get an answer within 5-10 minutes anyway

I would guess a lot of them are posting the question first, THEN they go look it up and find the solution on google, so they just abandon the thread. Personally I think it's easier to google something first than it is to sign up for a Forum and post the question, maybe that's just me though...

And it could always be getting asked on 5 forums, and another forum responds first so all the others are abandoned.

[FDibbins]

I know we all have our pet peeves about posts, thanks and attitudes etc, but we have to remember, we dont know where the OP is coming from and what there requirements are.

And of course, there is always the old fall-back - if you dont want to answer, then dont, easy as that If there are OP's that you feel constantly abuse the help they get here, and never give thanks or feedback, ignore them and dont answer their questions

[Whizbang]

I mostly post solutions as a way to hone my skills and encounter interesting problems or workbook designs. Still, it is nice to receive to positive feedback (or any feedback at all).

Like Speshul, though, I truly do not understand how someone can just accept a formula without trying to figure out the how's and why's. I understand some problems just require a solution as fast as possible and who cares why it works, but I don't see not coming back to it after the fact to try and understand.

I suppose I should be happy that most people feel that way, however, because my day-job entirely depends on me being the Excel guru of the company and everyone else being a total newb.

I still can't wrap my head around that mentality, though.

[FDibbins]

Whiz, I tend to agree with you. I am en engineer (industrial) and cant stand not knowing how things work. However, not all people are like that, some really couldnt care less how a formula works, they just need it to work - now. If they need to adapt it for another purpose and cant...they can always come back.

I have a feeling that some of the "peevers" are not people who use excel in their every-day lives. They have a need to fill, they fill it with a formula provided here, and move on

[Speshul]

Especially when I give the exact same solution as the "=solution()" guy, posted in large bold blue letters at the top of my post above the explanation, and am the first to respond on the thread, and still get nothin'!

I just use a copy/paste of a pre-formatted explanation of how IF functions work now, because I got tired of typing it out every day, lol

[Whizbang]

You can create custom Canned Responses in your UCP to hold things like that, Speshul.

[FDibbins]

Speshul, are we talking about a particular member now?

[Speshul]

No, not really, I mean most of us have posted nothing but a solution formula on some questions, I'm just talking the occasional instance when I post a small novel of explanation with the solution formula at the top, User2 posts a minute or two later with an almost identical or identical formula, and the OP is all "USER2 U R AMAZING!"

I am in no way suggesting User2 'took' my formula or anything, I mean there are only so many ways to write an IF/AND formula for example.

@ Whiz, I didn't know that, thanks!

[Whizbang]

That's why I like to take the I Heart IF approach. It takes the "IF" out of the equation and most people have never seen that method before, plus it is a lot more flexible and readable than a standard IF.

[Speshul]

That's why I like to take the I Heart IF approach. It takes the "IF" out of the equation and most people have never seen that method before, plus it is a lot more flexible and readable than a standard IF.

Hmm....maybe I'll intentionally over complicate my responses, so the person asking is super confused and HAS to read my explanation....hmmmm

Using the formula in your link.... ( =IF(A2="red",25,IF(A2="white",101,IF(A2="blue",74,0))) )

An excellent alternative would be;
=INDEX({25;101;98;56;15;85;74;74},VLOOKUP(A2,{"red",-5;"white",-4;"blue",2},COLUMNS(X4:Y57),0)+ROW(X96)-(SUMPRODUCT(45,2)))

Yes...yes...I like that...

[:) Sixthsense :)]

The IF() function is totally differed from the boolean logic.

If() will exit soon when it found a match but the boolean logic will evaluate all the given conditions which is an unnecessary process nicely avoided in If().

[daddylonglegs]

No, not really, I mean most of us have posted nothing but a solution formula on some questions, I'm just talking the occasional instance when I post a small novel of explanation with the solution formula at the top, User2 posts a minute or two later with an almost identical or identical formula, and the OP is all "USER2 U R AMAZING!"

Hey Speshul,

I sympathise, really......but this happens to everybody at one time or another. What are you going to do? It's not worth worrying about, you need to ask yourself what you are doing here? If you are trying to improve your knowledge and help a few people at the same time then, hey, you're doing a good job - don't expect any praise......and when you get some, it's great!

I used to post a lot at Experts Exchange (not so much now) and over there the OP awards points for the "correct" solution - it can be a recipe for an amazing amount of griping and bad feeling.....but my attitude was that for every occasion where I didn't get the points I deserved there was another occasion where I got some I didn't - it all evens out in the end.

Having said all that there are some techniques you can use - don't tell anyone I told you

1.) If you see some "forum guru" lurking on a thread that you're working on.......get in first with a "holding post" - perhaps ask a question (there's usually something that needs clarifying). By doing this you can establish a "rapport" with the OP before anybody else answers.

2.) Hold on - I can't give all my secrets away.......

The "repetition" aspect is always interesting - sometimes I look up an old post or worksheet of mine to answer a question but 90% of the time I write a formula from scratch (I mostly know the technique but I don't have a "library" of formulas - I find that constructing the formula/function each time from scratch is a great way for me to get to know them inside out).

[Whizbang]

The IF() function is totally differed from the boolean logic.

If() will exit soon when it found a match but the boolean logic will evaluate all the given conditions which is an unnecessary process nicely avoided in If().

I'd be interested to see some more information on comparing the two. It's been a while since I investigated the matter.

[Edit] Did some googling, haven't read everything yet.

http://www.ozgrid.com/forum/showthread.php?t=146823

http://www.excelbanter.com/showthread.php?t=241633

[Speshul]

Oh I almost always go from scratch, unless I had answered the same question a few days earlier or something. I don't really care about reputation too much, I find people asking for reputation constantly to be a little irritating honestly, like they are not posting to help people, they are posting for little green squares? That's another conversation though.

Mostly I'm just complaining I guess, so many people don't care how a solution happens as long as it happens. But I guess that's the mentality that keeps a lot of society us employed, so I can't complain too much

[Speshul]

I'd be interested to see some more information on comparing the two. It's been a while since I investigated the matter.

I think the point was a boolean product method may return the same result, but IF statements allow excel to effectively ignore parts of the formula resulting in simpler calculations, as well as simpler formula reading (assuming one understands IF logic in general). Explaining the boolean logic method is more difficult for someone without any computational logic knowledge. In other words, Muggles are scared of binary.

[:) Sixthsense :)]

Hi Whizbang,

Surely I will post the comparison between these two by tomorrow. Because I am replying this thread from my mobile device I cant type more text or create a example file. Its 12.30 AM midnight in India so wait for another 8 Hours so that I will get access to system when I am back to my workplace.

[JBeaucaire]

A point no one has made yet, every post of quality you put into the forum is a permanent researchable example of some technique that someone in the future may need. I try to use special key words where I can remember to make my posts more likely to be caught up in a Google or Forum search results.

Nothing you do goes to waste, even if it's not appreciated initially.

[Whizbang]

That is something else I try to keep in mind.

In fact, I've even stumbled upon my own solutions using Google and re-learned something from myself.

[Tony Valko]

I had to lookup the meaning of tldr.

[Tony Valko]

90% of the time I write a formula from scratch (I mostly know the technique but I don't have a "library" of formulas

I'm just the opposite.

I have an extensive library of formulas.

If it's a "trivial" formula then I'll just write it directly into the reply without testing.

Things that are a bit more complicated I (almost) always test.

[:) Sixthsense :)]

[Edit] Did some googling

Here is the another approach which is similar to the approach you have posted earlier...

=IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"")
&IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"")
&IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,"")
&IF(A1="J",10,"").....

Source:-
http://spreadsheetpage.com/index.php..._if_functions/

More info about If()

Using a volatile function in a formula will flag the cell containing the formula as volatile, even if the volatile function never gets executed:

=IF(1<2,99,NOW()) will always return 99 and the volatile NOW() function will never be called, but the cell containing the IF formula will be treated as volatile, (thanks to Stephen Bullen for pointing this out).
If cell A1 contains =NOW() then =IF(1<2,99,A1) will always return 99, but the cell containing the IF formula will NOT be treated as volatile.

Source:-
http://www.decisionmodels.com/calcsecretsi.htm

[snb]

But the most obvious approach is absent in

http://spreadsheetpage.com/index.php..._if_functions/

=code(A1)-64

[snb]

But the most obvious approach is absent in

http://spreadsheetpage.com/index.php..._if_functions/

=CODE(A1)-64

and for

=IF(A2="red",25,IF(A2="white",101,IF(A2="blue",74,0)))

=CHOOSE(LEN(A2),,,25,74,101)

or

=CHOOSE(SEARCH(LEFT(A2),"RBW"),25,74,101)

[JBeaucaire]

Hehehehehehehee, makes me laugh out loud.

[romperstomper]

and for

=IF(A2="red",25,IF(A2="white",101,IF(A2="blue",74,0)))

=CHOOSE(LEN(A2),,,25,74,101)

or

=CHOOSE(SEARCH(LEFT(A2),"RBW"),25,74,101)

I assume this is a joke of some sort since they're clearly not equivalent?

[JBeaucaire]

It is a joke, sort of, but they are certainly equivalent.

http://screencast.com/t/5HkIEmyB

[romperstomper]

No they aren't. Put "black" in A2 for example.

[Speshul]

They would be equivalent in terms of output for the original formula they are presented as awkward alternatives for

=IF(A2="red",25,IF(A2="white",101,IF(A2="blue",74,0)))


Just like mine is also an equivalent, based on result

=INDEX({25;101;98;56;15;85;74;74},VLOOKUP(A2,{"red",-5;"white",-4;"blue",2},COLUMNS(X4:Y57),0)+ROW(X96)-(SUMPRODUCT(45,2)))

[romperstomper]

I repeat my objection.

[JBeaucaire]

I think we have successfully hijacked this thread. (hat-tip)

[snb]

Objection, Your Honour.

[xladept]

I often find myself working on a post and then find that another contributor has posted an answer, usually I post my solution anyway since I had spent time on it