IF & MAX Formula
 

I can't seem to get this formula to work, it gives me a #VALUE. My answer
should be 1, what i'm asking is, if it says Creative in Col A, and Col E has
data for those Creative rows, then place a 1 in the cell, if not place a 0

=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
=IF(A23:A90="Advertising",MAX((K23:K90)0.001,1,0) ,0) answer should = 0
=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
Col A Col E
Creative .25
Creative empty
Creative .25
Advertising empty
Advertising empty
DG .10
DG .10

IF & MAX Formula
 

Sorry, I meant to say Column K, NOT E - changed it below

"KC" wrote:

I can't seem to get this formula to work, it gives me a #VALUE. My answer
should be 1, what i'm asking is, if it says Creative in Col A, and Col K has
data for those Creative rows, then place a 1 in the cell, if not place a 0

=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
=IF(A23:A90="Advertising",MAX((K23:K90)0.001,1,0) ,0) answer should = 0
=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
Col A Col K
Creative .25
Creative empty
Creative .25
Advertising empty
Advertising empty
DG .10
DG .10

IF & MAX Formula
 

On Apr 7, 3:15*pm, KC wrote:
I can't seem to get this formula to work, it gives me a #VALUE. *My answer
should be 1, what i'm asking is, if it says Creative in Col A, and Col E has
data for those Creative rows, then place a 1 in the cell, if not place a 0

=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) *answer should = 1
=IF(A23:A90="Advertising",MAX((K23:K90)0.001,1,0) ,0) *answer should = 0
=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) *answer should = 1
Col A * * * * * * * * * Col E
Creative * * * * * * * *.25
Creative * * * * * * * *empty
Creative * * * * * * * *.25
Advertising * * * * * *empty
Advertising * * * * * *empty
DG * * * * * * * * * * * *.10
DG * * * * * * * * * * * *.10


=IF(AND(A23="Creative",E230),1,0)

Unless I'm misunderstanding you, which is totally possible. Which
cells will these 1's and 0's be going into?

~Shadow

IF & MAX Formula
 

What this formula needs to do, is look at the collective list A23 thru A90
and anything that has creative on it, will need to be noted. The way it
needs to be noted though is, if any of the rows that say Creative in them
have a value in column K, then the answer should be 1, however, if theres
nothing in column K, then the answer should be 0. Therefore, the formula
you gave me below wouldn't work because its only looking at cell A23 when it
needs to be looking at A23 thru A90 collectively. I hope I've explained it
so you understand it.

" wrote:

On Apr 7, 3:15 pm, KC wrote:
I can't seem to get this formula to work, it gives me a #VALUE. My answer
should be 1, what i'm asking is, if it says Creative in Col A, and Col E has
data for those Creative rows, then place a 1 in the cell, if not place a 0

=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
=IF(A23:A90="Advertising",MAX((K23:K90)0.001,1,0) ,0) answer should = 0
=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
Col A Col E
Creative .25
Creative empty
Creative .25
Advertising empty
Advertising empty
DG .10
DG .10


=IF(AND(A23="Creative",E230),1,0)

Unless I'm misunderstanding you, which is totally possible. Which
cells will these 1's and 0's be going into?

~Shadow

IF & MAX Formula
 

You don't say what you want if there are more than one match. If you want
only 1 returned no matter how many matches there are use:

=IF((A23:A90="Creative")*(K23:K900.001),1,0)

This is an array formula which must be entered with Ctrl + Shift + Enter not
just enter.

If you want a count of the matches use:

=SUM(IF((A23:A29="Creative")*(K23:K290.001),1,0))

Also array entered, or:

=SUMPRODUCT((A23:A90="Creative")*(K23:K900))

Normally entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"KC" wrote in message
...
What this formula needs to do, is look at the collective list A23 thru A90
and anything that has creative on it, will need to be noted. The way it
needs to be noted though is, if any of the rows that say Creative in them
have a value in column K, then the answer should be 1, however, if theres
nothing in column K, then the answer should be 0. Therefore, the formula
you gave me below wouldn't work because its only looking at cell A23 when
it
needs to be looking at A23 thru A90 collectively. I hope I've explained
it
so you understand it.

" wrote:

On Apr 7, 3:15 pm, KC wrote:
I can't seem to get this formula to work, it gives me a #VALUE. My
answer
should be 1, what i'm asking is, if it says Creative in Col A, and Col
E has
data for those Creative rows, then place a 1 in the cell, if not place
a 0

=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
=IF(A23:A90="Advertising",MAX((K23:K90)0.001,1,0) ,0) answer should =
0
=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
Col A Col E
Creative .25
Creative empty
Creative .25
Advertising empty
Advertising empty
DG .10
DG .10


=IF(AND(A23="Creative",E230),1,0)

Unless I'm misunderstanding you, which is totally possible. Which
cells will these 1's and 0's be going into?

~Shadow

IF & MAX Formula
 

Sandy, thanks for the help. I do want to use the 1st formula you gave me - I
want a 1 returned no matter how many matches there are. However, while this
formula doesn't give me an error, it only gives me a 0 even though there are
amounts in the cells that its pointing to. I made sure to hit CTRL SHIFT
ENTER. Do you have any ideas why it wouldn't show the 1?

"Sandy Mann" wrote:

You don't say what you want if there are more than one match. If you want
only 1 returned no matter how many matches there are use:

=IF((A23:A90="Creative")*(K23:K900.001),1,0)

This is an array formula which must be entered with Ctrl + Shift + Enter not
just enter.

If you want a count of the matches use:

=SUM(IF((A23:A29="Creative")*(K23:K290.001),1,0))

Also array entered, or:

=SUMPRODUCT((A23:A90="Creative")*(K23:K900))

Normally entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"KC" wrote in message
...
What this formula needs to do, is look at the collective list A23 thru A90
and anything that has creative on it, will need to be noted. The way it
needs to be noted though is, if any of the rows that say Creative in them
have a value in column K, then the answer should be 1, however, if theres
nothing in column K, then the answer should be 0. Therefore, the formula
you gave me below wouldn't work because its only looking at cell A23 when
it
needs to be looking at A23 thru A90 collectively. I hope I've explained
it
so you understand it.

" wrote:

On Apr 7, 3:15 pm, KC wrote:
I can't seem to get this formula to work, it gives me a #VALUE. My
answer
should be 1, what i'm asking is, if it says Creative in Col A, and Col
E has
data for those Creative rows, then place a 1 in the cell, if not place
a 0

=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
=IF(A23:A90="Advertising",MAX((K23:K90)0.001,1,0) ,0) answer should =
0
=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should = 1
Col A Col E
Creative .25
Creative empty
Creative .25
Advertising empty
Advertising empty
DG .10
DG .10


=IF(AND(A23="Creative",E230),1,0)

Unless I'm misunderstanding you, which is totally possible. Which
cells will these 1's and 0's be going into?

~Shadow

IF & MAX Formula
 

Sorry that was my mistake try:

=MAX(IF((A23:A90="Creative")*(K23:K900.001),1,0))

Again array entered.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"KC" wrote in message
...
Sandy, thanks for the help. I do want to use the 1st formula you gave
me - I
want a 1 returned no matter how many matches there are. However, while
this
formula doesn't give me an error, it only gives me a 0 even though there
are
amounts in the cells that its pointing to. I made sure to hit CTRL SHIFT
ENTER. Do you have any ideas why it wouldn't show the 1?

"Sandy Mann" wrote:

You don't say what you want if there are more than one match. If you
want
only 1 returned no matter how many matches there are use:

=IF((A23:A90="Creative")*(K23:K900.001),1,0)

This is an array formula which must be entered with Ctrl + Shift + Enter
not
just enter.

If you want a count of the matches use:

=SUM(IF((A23:A29="Creative")*(K23:K290.001),1,0))

Also array entered, or:

=SUMPRODUCT((A23:A90="Creative")*(K23:K900))

Normally entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"KC" wrote in message
...
What this formula needs to do, is look at the collective list A23 thru
A90
and anything that has creative on it, will need to be noted. The way
it
needs to be noted though is, if any of the rows that say Creative in
them
have a value in column K, then the answer should be 1, however, if
theres
nothing in column K, then the answer should be 0. Therefore, the
formula
you gave me below wouldn't work because its only looking at cell A23
when
it
needs to be looking at A23 thru A90 collectively. I hope I've
explained
it
so you understand it.

" wrote:

On Apr 7, 3:15 pm, KC wrote:
I can't seem to get this formula to work, it gives me a #VALUE. My
answer
should be 1, what i'm asking is, if it says Creative in Col A, and
Col
E has
data for those Creative rows, then place a 1 in the cell, if not
place
a 0

=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should =
1
=IF(A23:A90="Advertising",MAX((K23:K90)0.001,1,0) ,0) answer should
=
0
=IF(A23:A90="Creative",MAX((K23:K90)0.001,1,0),0) answer should =
1
Col A Col E
Creative .25
Creative empty
Creative .25
Advertising empty
Advertising empty
DG .10
DG .10


=IF(AND(A23="Creative",E230),1,0)

Unless I'm misunderstanding you, which is totally possible. Which
cells will these 1's and 0's be going into?

~Shadow