Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complicated if function
i need to pose a question that has 4 possible responses. each response will
dictate a cell to have a quantity A1: Fins? B2: response is either; BM, Reno, Stucco, Nail Column B header is QTY A2:BM B2: 2 (if response is BM) 0 (if response is anything else) A3:Reno B3: 2 (if response is Reno) 0 (if response is anything else) A4:Stucco B4: 2 (if response is Stucco) 0 (if response is anything else) how do i create a formula for this? Oh and to make matters worse there is a qty for the sheet. if the qty for the sheet is 1 than the above is correct. if the qty for the sheet is 2 or anything else than B2, B3, and B4 need to be multiplied by that qty*2 ex: (sheet qty is 2 than B2 qty is 4, sheet qty is 3 than B2 is 6) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complicated if function
Presumably sheet qty is in a cell somewhere? Assume it is D1. Put this
formula in B2: =IF(B$1=A2,2,0)*D$1 Then copy into B3:B4 What happens if you select Nail in B1? Hope this helps. Pete On Nov 7, 12:41*am, ForeverH wrote: i need to pose a question that has 4 possible responses. *each response will dictate a cell to have a quantity A1: Fins? * B2: response is either; BM, Reno, Stucco, Nail * * * * * * * *Column B header is QTY A2:BM * * * B2: 2 (if response is BM) 0 (if response is anything else) A3:Reno * *B3: 2 (if response is Reno) 0 (if response is anything else) A4:Stucco *B4: 2 (if response is Stucco) 0 (if response is anything else) how do i create a formula for this? * Oh and to make matters worse there is a qty for the sheet. *if the qty for the sheet is 1 than the above is correct. * if the qty for the sheet is 2 or anything else than B2, B3, and B4 need to be multiplied by that qty*2 ex: (sheet qty is 2 than B2 qty is 4, sheet qty is 3 than B2 is 6) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complicated if function
Thank you! that worked.
- if the response is nail there will be no qtys to formulate (nail requires nothing done) Thanks again "Pete_UK" wrote: Presumably sheet qty is in a cell somewhere? Assume it is D1. Put this formula in B2: =IF(B$1=A2,2,0)*D$1 Then copy into B3:B4 What happens if you select Nail in B1? Hope this helps. Pete On Nov 7, 12:41 am, ForeverH wrote: i need to pose a question that has 4 possible responses. each response will dictate a cell to have a quantity A1: Fins? B2: response is either; BM, Reno, Stucco, Nail Column B header is QTY A2:BM B2: 2 (if response is BM) 0 (if response is anything else) A3:Reno B3: 2 (if response is Reno) 0 (if response is anything else) A4:Stucco B4: 2 (if response is Stucco) 0 (if response is anything else) how do i create a formula for this? Oh and to make matters worse there is a qty for the sheet. if the qty for the sheet is 1 than the above is correct. if the qty for the sheet is 2 or anything else than B2, B3, and B4 need to be multiplied by that qty*2 ex: (sheet qty is 2 than B2 qty is 4, sheet qty is 3 than B2 is 6) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complicated if function
You're welcome - thanks for feeding back.
Pete On Nov 7, 1:27*am, ForeverH wrote: Thank you! *that worked. * - if the response is nail there will be no qtys to formulate (nail requires nothing done) Thanks again "Pete_UK" wrote: Presumably sheet qty is in a cell somewhere? Assume it is D1. Put this formula in B2: =IF(B$1=A2,2,0)*D$1 Then copy into B3:B4 What happens if you select Nail in B1? Hope this helps. Pete On Nov 7, 12:41 am, ForeverH wrote: i need to pose a question that has 4 possible responses. *each response will dictate a cell to have a quantity A1: Fins? * B2: response is either; BM, Reno, Stucco, Nail * * * * * * * *Column B header is QTY A2:BM * * * B2: 2 (if response is BM) 0 (if response is anything else) A3:Reno * *B3: 2 (if response is Reno) 0 (if response is anything else) A4:Stucco *B4: 2 (if response is Stucco) 0 (if response is anything else) how do i create a formula for this? * Oh and to make matters worse there is a qty for the sheet. *if the qty for the sheet is 1 than the above is correct. * if the qty for the sheet is 2 or anything else than B2, B3, and B4 need to be multiplied by that qty*2 ex: (sheet qty is 2 than B2 qty is 4, sheet qty is 3 than B2 is 6)- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with a complicated function | Excel Worksheet Functions | |||
Complicated Function Question | Excel Discussion (Misc queries) | |||
Complicated Sum Product Function | Excel Worksheet Functions | |||
Complicated lookup function | Excel Worksheet Functions | |||
complicated function in excel | Excel Worksheet Functions |