Prev: dates on worksheets
Next: using GETPIVOTDATA feature and need to keep formatting
From: Woodi2 on 29 Apr 2010 11:20
I have a little function that i need help with.

=IF(AND($B9="Z",AE9=35,J9=1),1000). This function works fine, however I
need to add in somehow that J9 could equal M or 1 or C and I would like the
function still to be true and return the value as 1000.
As I see it, this is smoehow combining and AND OR statement but I cant get
it to work.
Thanks
From: Pete_UK on 29 Apr 2010 11:37
Try this:

=IF(AND($B9="Z",AE9=35,OR(J9=1,J9="M",J9="C")),1000,"not defined")

This will retern "not defined" if the conditions are not met.

Hope this helps.

Pete

On Apr 29, 4:20 pm, Woodi2 <Woo...(a)discussions.microsoft.com> wrote:
> I have a little function that i need help with.
>
> =IF(AND($B9="Z",AE9=35,J9=1),1000).  This function works fine, however I
> need to add in somehow that J9 could equal M or 1 or C and I would like the
> function still to be true and return the value as 1000.
> As I see it, this is smoehow combining and AND OR statement but I cant get
> it to work.
> Thanks

From: RonaldoOneNil on 29 Apr 2010 11:51
=IF(AND($B9="Z",AE9=35,OR(J9="M",J9=1,J9="C")),1000,"NOT 1000")

"Woodi2" wrote:

> I have a little function that i need help with.
>
> =IF(AND($B9="Z",AE9=35,J9=1),1000). This function works fine, however I
> need to add in somehow that J9 could equal M or 1 or C and I would like the
> function still to be true and return the value as 1000.
> As I see it, this is smoehow combining and AND OR statement but I cant get
> it to work.
> Thanks
From: JudithJubilee on 29 Apr 2010 11:53
Hi Wood12

try this:

=IF(AND(AND($B9="Z",AE9=35),or(J9=1,J9="M",J9="C")),1000).


--
Hope this helps


"Woodi2" wrote:

> I have a little function that i need help with.
>
> =IF(AND($B9="Z",AE9=35,J9=1),1000). This function works fine, however I
> need to add in somehow that J9 could equal M or 1 or C and I would like the
> function still to be true and return the value as 1000.
> As I see it, this is smoehow combining and AND OR statement but I cant get
> it to work.
> Thanks
From: Joe User on 29 Apr 2010 12:04
"Woodi2" wrote:
> =IF(AND($B9="Z",AE9=35,J9=1),1000).
> This function works fine, however I need to add
> in somehow that J9 could equal M or 1 or C and
> I would like the function still to be true and return
> the value as 1000.

Try:

=IF(AND($B9="Z",AE9=35,OR(J9={1,"M","C"})), 1000, "")

Note the last parameter: the function now returns the null string ("") when
the conditions are not met. As you wrote it, the function returned FALSE;
generally not what people want to see.


----- original message -----

"Woodi2" wrote:
> I have a little function that i need help with.
>
> =IF(AND($B9="Z",AE9=35,J9=1),1000). This function works fine, however I
> need to add in somehow that J9 could equal M or 1 or C and I would like the
> function still to be true and return the value as 1000.
> As I see it, this is smoehow combining and AND OR statement but I cant get
> it to work.
> Thanks
 | 
Pages: 1
Prev: dates on worksheets
Next: using GETPIVOTDATA feature and need to keep formatting