Remember Me? July 27th 11, 07:51 PM posted to microsoft.public.excel.worksheet.functions
 lehigh46 external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 43 MATH COMPUTAION

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.

Tom July 27th 11, 08:06 PM posted to microsoft.public.excel.worksheet.functions
 lehigh46 external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 43 MATH COMPUTAION

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.

Tom

I need to claify that if the project is longer than 48 months, then A2
@ A3 would both show 12. July 27th 11, 09:29 PM posted to microsoft.public.excel.worksheet.functions
 joeu2004[_2_] external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 829 MATH COMPUTAION

"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) July 28th 11, 01:35 PM posted to microsoft.public.excel.worksheet.functions
 lehigh46 external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 43 MATH COMPUTAION

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. July 28th 11, 01:47 PM posted to microsoft.public.excel.worksheet.functions
 Ron Rosenfeld[_2_] external usenet poster First recorded activity by ExcelBanter: Jun 2010 Posts: 1,045 MATH COMPUTAION

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. July 28th 11, 03:48 PM posted to microsoft.public.excel.worksheet.functions
 lehigh46 external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 43 MATH COMPUTAION

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! July 28th 11, 05:40 PM posted to microsoft.public.excel.worksheet.functions
 Ron Rosenfeld[_2_] external usenet poster First recorded activity by ExcelBanter: Jun 2010 Posts: 1,045 MATH COMPUTAION

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.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post DeAnna Ramirez Excel Worksheet Functions 2 June 7th 07 11:42 PM LatinAviation Excel Discussion (Misc queries) 4 August 18th 06 09:10 PM Curtis Excel Discussion (Misc queries) 0 July 24th 06 11:34 PM Bill Excel Discussion (Misc queries) 3 August 11th 05 11:21 PM Dawn Settles via OfficeKB.com Excel Worksheet Functions 4 May 23rd 05 02:36 PM

All times are GMT +1. The time now is 10:46 AM. Copyright ©2004-2021 ExcelBanter.