More then 7 IF functions?

[CdFMarshall]

Hi all,

I am new here and found this forum because, as most persons here
guess, I need some help in Excel.
I have been building a worksheet for my job information but get stuck.

What is the problem? I'll try and explain:
I am building an overview of ingredients of petfood. It should finall
result in a sheet in which you can fill in a value and the sheet say
it is ok or not.

In one sheet I have therefore lined out a couple of sorts petfood
(junior, adult, and so on) with the values of the ingredients it woul
contain in an ideal situation (for instance, vitamines of the standar
food with a minimum, maximum and mean value). I hope this part i
clear, please ask if it's not.

In the second sheet, the values which I find in a batch are filled in
It shows as following:
date-batchcode-productcode-productname-and then the values I have foun
for the ingredients followed by a correct / incorrect
It contains multiple functions.
The first function is the function that allows you to only insert th
productcode and excel will automatically fill in the productname.
created this function for it:

=IF(C4="010";"product A";IF(C4="015";"product B";IF(C4="020";"produc
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"produc
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))

Uptill the moment of product G it works fine, but then I get product H
which is the 8th product and therefore excel can not put it in one lin
anymore (as excel can only include 7 IF functions) so it automaticall
gives an INCORRECT.

First question is therefore: Is there a way to change this so I ca
include more then 7 products in the function line?


Then, I'll continue, If this is possible: How can I recreate this to a
automatic function which automatically takes the values of sheet 1 fo
that specific product into account?
So when I insert a date, batchcode, a productcode (at this moment exce
should automatically insert the productname) and then insert a valu
which I found for that batch, excel should automatically compare thi
to that specific value in sheet 1...

I hope you know what I mean and furthermore I of course hope you ca
help me because I almost gave up on this already....

Thanks!

CdFMarshall
I need a worksheet which contains in one sheet stats that can b
considered normal

 

[Guest]

You need the VLOOKUP() function. With Col A & B set up to hold the codes &
prod desc, like so, starting in row 2

010 prodcut A
015 product B
020 product C

and so on, you'd use the formula

=VLOOKUP(C4,$a$2:$b$10,2,false)

This would return an error if the value in C4 didn't appear in column a.
Get around that by using this variation

=IF(ISNA(VLOOKUP(C4,$a$2:$b$10,2,false)),"Incorrect",
VLOOKUP(C4,$a$2:$b$10,2,false))

 

[Bob Phillips]

Try a lookup

Create a table

010 product A
015 product B
020 product C
030 product D
040 product E
050 product F
060 product G
070 product H

and use

=IF(ISNA(VLOOKUP(C2;M1:N8;2;False));"INCORRECT",VLOOKUP(C2;M1:N8;2;False))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"CdFMarshall" <[email protected]>
wrote in message

 

[Bob Umlas]

Change =IF(C4="010";"product A";IF(C4="015";"product B";IF(C4="020";"product
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"product
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))
to
=IF(C4="010";"product A";"")&IF(C4="015";"product
B";"")&IF(C4="020";"product
C";"")&IF(C4="030";"product D";"")&IF(C4="040";"product
E";"")&IF(C4="050";"product
F";"")&IF(C4="060";"product G";"")&IF(C4="070";"product H";"")

better yet, make a table to look up the value:
010 product A
015 product B
020 product C
etc.
Suppose this is range F1:G10. You can use
=IF(ISERROR(VLOOKUP(C4,F1:G10,2,FALSE)),"INCORRECT",VLOOKUP(C4,F1:G10,2,FALS
E))
and this can be as large as you need.
HTH
Bob Umlas

"CdFMarshall" <[email protected]>
wrote in message

 

[PCLIVE]

The first thing I would do is create a table containing your product codes
and numbers.

Example: In D1 to D8, enter:
010
015
020
030
040
050
060
070

In E1 to E8, enter:
product A
product
product
product
product
product
product

"CdFMarshall" <[email protected]>
wrote in message

 

[Pete_UK]

You can replace your formula with a much simpler VLOOKUP, and this will
allow you to have many more products. First of all set up a two-column
table somewhere (assume Y1 to Z30 on the same Sheet1). In column Y you
would enter '010, '015, '020, '030 etc down the column, and in column Z
you would have product A, product B, product C etc adjacent to the
codes.

Then your formula can be replaced with this one:

=VLOOKUP(C4,Y$1:Z$30,2,0)

This takes the value in C4 and sees if there is an exact match in the
first column of the table Y1:Z30 - if there is it then returns the
value which is in the second column of the table (this is the parameter
2 in the formula). If there is not an exact match the formula returns
the #N/A error.

Hope this helps.

Pete

 

[PCLIVE]

My reply was sent inadvertently prior to being complete. I was suggesting
the Lookup function. However, I see someone has now already suggested that.
So you should have what you need.

"CdFMarshall" <[email protected]>
wrote in message

 

[CdFMarshall]

You guys rule!
I never even knew this function, wow! It did needed some translation to
find out what you guys meant, but it works! Thanks!

Ok, so part 1 of the question is solved. It now indeed gives a nice
productname whenever I fill in a value.
Now the, in my opinion even more tricky part of the question.

I'll draw it out for you;
I now have in sheet 2 colomn C the productcodes which I fill in, in
colomn D it now autimatically gives the productname with the function
you guys gave me, but now I have 5 ingredients of the product from
which I take samples. Every ingredient gives a value which I add to
this sheet (for instance vitamin D has a value of 50 in the test, this
is added in colomn E).
In another sheet (sheet 1) I have an overview of the minimum, maximum
and mean value of every ingredient (suppose for vitamin D this is 20,
70 and 45). In the sheet 2 colomn F, it should give an outcome whether
the filled in value is within the range of minimum and maximum
mentioned in sheet 1.

So it is:

C - D - E - F
productcode - productname - filled in value - correct/incorrect
(comparison with sheet1)

Thats the last part, you have any idea how this can be done?

Thanks a lot again!!! You guys are the greatest!

CdF Marshall

 

[Bob Phillips]

Just use VLOOKUP again

=IF(AND(VLOOKUP(C2,Sheet1!A1:C10,2,False)>=E2,VLOOKUP(C2,Sheet1!A1:C10,3,Fal
se)<=E2),"In range","Out of range")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"CdFMarshall" <[email protected]>
wrote in message
news:[email protected]...

 

[CdFMarshall]

Bob,

Thanks for your response (have been ill a couple of days so couldnt
respons sooner) but I don't get it (still have to convert it to the
dutch language )...

You say, use a combination of IF, AND and VLOOKUP...:

=IF(AND(VLOOKUP(C2,Sheet1!A1:C10,2,False)>=E2,VLOO
KUP(C2,Sheet1!A1:C10,3,False)<=E2),"In range","Out of range")

But I don't get it.
What you say in my reading is: If the value in E2 (I suppose thats the
value I fill in?) is both equal/smaller then and equal/bigger then the
values in sheet 1-A1 till C10 column 2 and 3 (I suppose with this you
mean bigger/equal then minimum and smaller/equal then maximum value)
excel should mention "in range".
Ok, if thats what you mean, then I get that. But how do you make excell
look in the correct row that is combined with that product?

For example, if I fill in productcode 010 in sheet 2, excel now
mentions automatically the productname in the next cell. Ok, nice, but
in sheet 1 all products are mentioned below eachother with all
different minimum and maximum values for, for eaxample, vitamin B.
So if column A of sheet 2 is the batchcode I fill in, column B would be
the productcode (010 and so on) and column C would be the productname
(which excel automatically pops up). Column D would then be the value
of the ingredient (take vitamin B) which I found in the product and
which I fill in. In column E would then be mentioned if this value is
in range/out of range. But for this excel should look at the values
specifically for that product (010 for instance) and not for all
minimum and maximum values. Because the value could be ok for product C
(030) but out of range of product A (010) and then it should give an out
of range...

You understand what I mean? (it is hard to explain)

Thanks again for any response!

Marshall

 

[CdFMarshall]

I understand from the radio silence that no one knows? Do I really need
to use acces for this? I resent Acces...

CdFMarshall

 

[PCLIVE]

There have been several postings regarding a solution for you. Are you
unable to see them? See below.


----- Original Message -----
From: "Duke Carey" <[email protected]>
Newsgroups: microsoft.public.excel.worksheet.functions
Sent: Thursday, March 02, 2006 2:31 PM
Subject: RE: More then 7 IF functions?

You need the VLOOKUP() function. With Col A & B set up to hold the codes &
prod desc, like so, starting in row 2

010 prodcut A
015 product B
020 product C

and so on, you'd use the formula

=VLOOKUP(C4,$a$2:$b$10,2,false)

This would return an error if the value in C4 didn't appear in column a.
Get around that by using this variation

=IF(ISNA(VLOOKUP(C4,$a$2:$b$10,2,false)),"Incorrect",
VLOOKUP(C4,$a$2:$b$10,2,false))

________________________________________________________

----- Original Message -----
From: "Bob Phillips" <[email protected]>
Newsgroups: microsoft.public.excel.worksheet.functions
Sent: Thursday, March 02, 2006 2:33 PM
Subject: Re: More then 7 IF functions?

Try a lookup

Create a table

010 product A
015 product B
020 product C
030 product D
040 product E
050 product F
060 product G
070 product H

and use

=IF(ISNA(VLOOKUP(C2;M1:N8;2;False));"INCORRECT",VLOOKUP(C2;M1:N8;2;False))


--

HTH

Bob Phillips

_______________________________________________________________-

----- Original Message -----
From: "Bob Umlas" <[email protected]>
Newsgroups: microsoft.public.excel.worksheet.functions
Sent: Thursday, March 02, 2006 2:34 PM
Subject: Re: More then 7 IF functions?

Change =IF(C4="010";"product A";IF(C4="015";"product
B";IF(C4="020";"product
C";IF(C4="030";"product D";IF(C4="040";"product E";IF(C4="050";"product
F";IF(C4="060";"product G";IF(C4="070";"product H";"INCORRECT"))))))))
to
=IF(C4="010";"product A";"")&IF(C4="015";"product
B";"")&IF(C4="020";"product
C";"")&IF(C4="030";"product D";"")&IF(C4="040";"product
E";"")&IF(C4="050";"product
F";"")&IF(C4="060";"product G";"")&IF(C4="070";"product H";"")

better yet, make a table to look up the value:
010 product A
015 product B
020 product C
etc.
Suppose this is range F1:G10. You can use
=IF(ISERROR(VLOOKUP(C4,F1:G10,2,FALSE)),"INCORRECT",VLOOKUP(C4,F1:G10,2,FALS
E))
and this can be as large as you need.
HTH
Bob Umlas

_______________________________________________________________-

----- Original Message -----
From: "Pete_UK" <[email protected]>
Newsgroups: microsoft.public.excel.worksheet.functions
Sent: Thursday, March 02, 2006 2:36 PM
Subject: Re: More then 7 IF functions?

You can replace your formula with a much simpler VLOOKUP, and this will
allow you to have many more products. First of all set up a two-column
table somewhere (assume Y1 to Z30 on the same Sheet1). In column Y you
would enter '010, '015, '020, '030 etc down the column, and in column Z
you would have product A, product B, product C etc adjacent to the
codes.

Then your formula can be replaced with this one:

=VLOOKUP(C4,Y$1:Z$30,2,0)

This takes the value in C4 and sees if there is an exact match in the
first column of the table Y1:Z30 - if there is it then returns the
value which is in the second column of the table (this is the parameter
2 in the formula). If there is not an exact match the formula returns
the #N/A error.

Hope this helps.

Pete

_______________________________________________________________-



"CdFMarshall" <[email protected]>
wrote in message

 

[EricCartman]

Perhaps you could try to chunk your condition to several conditions then
combine each on a separate cell.