Dead line or I'm dead.

[BIGHAIR]

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

 

[Jack in the UK]

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

 

[BIGHAIR]

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.

 

[Anne Troy]

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

=SUMIF(A1:A6,">0")

 

[BIGHAIR]

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?

 

[Anne Troy]

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]

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!

 

[Albert 1]

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

 

[Rocky E]

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