I see things like this, and I wonder...

[Tazguy37]

is it just that they have a different thought process than I do?
OR
do they think, "It works...why change it?"

I saw this formula in an Excel worksheet at work just now:
=IF(G19=0,4,IF(G19=1,3,IF(G19=2,2,IF(G19=3,1,IF(G19=4,1,IF(G19>=5,0))))))

That's six, count 'em, six IF statements. I shortened it to:
=IF(G18>=5,0,IF(G18=4,1,4-G18))

If anybody has another formula with two or less functions in it, by all means -- share it. But seeing as how I don't know who generated the formulas, I'll probably never know the answer to my query. I was just interested to see what everybody else thought.

 

[lenze]

How about =IF(G19<5,CHOOSE(G19+1,4,3,2,1,1),0)

lenze

 

[Greg Truby]

Maybe:<ul>[*]=LOOKUP(G19,{0,1,2,3,4,5},{4,3,2,1,1,0})[*]=4-MIN(G19,5)+(G19>3)[/list]

 

[NateO]

Hello,

They probably don't know any better...

How about zero functions? Perhaps:

=4-G19+(G19>3)

Assuming G19 is validated... Or:

=(4-G19+(G19>3))*(G19<6)

If not...

 

[Von Pookie]

They probably don't know any better...

That was my initial reaction. Not everyone has the same knowledge/skill level with Excel.

I know that *I* wouldn't be able to come up with any of these alternative formulas (edit: formulae, whatever) off of the top of my head. What I usually do: put in a formula that I know will work--such as the nested IF statement in question here--just so it's done. Then from that point, I work on finding possible ways to shorten it (and still have it return the correct results)

 

[Greg Truby]

Geez, ya made me delete the smartaxx reply I had all ready to go by coming back and posting that 2^nd version...

I was trying to come up with one that took no function calls, myself, and just fell short. Nice one (the second one).

 

[NateO]

That was my initial reaction. Not everyone has the same knowledge/skill level with Excel.

And you're probably right, Pook. My reaction/response comes from my experience. I started out writing functional, yet, perhaps questionable functions and vba, as my experience grew, so did the quality.

And you have to keep in mind, not everyone wants to learn to fish, so they'll continue to write goofy looking stuff that perhaps I, at this point, wouldn't touch with a 10-foot pole.

Thanks Greg, I knew I better post the 2nd function...

Pretty close to your constructs, with a little boolean trickery.