Dead line or I'm dead.
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Dead line or I'm dead.

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

    Default

     
    Can some one show me how to show the cell position on the worksheet that meets a specific critieria. I tried doing "What ifs" and the formula came to a hault after 9 or 10 "What ifs".
    Example: I need to show cells that are >0.
    A1=1,A2=0,A3=1,A4=0,A5=0,A6=1/2
    Answer:
    A1,A3,A6
    I would use the values in each answer, A1,A3,A6 in my calculation.
    My objective is to calculate the amount of time that's alotted in a 8 hour period for a person to walk from point to point and back a pre- calculated number of frequencies.
    In the case of the example, a person would walk the distance from A1 to A3 100% of the time, and walk from A3 to A6 50% of the time. If the pre-calculated time alotted to walk between each cell is 3.8 minutes / 8 hours, then the end result to the example problem would total to 13.3 minutes / 8 hours.
    I hope this is clear.

    Thank you.


    [ This Message was edited by: BIGHAIR on 2002-02-23 10:27 ]

    [ This Message was edited by: BIGHAIR on 2002-02-23 10:44 ]

    [ This Message was edited by: BIGHAIR on 2002-02-23 10:47 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Your hitting a limit of 7 ifs in one formula more and the formula will fail!

    You and nest ifs with AND also OR if that helps, stick arround some wonderful gurus of the formula are on this board.

    You need a to adjust the way your formuling but sadly a bit beyond me.

    HTH
    Rgds
    ==========
    Jack

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

    Default

    Thanks. I didn't realize I would get a response so quickly. I was still editing my message. I will try the ANDs or ORs. That's a lot of formulation. I have to cover 80 cells or distances.

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not sure I understand the second part, but for the first part, can you use:

    =SUMIF(A1:A6,">0")
    ~Anne Troy

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

    Default

    The SUMIF will only tell me the sum of the cells >0. I need a formula to tell me which cells are >0. i.e. Cell "A1" = a value greater than 0, so the answer in the cell with the formula will read "A1". Can this be done? If it can, I can use the answer "A1" in an other formula. I think?

  6. #6
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've just reviewed the site and am unable to find the terms I agreed to so please don't *fire* me, Bill, if I'm not allowed to offer this:

    BigHair: Feel free to email your file to dreamboat@thewordexpert.com if you want me to have a look-see.
    ~Anne Troy

  7. #7
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've looked over your file pretty extensively, Bighair. I apologize, but it would take me forever to figure it all out. Now that you've already written the email, however, perhaps someone better than me could help you with it faster. And there are many better than me, I'm sure!

    Sorry if I wasted your time!
    ~Anne Troy

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To show the cells > 0:
    In B1, type: =IF(A1>0,ADDRESS(ROW(A1),1),"")
    Copy this formula down, so in B2 comes: =IF(A2>0,ADDRESS(ROW(A2),1),"")
    etc.


    [ This Message was edited by: Albert 1 on 2002-02-23 18:51 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    OK I admit I don't fully understand your problem, but the first thing I try to do is analysis of the data. Either added a sort column with line numbers or make a copy to fiddle with. Then sort the whole thing to group the rows. This may give you some insight if the amount of data is not too great.

    Excel has a feature call array formulas that is useful on large data sets. Kind of a super sum if with multiple and, or abilities. Search for it or give me a better example.
    http://www.cpearson.com/excel/array.htm has a good expiation of array formula.

    HTH

    Rocky...

    PS Another post reminded me they are also called CSE formula for Control-Shift-Enter. Because to enter one you must end with CSE. So you can just search for CSE.

    [ This Message was edited by: Rocky E on 2002-02-23 22:05 ]

    [ This Message was edited by: Rocky E on 2002-02-23 22:05 ]

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
  •  

 

 
DMCA.com