Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Countif

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Cheers

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    >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. )







  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi,

    Gotta say, I agree.



  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •