Hi All:

This sounds simple but it's driving me nuts.


A1 contains the number of months for a construction project. (manual
input)

A2 looks @ A1 and calculates the number of months greater than 24 but
less than 36.

A3 looks at A1 and calculates the number of months greater than 36 but
less than 48.

A4 calculates the number of months greater than 48.


I need formulas for A2 & A3.

Can someone please help me.

thanks in advance.

Tom

On Wed, 27 Jul 2011 14:51:19 -0400, lehigh46
wrote:

Hi All:

This sounds simple but it's driving me nuts.


A1 contains the number of months for a construction project. (manual
input)

A2 looks @ A1 and calculates the number of months greater than 24 but
less than 36.

A3 looks at A1 and calculates the number of months greater than 36 but
less than 48.

A4 calculates the number of months greater than 48.


I need formulas for A2 & A3.

Can someone please help me.

thanks in advance.

Tom


I need to claify that if the project is longer than 48 months, then A2
@ A3 would both show 12.

"lehigh46" wrote:
in message ...
A1 contains the number of months for a construction project. (manual
input)
A2 looks @ A1 and calculates the number of months greater than 24 but
less than 36.
A3 looks at A1 and calculates the number of months greater than 36 but
less than 48.
A4 calculates the number of months greater than 48.

A2: =MAX(0,MIN(12,A1-24))
A3: =MAX(0,MIN(12,A1-36))
A4: =MAX(0,A1-48)

On Wed, 27 Jul 2011 13:29:04 -0700, "joeu2004"
wrote:

"lehigh46" wrote:
in message ...
A1 contains the number of months for a construction project. (manual
input)
A2 looks @ A1 and calculates the number of months greater than 24 but
less than 36.
A3 looks at A1 and calculates the number of months greater than 36 but
less than 48.
A4 calculates the number of months greater than 48.

A2: =MAX(0,MIN(12,A1-24))
A3: =MAX(0,MIN(12,A1-36))
A4: =MAX(0,A1-48)



Thanks joeu2004

This doesn't work for me, so I'll give you a few examples of what I
need to see.


Ist example is that A1 = 28
Then A2 would = 4
A3 would = 0
A4 would = 0


2nd example is that A1 = 39
Then A2 would = 12
A3 would = 3
A4 would = 0


3rd example is that A1 = 52
Then A2 would = 12
A3 would = 12
A4 would = 4

To sum it up, A1 = "total monthes" This number is keyed in by hand.
A2 shows the number of months over 24 BUT not more than 12. (between
24 & 36) See 1st example.
A3 shows the number of months over 36 BUT not more than 12. (between
36 & 48) See 2nd example.
A4 shows the number of ALL months over 48.

On Thu, 28 Jul 2011 08:35:48 -0400, lehigh46 wrote:

On Wed, 27 Jul 2011 13:29:04 -0700, "joeu2004"
wrote:

"lehigh46" wrote:
in message ...
A1 contains the number of months for a construction project. (manual
input)
A2 looks @ A1 and calculates the number of months greater than 24 but
less than 36.
A3 looks at A1 and calculates the number of months greater than 36 but
less than 48.
A4 calculates the number of months greater than 48.

A2: =MAX(0,MIN(12,A1-24))
A3: =MAX(0,MIN(12,A1-36))
A4: =MAX(0,A1-48)



Thanks joeu2004

This doesn't work for me, so I'll give you a few examples of what I
need to see.


Ist example is that A1 = 28
Then A2 would = 4
A3 would = 0
A4 would = 0


2nd example is that A1 = 39
Then A2 would = 12
A3 would = 3
A4 would = 0


3rd example is that A1 = 52
Then A2 would = 12
A3 would = 12
A4 would = 4

To sum it up, A1 = "total monthes" This number is keyed in by hand.
A2 shows the number of months over 24 BUT not more than 12. (between
24 & 36) See 1st example.
A3 shows the number of months over 36 BUT not more than 12. (between
36 & 48) See 2nd example.
A4 shows the number of ALL months over 48.



You need to clarify what you mean when you write: "This doesn't work for me". That can mean almost anything: computer crash; program crash; error message; wrong answer expressed in a variety of ways; etc. And the troubleshooting process is different depending on what "doesn't work" really means. Lots of people respond that way, and it is usually a completely useless response.


Using joeu's formulas, and your inputs, I get the same answers as the one's you posted here as being correct. Most likely there is a problem with your data, or with your expression of the problem.

On Thu, 28 Jul 2011 08:47:31 -0400, Ron Rosenfeld
wrote:

On Thu, 28 Jul 2011 08:35:48 -0400, lehigh46 wrote:

On Wed, 27 Jul 2011 13:29:04 -0700, "joeu2004"
wrote:

"lehigh46" wrote:
in message ...
A1 contains the number of months for a construction project. (manual
input)
A2 looks @ A1 and calculates the number of months greater than 24 but
less than 36.
A3 looks at A1 and calculates the number of months greater than 36 but
less than 48.
A4 calculates the number of months greater than 48.

A2: =MAX(0,MIN(12,A1-24))
A3: =MAX(0,MIN(12,A1-36))
A4: =MAX(0,A1-48)



Thanks joeu2004

This doesn't work for me, so I'll give you a few examples of what I
need to see.


Ist example is that A1 = 28
Then A2 would = 4
A3 would = 0
A4 would = 0


2nd example is that A1 = 39
Then A2 would = 12
A3 would = 3
A4 would = 0


3rd example is that A1 = 52
Then A2 would = 12
A3 would = 12
A4 would = 4

To sum it up, A1 = "total monthes" This number is keyed in by hand.
A2 shows the number of months over 24 BUT not more than 12. (between
24 & 36) See 1st example.
A3 shows the number of months over 36 BUT not more than 12. (between
36 & 48) See 2nd example.
A4 shows the number of ALL months over 48.



You need to clarify what you mean when you write: "This doesn't work for me". That can mean almost anything: computer crash; program crash; error message; wrong answer expressed in a variety of ways; etc. And the troubleshooting process is different depending on what "doesn't work" really means. Lots of people respond that way, and it is usually a completely useless response.


Using joeu's formulas, and your inputs, I get the same answers as the one's you posted here as being correct. Most likely there is a problem with your data, or with your expression of the problem.

I apologize. You're formulas worked perfectly.
I was so excited to see a response that I inadvertently used the same
cell location as was in my example. (I used A1 for simplicity)
When I typed your formula in my sheet I used A1. In reality it should
have been C10.
Again, I apologize.

A thousand thanks!

On Thu, 28 Jul 2011 10:48:29 -0400, lehigh46 wrote:

I apologize. You're formulas worked perfectly.
I was so excited to see a response that I inadvertently used the same
cell location as was in my example. (I used A1 for simplicity)
When I typed your formula in my sheet I used A1. In reality it should
have been C10.
Again, I apologize.

A thousand thanks!

Thank you for clarifying. Be sure to mark joeu's response as an Answer.