Countif

[Riki]

I have a list of rainfall data, ie

1.4
0.6
*0.2

How do I sum these three lines to get 2.2?
An * would normally only preceed the odd small number 0.8 and below.

Tks

[Aladin Akyurek]

On 2002-03-23 23:07, Riki wrote:
I have a list of rainfall data, ie

1.4
0.6
*0.2

How do I sum these three lines to get 2.2?
An * would normally only preceed the odd small number 0.8 and below.

Tks

Riki,

Array-enter:

=SUM(IF(LEFT(A1:A3)="*",MID(A1:A3,2,LEN(A1:A3))+0,(A1:A3)+0))

where A1:A3 houses the values of interest.

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Aladin

[Riki]

Tks Aladin for your prompt reply.
You're always a wealth of information!

Cheers

[Dave Hawley]

Hi Rick


It is not godd practice to mix text and numbers when you really need a number. Array formulas are also a step in the wrong direction.

Just Custom Format your cell(s) that you want to show a * with the number as:
"*"0.0

This way you will see *0.2 but its true value will be 0.2 and you can use a normal SUM function.



_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-24 00:35 ]

[Aladin Akyurek]

Eagerness to "wage war" makes one sloppy.

It would have been a good advice to posit:

Custom format the column in which you enter the rainfall readings as

[<=0.8]"*"0.0;General

and you will be able to use the ordinary SUM worksheet function.

But, not as custom format cell by cell, abusing user time:

"*"0.0

or the whole column unless one wants the whole column to show stars preceeding all numbers.

A side note. Why didn't I suggest this in my first reply? The rule I follow if it matters to know: Accept the user's routines (in tis case) of data entry if that does not conflict with the proccessing requirements. The cost of a single cell array-formula to sum starred and unstarred entries is definitely a "quantitée négligeable" set off against breaking a user routine. This consideration shows up clearly when users insist of entering (start and end) times as 2245, 0300, etc. instead of 22:45, 03:00, etc.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-24 01:40 ]

[Dave Hawley]

>Eagerness to "wage war" makes one sloppy

I'm very sorry you feel that way Aladin, but That's really your problem not mine.


No big deal applying a custom format when you have the "Format painter"


While one or only a few arrays will have no real adverse effect it really is very bad advise. You will certainly not realise it Aladin but I have been sent many an unusable spreadsheet which are due to poor advise about using arrays. When I ask why they allowed this to happen, they all say the same "I didn't realise!".

Aladin, my idea of helping is not a 'glossy cover' approach, which is all arrays are in most cases.

RE: Why didn't I suggest this in my first reply?
You, I and most others reading this thread can patently see this reason. )

[Yogi Anand]

Hi Aladin and Dave:
I feel fortunate that I am able to participate in this forum -- different approaches, personal preferences, pros & cons of different approaches, pitfalls, and new discoveries and all ...
So please keep enligtening us with different viewpoints withoust being defensive about it because if you will hold back your viewpoint you would deny us viewers the benefit of your invaluable insights and experiences...
With GREETINGS and REGARDS!

[Dave Hawley]

Yogi,

Gotta say, I agree.

[Yogi Anand]

On 2002-03-23 23:07, Riki wrote:
I have a list of rainfall data, ie

1.4
0.6
*0.2

How do I sum these three lines to get 2.2?
An * would normally only preceed the odd small number 0.8 and below.

Tks

Hi Rikki:
Aladin and Dave have already posted valuable approaches to your inquiry...
I have a question for you -- do I understand it right that rainfall under 0.8" will be preceded with an * (I realize you do have a vague qualifier in your writing) -- the reason I mention this is that Aladin gave you a formula that will flag all rainfall entries below 0.8" with an *, whereas Dave gave you an option to format entries selectively to be marked with *.

If you go with Aladin's suggested approach, your entries will be displayed as

1.4
*0.6
*0.2
Total rainfall 2.2

Why was 0.6 entry in your post not get preceded with an * ... JUST CURIOUS!