Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula for 13 plus variables
I am building a price sheet that I need to come up with a formula that can
figure a material coat if the part is one of 13 different types of material. I want to be able to update my material pricing on sheet 2 andd have the sheet recalculate it automaticly for each part. I have "A" column as Material type(abreviation like SS for stainless steel), "B" column as weight of the part, and Sheet 2 column "A" as the price for the material. My formula (column C")looks like : =IF(A1="SS",B1*sheet2!A1,.....) and so forth for each of the 13 types of material. I can get it to work for the first eight types of material but anything past that it gives me an error. I also would like to display "error" in the cell if someone does not enter the material abbreviation correctly . Any help would be greatly appreciated. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula for 13 plus variables
Here is what I would suggest:
On Sheet 2, list all of your materials in Column A and corresponding prices in column B. Now, for your formula in Column C of Sheet 1: =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) *B1),"Error",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE )*B1) I just assumed 100 rows in this example, you may have more or less. Adjust accordingly. HTH, Elkar "N Dependable" wrote: I am building a price sheet that I need to come up with a formula that can figure a material coat if the part is one of 13 different types of material. I want to be able to update my material pricing on sheet 2 andd have the sheet recalculate it automaticly for each part. I have "A" column as Material type(abreviation like SS for stainless steel), "B" column as weight of the part, and Sheet 2 column "A" as the price for the material. My formula (column C")looks like : =IF(A1="SS",B1*sheet2!A1,.....) and so forth for each of the 13 types of material. I can get it to work for the first eight types of material but anything past that it gives me an error. I also would like to display "error" in the cell if someone does not enter the material abbreviation correctly . Any help would be greatly appreciated. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula for 13 plus variables
Hi!
Here's another way to write that formula: =IF(COUNTIF(Sheet2!A$1:A$100,A1),VLOOKUP(A1,Sheet2 !A$1:B$100,2,0)*B1),"Error") Biff "Elkar" wrote in message ... Here is what I would suggest: On Sheet 2, list all of your materials in Column A and corresponding prices in column B. Now, for your formula in Column C of Sheet 1: =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) *B1),"Error",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE )*B1) I just assumed 100 rows in this example, you may have more or less. Adjust accordingly. HTH, Elkar "N Dependable" wrote: I am building a price sheet that I need to come up with a formula that can figure a material coat if the part is one of 13 different types of material. I want to be able to update my material pricing on sheet 2 andd have the sheet recalculate it automaticly for each part. I have "A" column as Material type(abreviation like SS for stainless steel), "B" column as weight of the part, and Sheet 2 column "A" as the price for the material. My formula (column C")looks like : =IF(A1="SS",B1*sheet2!A1,.....) and so forth for each of the 13 types of material. I can get it to work for the first eight types of material but anything past that it gives me an error. I also would like to display "error" in the cell if someone does not enter the material abbreviation correctly . Any help would be greatly appreciated. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula for 13 plus variables
Thank you Thank you Thank you!!!!!!!!
-- Nathan Hovious "Elkar" wrote: Here is what I would suggest: On Sheet 2, list all of your materials in Column A and corresponding prices in column B. Now, for your formula in Column C of Sheet 1: =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) *B1),"Error",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE )*B1) I just assumed 100 rows in this example, you may have more or less. Adjust accordingly. HTH, Elkar "N Dependable" wrote: I am building a price sheet that I need to come up with a formula that can figure a material coat if the part is one of 13 different types of material. I want to be able to update my material pricing on sheet 2 andd have the sheet recalculate it automaticly for each part. I have "A" column as Material type(abreviation like SS for stainless steel), "B" column as weight of the part, and Sheet 2 column "A" as the price for the material. My formula (column C")looks like : =IF(A1="SS",B1*sheet2!A1,.....) and so forth for each of the 13 types of material. I can get it to work for the first eight types of material but anything past that it gives me an error. I also would like to display "error" in the cell if someone does not enter the material abbreviation correctly . Any help would be greatly appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Creating a formula that will SUM with 2 Variables | Excel Worksheet Functions | |||
"Unable to set the Formula property of the Series class" with a tw | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
How to use array formula for three variables? | Excel Discussion (Misc queries) |