Please help with how to write this formula.

If A1 <= 10 then 500
If A1 10 and <= 20 then 600
If A1 20 and <= 30 then 700

Thanks in advance!

"Saul" wrote:
Please help with how to write this formula.
If A1 <= 10 then 500
If A1 10 and <= 20 then 600
If A1 20 and <= 30 then 700

One possibility, in say B1:

=IF(A1="","",IF(A1<=10,500,IF(AND(A110,A1<=20),60 0,IF(AND(A120,A1<=30),700,"More than 30"))))

I've made some additional criteria assumptions to your post for
completeness. The formula above will return "nothing", ie: "", if A1 is empty
or A1 contains a formula evaluating to "", and it'll return the phrase: More
than 30, if A1 contains a number exceeding 30 (or a formula evaluating to a
number exceeding 30)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi!

One way:

=IF(ISNUMBER(A1),IF(A1<=10,500,IF(A1<=20,600,IF(A1 <=30,700,"Not
defined"))),"")

You haven't defined what to do if A1 30.

Biff

"Saul" wrote in message
...
Please help with how to write this formula.

If A1 <= 10 then 500
If A1 10 and <= 20 then 600
If A1 20 and <= 30 then 700

Thanks in advance!

This one's a few keystrokes shorter:

=IF(ISNUMBER(A1),LOOKUP(A1,{0;11;21;31},{500;600;7 00;""}),"")

Returns a blank "" if A1 30.

Biff

"Biff" wrote in message
...
Hi!

One way:

=IF(ISNUMBER(A1),IF(A1<=10,500,IF(A1<=20,600,IF(A1 <=30,700,"Not
defined"))),"")

You haven't defined what to do if A1 30.

Biff

"Saul" wrote in message
...
Please help with how to write this formula.

If A1 <= 10 then 500
If A1 10 and <= 20 then 600
If A1 20 and <= 30 then 700

Thanks in advance!

Hi

Or maybe shorter still

=500+MIN(2,INT(A1/10))*100

or for 0 if A1 30
=(500+MIN(2,INT(A1/10))*100)*(A1<=30)

--
Regards

Roger Govier


"Biff" wrote in message
...
This one's a few keystrokes shorter:

=IF(ISNUMBER(A1),LOOKUP(A1,{0;11;21;31},{500;600;7 00;""}),"")

Returns a blank "" if A1 30.

Biff

"Biff" wrote in message
...
Hi!

One way:

=IF(ISNUMBER(A1),IF(A1<=10,500,IF(A1<=20,600,IF(A1 <=30,700,"Not
defined"))),"")

You haven't defined what to do if A1 30.

Biff

"Saul" wrote in message
...
Please help with how to write this formula.

If A1 <= 10 then 500
If A1 10 and <= 20 then 600
If A1 20 and <= 30 then 700

Thanks in advance!

"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
---

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
---

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
---

"Ragdyer" wrote:
It all adds to *OUR* knowledge!
OUR ... being all who read these groups.

Couldn't agree more !

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!

*Believe the entire lot of suggestions would enrich all who read/trawl the
newsgroups (Don't leave out the suggestion(s) preferred by the OP <g)

Roger: Thanks for your in-depth comments. Just wanted to say that the
enjoyment is mutual. I too, have enjoyed reading and learning from your many
posts over the years. All the best !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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
---