How can I create a formula out of more than 7 functions in excel?

D

Dolores

I am working with excel (office 2003) mainly based on "text-cells". I have
created a formula where I can nestl up to 7 funktions but not more! Goal is
to sort out of a certain text, in a cell, a certain word that has to be shown
at the end.
eg:
=IF(ISNUMBER(FIND("CHANGE",J11)),"CHANGE
ORDER",(IF(ISNUMBER(FIND("NOTICE",J11)),"NOTICE",(IF(ISNUMBER(FIND("NCR",J11)),"NCR",(IF(ISNUMBER(FIND("Plus/Minus",J11)),"PLUS/MINUS",(IF(ISNUMBER(FIND("Minutes",J11)),"MOM",(IF(ISNUMBER(FIND("RFI",J11)),"RFI")))))))))))&IF(B11="","","")
 
N

N Harkawat

in a separate are in the sheet make 2 columns
in the first column say Col A type your searched values like
"CHANGE","NCR","Plus/Minus"
In the column next to COL B it type their replacement values " CHANGE
ORDER", NCR etc

=INDEX($B$1:$B$4,MATCH(TRUE,ISNUMBER(SEARCH("*"&J11&"*","*"&A1:A4&"*")),0))&IF(B11="","","")
array entered (ctrl+shift+enter)

This way there is no restriction of 7
 
K

Kassie

Hi Dolores

Seems to me that =IF(J11="","",IF(J11="CHANGE","CHANGE
ORDER",IF(J11="Plus/Minus","PLUS/MINUS",IF(J11="Minutes","MOM",J11)))) will
do the trick equally well. However, if you really need to go further, you
can get past the 7nested IF issue in a number of ways
 
D

Dolores

Hello and thanks for advise... but there is still some error as it shows me a
#N/A!

if I put:

=INDEX($B$1:$B$4,MATCH(TRUE,ISNUMBER(SEARCH("*"&J11&"*","*"&Specs!S2:S14&"*")),0))&IF(B11="","","")
array entered (ctrl+shift+enter)

The column in the "Specs" sheet has all the information that has to be
searched for in column "J" and if any of these words are find it should show
General instead of the searched word...

maybe you have another idea.. I am sitting here in Chile and quite lost as
nobody can help me!!!

Dolores
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top