Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky formula
Hi Guys,
been a while since ive been on here! I am trying to work out a tricky formula...I will attempt to paste an example below. Header1 Header2 Header3 Formula Values 1 Date Date 1 Date Date Date 1 Date Date 0 Date 0 Now if header 1 2 and 3 are blank then I want a result of 1 from my formula, if there are dates in all columns then I want a result of 1, and same if only B column is blank (or header2), but then if 3 is blank or 3 + 2 are blank then I want a result of 0. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky formula
=--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUM BER(C2)),OR(ISNUMBER(B2
),ISBLANK(B2))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Duncan" wrote in message oups.com... Hi Guys, been a while since ive been on here! I am trying to work out a tricky formula...I will attempt to paste an example below. Header1 Header2 Header3 Formula Values 1 Date Date 1 Date Date Date 1 Date Date 0 Date 0 Now if header 1 2 and 3 are blank then I want a result of 1 from my formula, if there are dates in all columns then I want a result of 1, and same if only B column is blank (or header2), but then if 3 is blank or 3 + 2 are blank then I want a result of 0. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky formula
Hi Bob,
Thanks for your swift reply, but I pasted it straight in and came up with an error, am i missing something? Many thanks Duncan Bob Phillips wrote: =--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUM BER(C2)),OR(ISNUMBER(B2 ),ISBLANK(B2))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Duncan" wrote in message oups.com... Hi Guys, been a while since ive been on here! I am trying to work out a tricky formula...I will attempt to paste an example below. Header1 Header2 Header3 Formula Values 1 Date Date 1 Date Date Date 1 Date Date 0 Date 0 Now if header 1 2 and 3 are blank then I want a result of 1 from my formula, if there are dates in all columns then I want a result of 1, and same if only B column is blank (or header2), but then if 3 is blank or 3 + 2 are blank then I want a result of 0. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky formula
Like the novice that I am I changed it to
=(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNU MBER(C2)),OR(ISNUMBER(B2),ISBLANK(B2))))) But that brings up true or false and i want it to bring up a 1 or a 0, also doesnt bring up the true or false in the right contexts..... I think I am being stupid..... Duncan wrote: Hi Bob, Thanks for your swift reply, but I pasted it straight in and came up with an error, am i missing something? Many thanks Duncan Bob Phillips wrote: =--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUM BER(C2)),OR(ISNUMBER(B2 ),ISBLANK(B2))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Duncan" wrote in message oups.com... Hi Guys, been a while since ive been on here! I am trying to work out a tricky formula...I will attempt to paste an example below. Header1 Header2 Header3 Formula Values 1 Date Date 1 Date Date Date 1 Date Date 0 Date 0 Now if header 1 2 and 3 are blank then I want a result of 1 from my formula, if there are dates in all columns then I want a result of 1, and same if only B column is blank (or header2), but then if 3 is blank or 3 + 2 are blank then I want a result of 0. Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky formula
Ok, I was being stupid, I pasted it in again correctly and it reads
=--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUM BER(C2)),OR(ISNUMBER(B2),ISBLANK(B2))))) But it works for when all 3 are balnk, but shows 0 for all other combinations.... Duncan wrote: Like the novice that I am I changed it to =(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNU MBER(C2)),OR(ISNUMBER(B2),ISBLANK(B2))))) But that brings up true or false and i want it to bring up a 1 or a 0, also doesnt bring up the true or false in the right contexts..... I think I am being stupid..... Duncan wrote: Hi Bob, Thanks for your swift reply, but I pasted it straight in and came up with an error, am i missing something? Many thanks Duncan Bob Phillips wrote: =--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUM BER(C2)),OR(ISNUMBER(B2 ),ISBLANK(B2))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Duncan" wrote in message oups.com... Hi Guys, been a while since ive been on here! I am trying to work out a tricky formula...I will attempt to paste an example below. Header1 Header2 Header3 Formula Values 1 Date Date 1 Date Date Date 1 Date Date 0 Date 0 Now if header 1 2 and 3 are blank then I want a result of 1 from my formula, if there are dates in all columns then I want a result of 1, and same if only B column is blank (or header2), but then if 3 is blank or 3 + 2 are blank then I want a result of 0. Any ideas? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky formula
Ok, I am really embarressed now, Bob's suggestion worked perfectly, I
have got tuesday morning blues and had an error in my tests and not with the formula, I am extremely sorry Bob, I should have know that it would work coming from you! Many thanks again, Duncan Duncan wrote: Ok, I was being stupid, I pasted it in again correctly and it reads =--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUM BER(C2)),OR(ISNUMBER(B2),ISBLANK(B2))))) But it works for when all 3 are balnk, but shows 0 for all other combinations.... Duncan wrote: Like the novice that I am I changed it to =(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNU MBER(C2)),OR(ISNUMBER(B2),ISBLANK(B2))))) But that brings up true or false and i want it to bring up a 1 or a 0, also doesnt bring up the true or false in the right contexts..... I think I am being stupid..... Duncan wrote: Hi Bob, Thanks for your swift reply, but I pasted it straight in and came up with an error, am i missing something? Many thanks Duncan Bob Phillips wrote: =--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUM BER(C2)),OR(ISNUMBER(B2 ),ISBLANK(B2))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Duncan" wrote in message oups.com... Hi Guys, been a while since ive been on here! I am trying to work out a tricky formula...I will attempt to paste an example below. Header1 Header2 Header3 Formula Values 1 Date Date 1 Date Date Date 1 Date Date 0 Date 0 Now if header 1 2 and 3 are blank then I want a result of 1 from my formula, if there are dates in all columns then I want a result of 1, and same if only B column is blank (or header2), but then if 3 is blank or 3 + 2 are blank then I want a result of 0. Any ideas? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky formula
Luckily I went out Duncan, so I didn't see these until you had resolved it
all <BG -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Duncan" wrote in message ups.com... Ok, I am really embarressed now, Bob's suggestion worked perfectly, I have got tuesday morning blues and had an error in my tests and not with the formula, I am extremely sorry Bob, I should have know that it would work coming from you! Many thanks again, Duncan Duncan wrote: Ok, I was being stupid, I pasted it in again correctly and it reads =--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUM BER(C2)),OR(ISNUMBER(B2 ),ISBLANK(B2))))) But it works for when all 3 are balnk, but shows 0 for all other combinations.... Duncan wrote: Like the novice that I am I changed it to =(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNU MBER(C2)),OR(ISNUMBER(B2), ISBLANK(B2))))) But that brings up true or false and i want it to bring up a 1 or a 0, also doesnt bring up the true or false in the right contexts..... I think I am being stupid..... Duncan wrote: Hi Bob, Thanks for your swift reply, but I pasted it straight in and came up with an error, am i missing something? Many thanks Duncan Bob Phillips wrote: =--(OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUM BER(C2)),OR(ISNUMBER(B2 ),ISBLANK(B2))))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Duncan" wrote in message oups.com... Hi Guys, been a while since ive been on here! I am trying to work out a tricky formula...I will attempt to paste an example below. Header1 Header2 Header3 Formula Values 1 Date Date 1 Date Date Date 1 Date Date 0 Date 0 Now if header 1 2 and 3 are blank then I want a result of 1 from my formula, if there are dates in all columns then I want a result of 1, and same if only B column is blank (or header2), but then if 3 is blank or 3 + 2 are blank then I want a result of 0. Any ideas? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky formula
Hi Bob,
I wonder if you have the time to break this formula down for me and explain how it works? that way I might be able to recreate it at another time with the knowledge instead of just copying the formula. I do have a basic understanding of formulas and maths but working out how your formula works is giving me a headache! Many many thanks Duncan Bob Phillips wrote: Luckily I went out Duncan, so I didn't see these until you had resolved it all <BG -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
tricky formula
Duncan,
It is quite straight-forward, I just put all of your logic into separate groups OR(COUNTIF(A2:C2,"")=3 will test fro all blank AND(ISNUMBER(A2),ISNUMBER(C2)) tests for column A and C being a date (actually just a number, but Excel doesn't have a date type) OR(ISNUMBER(B2),ISBLANK(B2)) tests for B being a date or blank we join the last two with an AND, AND(AND(ISNUMBER(A2),ISNUMBER(C2)),OR(ISNUMBER(B2) ,ISBLANK(B2)))) so we effectively test for A being a date, B being a date OR blank, and C being a date Combining this with OR OR(COUNTIF(A2:C2,"")=3,AND(AND(ISNUMBER(A2),ISNUMB ER(C2)),OR(ISNUMBER(B2),IS BLANK(B2)))) tests for all blank, OR A being a date, B being a date OR blank, and C being a date This would just return TRUE or FALSE, so we need to coerce it to 1 or 0, this is what the leading -- does. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Duncan" wrote in message ups.com... Hi Bob, I wonder if you have the time to break this formula down for me and explain how it works? that way I might be able to recreate it at another time with the knowledge instead of just copying the formula. I do have a basic understanding of formulas and maths but working out how your formula works is giving me a headache! Many many thanks Duncan Bob Phillips wrote: Luckily I went out Duncan, so I didn't see these until you had resolved it all <BG -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tricky Formula | Excel Worksheet Functions | |||
Tricky Formula | Excel Discussion (Misc queries) | |||
Help with a tricky formula...... | Excel Discussion (Misc queries) | |||
Tricky formula | Excel Discussion (Misc queries) | |||
tricky formula.. please help | Excel Programming |