Which function to use?
 

Hi Everyone,

I'm sure this will be simple for you. However, I'm stuck.

I have a column d9:d2909 that sometimes has figures and sometimes does not.
All I need is a formula to look in this column and if there is no figures
anywere in the column to return "OK" if there is a number anywhere in the
column to return "Change". I cannot reference a total because if there is a
number in this column it will be a positive in one city and a negative in
another city, so the total would be zero.

I tried ifnumber but I could only get it to work for one cell not a range of
cells.

Can you help me?

thanks

One way
=IF(COUNTIF(D9:D2909,"<")0,"Change","OK")

--
Regards

Roger Govier


"taxmom" wrote in message
...
Hi Everyone,

I'm sure this will be simple for you. However, I'm stuck.

I have a column d9:d2909 that sometimes has figures and sometimes does
not.
All I need is a formula to look in this column and if there is no figures
anywere in the column to return "OK" if there is a number anywhere in the
column to return "Change". I cannot reference a total because if there is
a
number in this column it will be a positive in one city and a negative in
another city, so the total would be zero.

I tried ifnumber but I could only get it to work for one cell not a range
of
cells.

Can you help me?

thanks

Hi

Can you test for largest and smallest value and see if both are zero ?
=IF(AND(MAX(D9:D2909)=0,MIN(D9:D2909)=0),"ok","cha nge")

HTH. Best wishes Harald

"taxmom" skrev i melding
...
Hi Everyone,

I'm sure this will be simple for you. However, I'm stuck.

I have a column d9:d2909 that sometimes has figures and sometimes does
not.
All I need is a formula to look in this column and if there is no figures
anywere in the column to return "OK" if there is a number anywhere in the
column to return "Change". I cannot reference a total because if there is
a
number in this column it will be a positive in one city and a negative in
another city, so the total would be zero.

I tried ifnumber but I could only get it to work for one cell not a range
of
cells.

Can you help me?

thanks

Thank you, they both worked great!

You are all so busy but could you explain why countif, max or min and what
does the < do?

I'm trying to understand the logic of these functions. Some I can figure
out others I'm lost.

Thanks again for all of your help.


"Roger Govier" wrote:

One way
=IF(COUNTIF(D9:D2909,"<")0,"Change","OK")

--
Regards

Roger Govier


"taxmom" wrote in message
...
Hi Everyone,

I'm sure this will be simple for you. However, I'm stuck.

I have a column d9:d2909 that sometimes has figures and sometimes does
not.
All I need is a formula to look in this column and if there is no figures
anywere in the column to return "OK" if there is a number anywhere in the
column to return "Change". I cannot reference a total because if there is
a
number in this column it will be a positive in one city and a negative in
another city, so the total would be zero.

I tried ifnumber but I could only get it to work for one cell not a range
of
cells.

Can you help me?

thanks

Hi
Harald and I took two slightly different approaches to the problem, both of
which seem to have achieved the desired result

As you had said that you were looking for "no figures in the column",
I therefore told COUNTIF to look at the column and count the cells which
"<".
< is the symbol for Not Equal to, and with no further reference, the
implication is Not Equal to null therfore empty.
If the result of this countif calculation yielded an answer which is greater
than 0 (0), then obviously the whole column has one or more cells in it
which are not empty and therfore require a change.

Harald used the MAX and MIN functions to achieve a similar outcome.
MAX(range) will return the highest value found in that range and MIN
obviously returns the lowest value found in that range.
With the use of AND(), Harald is testing if the maximum and Minimum values
are both 0, then there is no data in the column hence return the value "ok",
otherwise if the result is false, then there is a need to change.

You mentioned that values for one city could be positive, and another city
could be negative and that is why you could not use the total for the column
as a test. You did not say whether the value for a city could be 0. If that
is the case, then Harald's formula would return you the wrong result.

I hope that this has given you some insight.


--
Regards

Roger Govier


"taxmom" wrote in message
...
Thank you, they both worked great!

You are all so busy but could you explain why countif, max or min and what
does the < do?

I'm trying to understand the logic of these functions. Some I can figure
out others I'm lost.

Thanks again for all of your help.


"Roger Govier" wrote:

One way
=IF(COUNTIF(D9:D2909,"<")0,"Change","OK")

--
Regards

Roger Govier


"taxmom" wrote in message
...
Hi Everyone,

I'm sure this will be simple for you. However, I'm stuck.

I have a column d9:d2909 that sometimes has figures and sometimes does
not.
All I need is a formula to look in this column and if there is no
figures
anywere in the column to return "OK" if there is a number anywhere in
the
column to return "Change". I cannot reference a total because if there
is
a
number in this column it will be a positive in one city and a negative
in
another city, so the total would be zero.

I tried ifnumber but I could only get it to work for one cell not a
range
of
cells.

Can you help me?

thanks

Thank you, your explanation was very helpful. You have helped shed some
light on the mystery.

Thanks for your help you are an angel ! : )


"Roger Govier" wrote:

Hi
Harald and I took two slightly different approaches to the problem, both of
which seem to have achieved the desired result

As you had said that you were looking for "no figures in the column",
I therefore told COUNTIF to look at the column and count the cells which
"<".
< is the symbol for Not Equal to, and with no further reference, the
implication is Not Equal to null therfore empty.
If the result of this countif calculation yielded an answer which is greater
than 0 (0), then obviously the whole column has one or more cells in it
which are not empty and therfore require a change.

Harald used the MAX and MIN functions to achieve a similar outcome.
MAX(range) will return the highest value found in that range and MIN
obviously returns the lowest value found in that range.
With the use of AND(), Harald is testing if the maximum and Minimum values
are both 0, then there is no data in the column hence return the value "ok",
otherwise if the result is false, then there is a need to change.

You mentioned that values for one city could be positive, and another city
could be negative and that is why you could not use the total for the column
as a test. You did not say whether the value for a city could be 0. If that
is the case, then Harald's formula would return you the wrong result.

I hope that this has given you some insight.


--
Regards

Roger Govier


"taxmom" wrote in message
...
Thank you, they both worked great!

You are all so busy but could you explain why countif, max or min and what
does the < do?

I'm trying to understand the logic of these functions. Some I can figure
out others I'm lost.

Thanks again for all of your help.


"Roger Govier" wrote:

One way
=IF(COUNTIF(D9:D2909,"<")0,"Change","OK")

--
Regards

Roger Govier


"taxmom" wrote in message
...
Hi Everyone,

I'm sure this will be simple for you. However, I'm stuck.

I have a column d9:d2909 that sometimes has figures and sometimes does
not.
All I need is a formula to look in this column and if there is no
figures
anywere in the column to return "OK" if there is a number anywhere in
the
column to return "Change". I cannot reference a total because if there
is
a
number in this column it will be a positive in one city and a negative
in
another city, so the total would be zero.

I tried ifnumber but I could only get it to work for one cell not a
range
of
cells.

Can you help me?

thanks

Thanks for your help you are an angel ! : )
Not many would agree with that!!!
Thanks for the response and glad to have been of some help.

--
Regards

Roger Govier


"taxmom" wrote in message
...
Thank you, your explanation was very helpful. You have helped shed some
light on the mystery.

Thanks for your help you are an angel ! : )


"Roger Govier" wrote:

Hi
Harald and I took two slightly different approaches to the problem, both
of
which seem to have achieved the desired result

As you had said that you were looking for "no figures in the column",
I therefore told COUNTIF to look at the column and count the cells which
"<".
< is the symbol for Not Equal to, and with no further reference, the
implication is Not Equal to null therfore empty.
If the result of this countif calculation yielded an answer which is
greater
than 0 (0), then obviously the whole column has one or more cells in it
which are not empty and therfore require a change.

Harald used the MAX and MIN functions to achieve a similar outcome.
MAX(range) will return the highest value found in that range and MIN
obviously returns the lowest value found in that range.
With the use of AND(), Harald is testing if the maximum and Minimum
values
are both 0, then there is no data in the column hence return the value
"ok",
otherwise if the result is false, then there is a need to change.

You mentioned that values for one city could be positive, and another
city
could be negative and that is why you could not use the total for the
column
as a test. You did not say whether the value for a city could be 0. If
that
is the case, then Harald's formula would return you the wrong result.

I hope that this has given you some insight.


--
Regards

Roger Govier


"taxmom" wrote in message
...
Thank you, they both worked great!

You are all so busy but could you explain why countif, max or min and
what
does the < do?

I'm trying to understand the logic of these functions. Some I can
figure
out others I'm lost.

Thanks again for all of your help.


"Roger Govier" wrote:

One way
=IF(COUNTIF(D9:D2909,"<")0,"Change","OK")

--
Regards

Roger Govier


"taxmom" wrote in message
...
Hi Everyone,

I'm sure this will be simple for you. However, I'm stuck.

I have a column d9:d2909 that sometimes has figures and sometimes
does
not.
All I need is a formula to look in this column and if there is no
figures
anywere in the column to return "OK" if there is a number anywhere
in
the
column to return "Change". I cannot reference a total because if
there
is
a
number in this column it will be a positive in one city and a
negative
in
another city, so the total would be zero.

I tried ifnumber but I could only get it to work for one cell not a
range
of
cells.

Can you help me?

thanks