Complex Formula

"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(A1>10,A1<=20),600,IF(AND(A1>20,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" <[email protected]> wrote in message
news:[email protected]...
> 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;700;""}),"")

Returns a blank "" if A1 >30.

Biff

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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!
>
>

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(A1>10,A1<=20),600,IF(AND(A1>20,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" <[email protected]> wrote in message
news:[email protected]...
> 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;700;""}),"")

Returns a blank "" if A1 >30.

Biff

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> This one's a few keystrokes shorter:
>
> =IF(ISNUMBER(A1),LOOKUP(A1,{0;11;21;31},{500;600;700;""}),"")
>
> Returns a blank "" if A1 >30.
>
> Biff
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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;700;""}),"")

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" <[email protected]> wrote in message
news:[email protected]...
> "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" <[email protected]> wrote in message
news:[email protected]...
> >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;700;""}),"")
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> "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
>> ---
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> >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;700;""}),"")
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> "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
---