Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with =IF(AND........ formula
I have the following formula on one sheet that reads another sheet to
determine a value: =IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0) My problem is this......if I only have a "YES" (in the cell noted) and the other cells are blank, it is returning a "1" when it should return a "0". What have I done wrong or failed to do???? Please note the formula works fine when all cells contain data. It seems to be only the G21 cell and that d_ _ _ "YES" thats causing me the problem. For reference cells are formatted as follows: ... B15 is a number, G21 is "general" displaying blank or "YES", C7 is a date, C11 is a date, E12 is a date. All date cells are formatted MMM-YY. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with =IF(AND........ formula
Check your cells for " " versus ""
" """ is true " "0 is true if B15 and C11 are " " and c7 and e12 are "" the "and" would evaluate as true "PSU35" wrote: I have the following formula on one sheet that reads another sheet to determine a value: =IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0) My problem is this......if I only have a "YES" (in the cell noted) and the other cells are blank, it is returning a "1" when it should return a "0". What have I done wrong or failed to do???? Please note the formula works fine when all cells contain data. It seems to be only the G21 cell and that d_ _ _ "YES" thats causing me the problem. For reference cells are formatted as follows: ... B15 is a number, G21 is "general" displaying blank or "YES", C7 is a date, C11 is a date, E12 is a date. All date cells are formatted MMM-YY. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with =IF(AND........ formula
BJ or Anyone else
I'm not sure what your telling me when you say check my cells for " " vs "". Let me expand a little and see if this clarifies it better. Cell B15 is populated via a "VLOOKUP" formula used an IF statement formula. Cell C7 is populated via the user from a dropdown selection list. Cell C11 is calculated based on a user entry in a neighboring cell. Cell E12 always contains a date formatted as mentioned before. "bj" wrote: Check your cells for " " versus "" " """ is true " "0 is true if B15 and C11 are " " and c7 and e12 are "" the "and" would evaluate as true "PSU35" wrote: I have the following formula on one sheet that reads another sheet to determine a value: =IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0) My problem is this......if I only have a "YES" (in the cell noted) and the other cells are blank, it is returning a "1" when it should return a "0". What have I done wrong or failed to do???? Please note the formula works fine when all cells contain data. It seems to be only the G21 cell and that d_ _ _ "YES" thats causing me the problem. For reference cells are formatted as follows: ... B15 is a number, G21 is "general" displaying blank or "YES", C7 is a date, C11 is a date, E12 is a date. All date cells are formatted MMM-YY. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with =IF(AND........ formula
for each of the items in the and statement
make a separate euqtion =$B150 ='Proposal Data'!$G$21="YES", ='Proposal Data'!$C$7<=E12 =E12<'Proposal Data'!$C$11 for you to get 1 all of these must be true. a way for all of these to be true is for B15 and C11 to have spaces " ", not a blank "" but for C7 to have an actual blank try =B15 = "" and =b15 = " " and see what you get one way tp fix it is IF(AND(isnumber($B15),isnumber('Proposal Data'!$C$7),isnumber('Proposal Data'!$C$11),$B$150,'Proposal Data'!$G$21="YES",'Proposal Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0) "PSU35" wrote: BJ or Anyone else I'm not sure what your telling me when you say check my cells for " " vs "". Let me expand a little and see if this clarifies it better. Cell B15 is populated via a "VLOOKUP" formula used an IF statement formula. Cell C7 is populated via the user from a dropdown selection list. Cell C11 is calculated based on a user entry in a neighboring cell. Cell E12 always contains a date formatted as mentioned before. "bj" wrote: Check your cells for " " versus "" " """ is true " "0 is true if B15 and C11 are " " and c7 and e12 are "" the "and" would evaluate as true "PSU35" wrote: I have the following formula on one sheet that reads another sheet to determine a value: =IF(AND($B$150,'Proposal Data'!$G$21="YES",'Proposal Data'!$C$7<=E12,E12<'Proposal Data'!$C$11),1,0) My problem is this......if I only have a "YES" (in the cell noted) and the other cells are blank, it is returning a "1" when it should return a "0". What have I done wrong or failed to do???? Please note the formula works fine when all cells contain data. It seems to be only the G21 cell and that d_ _ _ "YES" thats causing me the problem. For reference cells are formatted as follows: ... B15 is a number, G21 is "general" displaying blank or "YES", C7 is a date, C11 is a date, E12 is a date. All date cells are formatted MMM-YY. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with =IF(AND........ formula
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem | Excel Discussion (Misc queries) | |||
Formula Problem | Excel Discussion (Misc queries) | |||
Help! Formula problem | Excel Discussion (Misc queries) | |||
formula problem | Excel Discussion (Misc queries) | |||
Formula problem | Excel Worksheet Functions |