| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement to calculate based on cell values
I am trying to get a cell to run a multiplication calculation on two cells
(i17 and k17), where one cell (b17) contains a fixed value ("S") or b17 contains a fixed value ("F") AND c17 has a value greater than 3. can anyone help me with the expression I should use? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statement to calculate based on cell values
try
=if(and(or(B17="S",B17="F"),C173),I17*K17,otherwi se) "Jos" wrote: I am trying to get a cell to run a multiplication calculation on two cells (i17 and k17), where one cell (b17) contains a fixed value ("S") or b17 contains a fixed value ("F") AND c17 has a value greater than 3. can anyone help me with the expression I should use? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statement to calculate based on cell values
=SUMPRODUCT(((--(B17="S"))+(--(B17="F")))*--(C17=3)*(I17*K17))
+ is OR * is AND -- converts a TRUE to a 1 or =IF(AND(OR(B17="S",B17="F"),C17=3),I17*K17,0) On Jan 30, 9:17 am, Jos wrote: I am trying to get a cell to run a multiplication calculation on two cells (i17 and k17), where one cell (b17) contains a fixed value ("S") or b17 contains a fixed value ("F") AND c17 has a value greater than 3. can anyone help me with the expression I should use? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statement to calculate based on cell values
....and...another option:
=IF(AND(OR((B17={"S","F"})),C173),I17*K17,"") Does that help? *********** Regards, Ron XL2002, WinXP "Jos" wrote: I am trying to get a cell to run a multiplication calculation on two cells (i17 and k17), where one cell (b17) contains a fixed value ("S") or b17 contains a fixed value ("F") AND c17 has a value greater than 3. can anyone help me with the expression I should use? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statement to calculate based on cell values
Hi Ron,
many thanks for your help. It works for the clause where B17="F" and C173 but i17*k17 should apply just where b17="S" (ie. C17 doesn't have to be greater than 3 with the value "S") Jo x "Ron Coderre" wrote: ...and...another option: =IF(AND(OR((B17={"S","F"})),C173),I17*K17,"") Does that help? *********** Regards, Ron XL2002, WinXP "Jos" wrote: I am trying to get a cell to run a multiplication calculation on two cells (i17 and k17), where one cell (b17) contains a fixed value ("S") or b17 contains a fixed value ("F") AND c17 has a value greater than 3. can anyone help me with the expression I should use? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statement to calculate based on cell values
Hi Jason,
This works fine but the multiplaction between i17 and k17 happens just when b17="S". The link to c17 being greater than 3 only applies when the value in B17 ="F". Jo xx "Jason Lepack" wrote: =SUMPRODUCT(((--(B17="S"))+(--(B17="F")))*--(C17=3)*(I17*K17)) + is OR * is AND -- converts a TRUE to a 1 or =IF(AND(OR(B17="S",B17="F"),C17=3),I17*K17,0) On Jan 30, 9:17 am, Jos wrote: I am trying to get a cell to run a multiplication calculation on two cells (i17 and k17), where one cell (b17) contains a fixed value ("S") or b17 contains a fixed value ("F") AND c17 has a value greater than 3. can anyone help me with the expression I should use? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statement to calculate based on cell values
OK....how about this:
=IF(OR(B17="S",AND(B17="F",C173)),I17*K17,"") or, maybe this =((B17="S")+(B17="F")*(C173))*(I17*K17) Does that help? *********** Regards, Ron XL2002, WinXP "Jos" wrote: Hi Ron, many thanks for your help. It works for the clause where B17="F" and C173 but i17*k17 should apply just where b17="S" (ie. C17 doesn't have to be greater than 3 with the value "S") Jo x "Ron Coderre" wrote: ...and...another option: =IF(AND(OR((B17={"S","F"})),C173),I17*K17,"") Does that help? *********** Regards, Ron XL2002, WinXP "Jos" wrote: I am trying to get a cell to run a multiplication calculation on two cells (i17 and k17), where one cell (b17) contains a fixed value ("S") or b17 contains a fixed value ("F") AND c17 has a value greater than 3. can anyone help me with the expression I should use? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statement to calculate based on cell values
Hi Ron,
yes - thank you! Jo xx "Ron Coderre" wrote: OK....how about this: =IF(OR(B17="S",AND(B17="F",C173)),I17*K17,"") or, maybe this =((B17="S")+(B17="F")*(C173))*(I17*K17) Does that help? *********** Regards, Ron XL2002, WinXP "Jos" wrote: Hi Ron, many thanks for your help. It works for the clause where B17="F" and C173 but i17*k17 should apply just where b17="S" (ie. C17 doesn't have to be greater than 3 with the value "S") Jo x "Ron Coderre" wrote: ...and...another option: =IF(AND(OR((B17={"S","F"})),C173),I17*K17,"") Does that help? *********** Regards, Ron XL2002, WinXP "Jos" wrote: I am trying to get a cell to run a multiplication calculation on two cells (i17 and k17), where one cell (b17) contains a fixed value ("S") or b17 contains a fixed value ("F") AND c17 has a value greater than 3. can anyone help me with the expression I should use? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hide cell values based on a condition in another cell | Excel Worksheet Functions | |||
| VBA: For Count, when count changes from cell to cell | Excel Discussion (Misc queries) | |||
| Cell References | Excel Discussion (Misc queries) | |||
| if cell has date value how to calculate other cell values | Excel Discussion (Misc queries) | |||
| Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Linear Mode
