It all adds to *OUR* knowledge!

OUR ... being all who read these groups.

The OPs get their answer from any of the suggestions that they can
comprehend.

The balance is for everyone else to assimilate and/or enjoy!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
I"Roger Govier" wrote in message
...
Max

You have a very valid point.
It is sometimes "fun" to try to minimise a formula result, which can(?) be
appreciated amongst regulars, but equally can leave the OP totally
confused. I hold my hands up as "Guilty" on this occasion, in this
respect.

On the other hand, alternative constructs to solve a problem can be useful
in learning new techniques, and I have certainly learnt a lot from the
sometimes "offbeat" responses, from Dana, JE, Harlan and others (yourself
and Biff included of course <bg), where approaching the problem from a
different perspective can be quite enlightening.
For my own part, I nearly always work through a different construct to
understand how and why the result is returned, but I cannot (should not)
assume that others will do the same.
Sometimes (when I have the time), I do take the time to explain the logic
behind the proposed solution, but equally, I can often think the reader
may think I am "trying to teach my granny to suck eggs".
How to get the balance right, that is the question, and I for one don't
know the answer.

Biff
In answer to your "nitpick", null is <=10 hence the 500 result is true.
You are right, however, but it can be resolved with
=((500+MIN(2,INT(A1/10))*100)*(A1<=30)*ISNUMBER(A1))
but then the formula is beginning to approach the length of other
solutions <vbg

I thank you both for all of your frequent and interesting posts which I
read with interest.

--
Regards

Roger Govier


"Biff" wrote in message
...
Are / Should we always be in the race to be shortest here ?

As a general rule, (I guess) shorter is better *BUT* only if you
understand the formula!

Will the OP understand these:

=500+MIN(2,INT(A1/10))*100
=IF(ISNUMBER(A1),LOOKUP(A1,{0;11;21;31},{500;600;7 00;""}),"")

Based on the subject of the post I would guess not since this can be
solved using fairly basic IF constructs and they didn't know how to do
that. Also, what if they have to add another condition?

Just to nit pick a little.......the shortest doesn't account for an empty
cell.

Biff

"Max" wrote in message
...
"Roger Govier" wrote:
Or maybe shorter still
=500+MIN(2,INT(A1/10))*100 ..

.. and it doesn't even have an IF inside <g

but .. just for general discussions' sake:
Are / Should we always be in the race to be shortest here ?

From my reading, it appears OP has some knowledge on using IF but
probably*
just wants some examples of how to formulate nested IFs together with
AND /
OR (etc) to handle dual/multiple condition checks, etc
*albeit OP's subject line says: Complex Formula <g

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---