Re: Which function to use?

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" <taxmom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A5820281-C79D-4453-A9AB-425214EEE827@xxxxxxxxxxxxxxxx
> 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" <taxmom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:4A35FE48-94D8-4E4D-A72B-78214D9FD0DC@xxxxxxxxxxxxxxxx
>> > 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
>> >
>>
>>
>>


.

Relevant Pages

  • Re: Which function to use?
    ... "Roger Govier" wrote: ... > If the result of this countif calculation yielded an answer which is greater ... > Harald used the MAX and MIN functions to achieve a similar outcome. ... > You mentioned that values for one city could be positive, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Create a Pie Chart?
    ... I think the easiest way to get what you want is by using countif ... Let's say that your city names are in the D column, ... now create a pie chart with these cells as the data series. ...
    (microsoft.public.excel.charting)
  • Re: How do I include an attachment to mail merged email?
    ... The code in the macro does rely on the catalog merge producing a table, ... to be in the cells of a table. ... So for a record that has Houston as the city, ... If there was no entry in the city field, ...
    (microsoft.public.word.mailmerge.fields)
  • Re: How do I include an attachment to mail merged email?
    ... > to be in the cells of a table. ... > So for a record that has Houston as the city, ... > If there was no entry in the city field, ... > Doug Robbins - Word MVP ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Bottlenecks in an Excel VBA application
    ... But I am not an MVP ... Calculation turned off when entering into cells? ... find all swedish male persons in the world by testing everybody. ... Best wishes Harald ...
    (microsoft.public.excel.programming)