IF Function

  • Thread starter Thread starter bmistry
  • Start date Start date
B

bmistry

Hi,

I'm trying to insert a function that has more then 7
nested IF statments. Help has told me this is not
possible, but i wondered if there was anyway around this.

Thanks

B Mistry
 
Hi
no way around this limitation but in most cases there're alternative
solutions depending on your requirements:
- multiple IF statements can often be replaced with lookup tables and
VLOOKUP or with an intelligent algorithmn
- You could use defined names for formula parts and use these names
within your formula
- split the formula in different cells

So you may post your current formula or the specification of what
you're trying to achieve to´get a more specific answer :-)
 
Ok, so here is the function as I have it so far. I may be
adding to it...

=IF(AND(L2="National"),IF(OR(K2="Calgary",K2="Calgary
(30620)"),"Calgary",IF(AND(L2="National"),IF(OR
(K2="Toronto - Bay Street (30030)",K2="Toronto
(30010)",K2="Toronto University (30018)",K2="Vaughan
(30016)",K2="Mississauga (30013)",K2="National - 40
University (30993)",K2="National (CCW)
(30991)",K2="National (University) (30990)",K2="North York
(30014)"),"GTA",IF(AND(L2="National"),IF(OR
(K2="Burnaby/New Westminster (30075)",K2="Vancouver
(30077)"),"GVA",IF(L2="National Shared Services","NSS","No
Data")))))))

It's pretty basic: I have a list of locations and I want
to group them. I'm actually using this as a part of a
macro. The macro collects data from an external data
source and then inserts a column where I would like this
formula to be copied across to all records.

Any help is very much appreciated.

Thanks

B
 
Hi
try the following:
1. Setup a separate sheet as lookup range (call this sheet for example
'lookup'). You seem to have two conditions in L2 and K2 so try
something like the following (as I'm not sure about what other
conditions you may have):
A B C
1 National Calgary Calgary
2 National Calgary (30620) Calgary
3 National Toronto GTA
.....

Now use the following array formula (entered with CTRL+SHIFT+ENTER or
if you use VBA assigned to the formulaarray property instead of the
formula property of your range object):
=INDEX('lookup'!$C$1:$C$100,MATCH(1,('lookup'!$A$1:$A$100=L2)*('lookup'
!$B$1:$B$100=K2),0))
 
Back
Top