Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND statements
I'm seeking a formula for the following problem:
IF G2 = "a" or "a3" or "m" or "m3" or "ftm" AND H2 and K2 are populated with any value (that is, they are not blank cells), then the formula should return a value of 0.5 BUT if the G2 equals any of the above values AND H2 is populated with any value AND K2 is blank (has nothing in the cell), then the formula should return a value of 1 BUT if the G2 equals any of the above values AND K2 is populated with any value AND H2 is blank (has nothing in the cell), then the formula should return a value of 1 Can anyone help? many thanks, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND statements
Try
=IF(OR(G2="a",G2="a3",G2="m",G2="m3",G2="ftm"),IF( AND(H2="",K2=""),0.5,1),"No match") or the longer version =IF(AND(OR(G2="a",G2="a3",G2="m",G2="m3",G2="ftm") ,H2="",K2=""),0.5,IF(AND(OR(G2="a",G2="a3",G2="m", G2="m3",G2="ftm"),OR(H2<"",K2<"")),1,"No match")) "bob" wrote: I'm seeking a formula for the following problem: IF G2 = "a" or "a3" or "m" or "m3" or "ftm" AND H2 and K2 are populated with any value (that is, they are not blank cells), then the formula should return a value of 0.5 BUT if the G2 equals any of the above values AND H2 is populated with any value AND K2 is blank (has nothing in the cell), then the formula should return a value of 1 BUT if the G2 equals any of the above values AND K2 is populated with any value AND H2 is blank (has nothing in the cell), then the formula should return a value of 1 Can anyone help? many thanks, Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND statements
Maybe
=IF(AND(OR(G2="a",G2="a3",G2="m",G2="ftm"),H2<"", K2<""),0.5,1) Mike "bob" wrote: I'm seeking a formula for the following problem: IF G2 = "a" or "a3" or "m" or "m3" or "ftm" AND H2 and K2 are populated with any value (that is, they are not blank cells), then the formula should return a value of 0.5 BUT if the G2 equals any of the above values AND H2 is populated with any value AND K2 is blank (has nothing in the cell), then the formula should return a value of 1 BUT if the G2 equals any of the above values AND K2 is populated with any value AND H2 is blank (has nothing in the cell), then the formula should return a value of 1 Can anyone help? many thanks, Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND statements
=IF(OR(G2="a",G2="a3",G2="m",G2="m3",G2="ftm"),IF( H2<"",IF(K2<"",0.5,1),IF(K2<"",1,"H2
and K2 both blank so answer undefined")),"G2 not one of specified values so answer undefined") -- David Biddulph "bob" wrote in message ... I'm seeking a formula for the following problem: IF G2 = "a" or "a3" or "m" or "m3" or "ftm" AND H2 and K2 are populated with any value (that is, they are not blank cells), then the formula should return a value of 0.5 BUT if the G2 equals any of the above values AND H2 is populated with any value AND K2 is blank (has nothing in the cell), then the formula should return a value of 1 BUT if the G2 equals any of the above values AND K2 is populated with any value AND H2 is blank (has nothing in the cell), then the formula should return a value of 1 Can anyone help? many thanks, Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND statements
An alternative to David's formula:
=IF(AND(OR(G2={"a","a3","m","m3","ftm"})+0,OR(H2< "",K2<"")),1/((H2<"")+(K2<"")),"unmatched") The other 2 formulas offered so far (S & MH), fail in certain areas). -- ** John C ** "bob" wrote: I'm seeking a formula for the following problem: IF G2 = "a" or "a3" or "m" or "m3" or "ftm" AND H2 and K2 are populated with any value (that is, they are not blank cells), then the formula should return a value of 0.5 BUT if the G2 equals any of the above values AND H2 is populated with any value AND K2 is blank (has nothing in the cell), then the formula should return a value of 1 BUT if the G2 equals any of the above values AND K2 is populated with any value AND H2 is blank (has nothing in the cell), then the formula should return a value of 1 Can anyone help? many thanks, Bob |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND statements
Do you need the +0 ?
-- David Biddulph "John C" <johnc@stateofdenial wrote in message ... An alternative to David's formula: =IF(AND(OR(G2={"a","a3","m","m3","ftm"})+0,OR(H2< "",K2<"")),1/((H2<"")+(K2<"")),"unmatched") The other 2 formulas offered so far (S & MH), fail in certain areas). -- ** John C ** "bob" wrote: I'm seeking a formula for the following problem: IF G2 = "a" or "a3" or "m" or "m3" or "ftm" AND H2 and K2 are populated with any value (that is, they are not blank cells), then the formula should return a value of 0.5 BUT if the G2 equals any of the above values AND H2 is populated with any value AND K2 is blank (has nothing in the cell), then the formula should return a value of 1 BUT if the G2 equals any of the above values AND K2 is populated with any value AND H2 is blank (has nothing in the cell), then the formula should return a value of 1 Can anyone help? many thanks, Bob |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND statements
Nope. Early version, I thought I did, and just never got rid of it. Kind of
interesting, checkmarks go to the other 2 respondents, when the formulas don't match OP's request, lol. -- ** John C ** "David Biddulph" wrote: Do you need the +0 ? -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... An alternative to David's formula: =IF(AND(OR(G2={"a","a3","m","m3","ftm"})+0,OR(H2< "",K2<"")),1/((H2<"")+(K2<"")),"unmatched") The other 2 formulas offered so far (S & MH), fail in certain areas). -- ** John C ** "bob" wrote: I'm seeking a formula for the following problem: IF G2 = "a" or "a3" or "m" or "m3" or "ftm" AND H2 and K2 are populated with any value (that is, they are not blank cells), then the formula should return a value of 0.5 BUT if the G2 equals any of the above values AND H2 is populated with any value AND K2 is blank (has nothing in the cell), then the formula should return a value of 1 BUT if the G2 equals any of the above values AND K2 is populated with any value AND H2 is blank (has nothing in the cell), then the formula should return a value of 1 Can anyone help? many thanks, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if then statements | Excel Discussion (Misc queries) | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
IF and OR statements | Excel Discussion (Misc queries) | |||
More than 7 IF(AND) statements | Excel Discussion (Misc queries) | |||
If Statements... | Excel Discussion (Misc queries) |