Dead line or I'm dead.

BIGHAIR · Feb 23rd, 2002, 11:27 AM

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 · Feb 23rd, 2002, 11:42 AM

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 · Feb 23rd, 2002, 11:52 AM

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 · Feb 23rd, 2002, 12:24 PM

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

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

BIGHAIR · Feb 23rd, 2002, 01:09 PM

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 · Feb 23rd, 2002, 01:26 PM

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 · Feb 23rd, 2002, 03:56 PM

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 · Feb 23rd, 2002, 07:40 PM

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 · Feb 23rd, 2002, 11:00 PM

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 ]

Feb 23rd, 2002, 12:24 PM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	I'm not sure I understand the second part, but for the first part, can you use: =SUMIF(A1:A6,">0") __________________ ~Anne Troy

Feb 23rd, 2002, 01:26 PM	#6
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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

Feb 23rd, 2002, 03:56 PM	#7
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 23rd, 2002, 11:27 AM	#1
BIGHAIR New Member Join Date: Feb 2002 Posts: 3	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 ]

Feb 23rd, 2002, 11:42 AM	#2
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	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

Feb 23rd, 2002, 11:52 AM	#3
BIGHAIR New Member Join Date: Feb 2002 Posts: 3	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.

Feb 23rd, 2002, 01:09 PM	#5
BIGHAIR New Member Join Date: Feb 2002 Posts: 3	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?

Feb 23rd, 2002, 07:40 PM	#8
Albert 1 Board Regular Join Date: Feb 2002 Posts: 390	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 ]

Feb 23rd, 2002, 11:00 PM	#9
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	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 ]