14 Clients
 

Column P has a picklist of 14 clients. With exception of 2 they are charged a
standard rate of $26, the exceptions are $25 for hackney and $30 for lewis.
Howcan I write a better formula than the one below?
=IF(L3="Hackney",25,IF(L3="Lewisham",30,IF(L3="Pin nacle",26))) ... which
returns a FALSE because I cannot include all the arguments.

14 Clients
 

On Wed, 18 Oct 2006 03:20:02 -0700, Michell Major
wrote:

Column P has a picklist of 14 clients. With exception of 2 they are charged a
standard rate of $26, the exceptions are $25 for hackney and $30 for lewis.
Howcan I write a better formula than the one below?
=IF(L3="Hackney",25,IF(L3="Lewisham",30,IF(L3="Pi nnacle",26))) ... which
returns a FALSE because I cannot include all the arguments.

Build a table with names in one column and rates in adjacent column. Then use
VLOOKUP (see HELP for details).


--ron

14 Clients
 

Hi Michell

You have raised 3 questions in 3 separate threads. It might have been
better to combine them into one.

=IF(L3="",0,IF(L3="Hackney",25,IF(L3="Lewisham",30 ,26)))
will return 26 for any value in L3 other than Null which will return 0
or the 2 exceptions you outline

Sometimes we need to add $10 for a priority callout across the board.
Suggestions please on how ... IF(J3=P then add $10

=IF(L3="",0,IF(L3="Hackney",25,IF(L3="Lewisham",30 ,26)))+(J3="P")*10

=IF(K15="Private Job","S",IF(K15="",""))
I want it to return a void ("") for any other value in K15

=IF(K15="Private Job","S","")


--
Regards

Roger Govier


"Michell Major" wrote in
message ...
Column P has a picklist of 14 clients. With exception of 2 they are
charged a
standard rate of $26, the exceptions are $25 for hackney and $30 for
lewis.
Howcan I write a better formula than the one below?
=IF(L3="Hackney",25,IF(L3="Lewisham",30,IF(L3="Pin nacle",26))) ...
which
returns a FALSE because I cannot include all the arguments.

14 Clients
 

You only need the IFs to pick up the exceptions - the other clients can
default to one rate, so something like this should do what you want:

=IF(L3="Hackney",25,IF(L3="Lewisham",30,26))

You might like to amend it to:

=IF(L3="",0,IF(L3="Hackney",25,IF(L3="Lewisham",30 ,26)))

so that you return zero if L3 is empty (more useful if you copy the
formula down).

Hope this helps.

Pete

Michell Major wrote:
Column P has a picklist of 14 clients. With exception of 2 they are charged a
standard rate of $26, the exceptions are $25 for hackney and $30 for lewis.
Howcan I write a better formula than the one below?
=IF(L3="Hackney",25,IF(L3="Lewisham",30,IF(L3="Pin nacle",26))) ... which
returns a FALSE because I cannot include all the arguments.

14 Clients
 

Wow thanks! Very kind of you.

"Roger Govier" wrote:

Hi Michell

You have raised 3 questions in 3 separate threads. It might have been
better to combine them into one.

=IF(L3="",0,IF(L3="Hackney",25,IF(L3="Lewisham",30 ,26)))
will return 26 for any value in L3 other than Null which will return 0
or the 2 exceptions you outline

Sometimes we need to add $10 for a priority callout across the board.
Suggestions please on how ... IF(J3=P then add $10

=IF(L3="",0,IF(L3="Hackney",25,IF(L3="Lewisham",30 ,26)))+(J3="P")*10

=IF(K15="Private Job","S",IF(K15="",""))
I want it to return a void ("") for any other value in K15

=IF(K15="Private Job","S","")


--
Regards

Roger Govier


"Michell Major" wrote in
message ...
Column P has a picklist of 14 clients. With exception of 2 they are
charged a
standard rate of $26, the exceptions are $25 for hackney and $30 for
lewis.
Howcan I write a better formula than the one below?
=IF(L3="Hackney",25,IF(L3="Lewisham",30,IF(L3="Pin nacle",26))) ...
which
returns a FALSE because I cannot include all the arguments.