Need formula help setting a range for calculations

I made the following formula:
=IF(AND(F16>12,F16<25),(really large formula)/12*(F16-12),0)

F16 is the # of months. What I need to do is be able to calculate only for
the range of months listed, and if the # of months is higher, I need to keep
the calculation to a 12 month period only, not calculate higher with this set
of formulas. I will then have another line with the same basic formula
calculating for the next year range, but only for 25-36 months, and be 0 if
F16 is < 25 months.

Ideas? I can't figure out how to calculate only the months for the ranges-
it keeps calculating more if F16 is greater than the range.

HELP!!

Hi Sharona

I substituted the figure of 480 for your (really large formula).
With 13 in F16 it returns 40, with 14 it returns 80, with 15 it returns
120 and so on, which is exactly what I would expect if the really large
formula were returning a constant figure, as the divisor will be an ever
increasing value between 1 and 12.
Is this the type of result you are looking for? If not, then re-examine
this part.

Now, you need to turn to the really large formula bit, what is the total
value being returned from here. Is this picking up the data from the
same range of months that you are dividing by?

--
Regards

Roger Govier


"Sharona77" <[email protected]> wrote in message
news:[email protected]...
>I made the following formula:
> =IF(AND(F16>12,F16<25),(really large formula)/12*(F16-12),0)
>
> F16 is the # of months. What I need to do is be able to calculate only
> for
> the range of months listed, and if the # of months is higher, I need
> to keep
> the calculation to a 12 month period only, not calculate higher with
> this set
> of formulas. I will then have another line with the same basic
> formula
> calculating for the next year range, but only for 25-36 months, and be
> 0 if
> F16 is < 25 months.
>
> Ideas? I can't figure out how to calculate only the months for the
> ranges-
> it keeps calculating more if F16 is greater than the range.
>
> HELP!!

Thanks for your reply Roger-

the RLF is a calculation from another part of a different worksheet
involving salary, 3% increases, the appropriate fringe rates. It will be
constant for the year grouping (calculations under year 2 cell (example),
then 3%increase and diff. fringe rate for year4 cell, etc.) My problems
are: 1). I need the calculation to stop after it completes the designated
year- the way I have it now, it calculates the diff over the whole range
given (ie if F16 is 25 it calculates it for 13 months, not just 12). I want
each cell to calculate up to the # of months in that year and then stop at
that value, or give a 0 value only if the months are less than that year (ie
if 25 months, the year 4 and 5 cells will give 0 values). I have a cell for
2 year (example), 3yr, 4yr, and 5 yr. 2).I need the value to stay in the cell
if it is applicable- right now it only is in the cell if it is in the middle
of the range; if F16 were 25, it would come back with 0, instead of the value
for months 12-24.

I know what I need to do, just not how to do it. Any help/ideas would be
great!!!

Thanks!!

S

"Roger Govier" wrote:

> Hi Sharona
>
> I substituted the figure of 480 for your (really large formula).
> With 13 in F16 it returns 40, with 14 it returns 80, with 15 it returns
> 120 and so on, which is exactly what I would expect if the really large
> formula were returning a constant figure, as the divisor will be an ever
> increasing value between 1 and 12.
> Is this the type of result you are looking for? If not, then re-examine
> this part.
>
> Now, you need to turn to the really large formula bit, what is the total
> value being returned from here. Is this picking up the data from the
> same range of months that you are dividing by?
>
> --
> Regards
>
> Roger Govier
>
>
> "Sharona77" <[email protected]> wrote in message
> news:[email protected]...
> >I made the following formula:
> > =IF(AND(F16>12,F16<25),(really large formula)/12*(F16-12),0)
> >
> > F16 is the # of months. What I need to do is be able to calculate only
> > for
> > the range of months listed, and if the # of months is higher, I need
> > to keep
> > the calculation to a 12 month period only, not calculate higher with
> > this set
> > of formulas. I will then have another line with the same basic
> > formula
> > calculating for the next year range, but only for 25-36 months, and be
> > 0 if
> > F16 is < 25 months.
> >
> > Ideas? I can't figure out how to calculate only the months for the
> > ranges-
> > it keeps calculating more if F16 is greater than the range.
> >
> > HELP!!
>
>
>

Hi Sharona

Still not sure I understand things fully from your explanation, but of
course it is quite obvious to you<bg>

One thing to try maybe is to use MIN() within your formula somewhere.
=RLF/MIN(12,number_of_months)

If that doesn't help, you may email me direct with a copy of your file
and an explanation, and I will see if I can sort it out for you.
Remove NOSAPM from my address to send direct.

--
Regards

Roger Govier


"Sharona77" <[email protected]> wrote in message
news:[email protected]...
> Thanks for your reply Roger-
>
> the RLF is a calculation from another part of a different worksheet
> involving salary, 3% increases, the appropriate fringe rates. It will
> be
> constant for the year grouping (calculations under year 2 cell
> (example),
> then 3%increase and diff. fringe rate for year4 cell, etc.) My
> problems
> are: 1). I need the calculation to stop after it completes the
> designated
> year- the way I have it now, it calculates the diff over the whole
> range
> given (ie if F16 is 25 it calculates it for 13 months, not just 12). I
> want
> each cell to calculate up to the # of months in that year and then
> stop at
> that value, or give a 0 value only if the months are less than that
> year (ie
> if 25 months, the year 4 and 5 cells will give 0 values). I have a
> cell for
> 2 year (example), 3yr, 4yr, and 5 yr. 2).I need the value to stay in
> the cell
> if it is applicable- right now it only is in the cell if it is in the
> middle
> of the range; if F16 were 25, it would come back with 0, instead of
> the value
> for months 12-24.
>
> I know what I need to do, just not how to do it. Any help/ideas would
> be
> great!!!
>
> Thanks!!
>
> S
>
> "Roger Govier" wrote:
>
>> Hi Sharona
>>
>> I substituted the figure of 480 for your (really large formula).
>> With 13 in F16 it returns 40, with 14 it returns 80, with 15 it
>> returns
>> 120 and so on, which is exactly what I would expect if the really
>> large
>> formula were returning a constant figure, as the divisor will be an
>> ever
>> increasing value between 1 and 12.
>> Is this the type of result you are looking for? If not, then
>> re-examine
>> this part.
>>
>> Now, you need to turn to the really large formula bit, what is the
>> total
>> value being returned from here. Is this picking up the data from the
>> same range of months that you are dividing by?
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Sharona77" <[email protected]> wrote in message
>> news:[email protected]...
>> >I made the following formula:
>> > =IF(AND(F16>12,F16<25),(really large formula)/12*(F16-12),0)
>> >
>> > F16 is the # of months. What I need to do is be able to calculate
>> > only
>> > for
>> > the range of months listed, and if the # of months is higher, I
>> > need
>> > to keep
>> > the calculation to a 12 month period only, not calculate higher
>> > with
>> > this set
>> > of formulas. I will then have another line with the same basic
>> > formula
>> > calculating for the next year range, but only for 25-36 months, and
>> > be
>> > 0 if
>> > F16 is < 25 months.
>> >
>> > Ideas? I can't figure out how to calculate only the months for the
>> > ranges-
>> > it keeps calculating more if F16 is greater than the range.
>> >
>> > HELP!!
>>
>>
>>

Thanks for your suggestion,Roger.

I will send you what i am working on so it makes sense on your end, and see
what you can come up with.

Again, thanks for your help and suggestions!

S

"Roger Govier" wrote:

> Hi Sharona
>
> Still not sure I understand things fully from your explanation, but of
> course it is quite obvious to you<bg>
>
> One thing to try maybe is to use MIN() within your formula somewhere.
> =RLF/MIN(12,number_of_months)
>
> If that doesn't help, you may email me direct with a copy of your file
> and an explanation, and I will see if I can sort it out for you.
> Remove NOSAPM from my address to send direct.
>
> --
> Regards
>
> Roger Govier
>
>
> "Sharona77" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for your reply Roger-
> >
> > the RLF is a calculation from another part of a different worksheet
> > involving salary, 3% increases, the appropriate fringe rates. It will
> > be
> > constant for the year grouping (calculations under year 2 cell
> > (example),
> > then 3%increase and diff. fringe rate for year4 cell, etc.) My
> > problems
> > are: 1). I need the calculation to stop after it completes the
> > designated
> > year- the way I have it now, it calculates the diff over the whole
> > range
> > given (ie if F16 is 25 it calculates it for 13 months, not just 12). I
> > want
> > each cell to calculate up to the # of months in that year and then
> > stop at
> > that value, or give a 0 value only if the months are less than that
> > year (ie
> > if 25 months, the year 4 and 5 cells will give 0 values). I have a
> > cell for
> > 2 year (example), 3yr, 4yr, and 5 yr. 2).I need the value to stay in
> > the cell
> > if it is applicable- right now it only is in the cell if it is in the
> > middle
> > of the range; if F16 were 25, it would come back with 0, instead of
> > the value
> > for months 12-24.
> >
> > I know what I need to do, just not how to do it. Any help/ideas would
> > be
> > great!!!
> >
> > Thanks!!
> >
> > S
> >
> > "Roger Govier" wrote:
> >
> >> Hi Sharona
> >>
> >> I substituted the figure of 480 for your (really large formula).
> >> With 13 in F16 it returns 40, with 14 it returns 80, with 15 it
> >> returns
> >> 120 and so on, which is exactly what I would expect if the really
> >> large
> >> formula were returning a constant figure, as the divisor will be an
> >> ever
> >> increasing value between 1 and 12.
> >> Is this the type of result you are looking for? If not, then
> >> re-examine
> >> this part.
> >>
> >> Now, you need to turn to the really large formula bit, what is the
> >> total
> >> value being returned from here. Is this picking up the data from the
> >> same range of months that you are dividing by?
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Sharona77" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I made the following formula:
> >> > =IF(AND(F16>12,F16<25),(really large formula)/12*(F16-12),0)
> >> >
> >> > F16 is the # of months. What I need to do is be able to calculate
> >> > only
> >> > for
> >> > the range of months listed, and if the # of months is higher, I
> >> > need
> >> > to keep
> >> > the calculation to a 12 month period only, not calculate higher
> >> > with
> >> > this set
> >> > of formulas. I will then have another line with the same basic
> >> > formula
> >> > calculating for the next year range, but only for 25-36 months, and
> >> > be
> >> > 0 if
> >> > F16 is < 25 months.
> >> >
> >> > Ideas? I can't figure out how to calculate only the months for the
> >> > ranges-
> >> > it keeps calculating more if F16 is greater than the range.
> >> >
> >> > HELP!!
> >>
> >>
> >>
>
>
>

Thanks Roger- You were very helpful in solving my problem!!!!!

S

"Sharona77" wrote:

> I made the following formula:
> =IF(AND(F16>12,F16<25),(really large formula)/12*(F16-12),0)
>
> F16 is the # of months. What I need to do is be able to calculate only for
> the range of months listed, and if the # of months is higher, I need to keep
> the calculation to a 12 month period only, not calculate higher with this set
> of formulas. I will then have another line with the same basic formula
> calculating for the next year range, but only for 25-36 months, and be 0 if
> F16 is < 25 months.
>
> Ideas? I can't figure out how to calculate only the months for the ranges-
> it keeps calculating more if F16 is greater than the range.
>
> HELP!!

Thanks Roger- You were very helpful in solving my problem!!!!!

S

"Sharona77" wrote:

> I made the following formula:
> =IF(AND(F16>12,F16<25),(really large formula)/12*(F16-12),0)
>
> F16 is the # of months. What I need to do is be able to calculate only for
> the range of months listed, and if the # of months is higher, I need to keep
> the calculation to a 12 month period only, not calculate higher with this set
> of formulas. I will then have another line with the same basic formula
> calculating for the next year range, but only for 25-36 months, and be 0 if
> F16 is < 25 months.
>
> Ideas? I can't figure out how to calculate only the months for the ranges-
> it keeps calculating more if F16 is greater than the range.
>
> HELP!!

Hi

For the sake of completeness in the Google archives, Sharona mailed her
file direct.
Her "really large formula" (RLF) ,which was computing a total cost with
inflation of costs over time, I just simplified a little, but that was
not really the issue.
Cell E16 held the number of months duration of a project. For time
periods over 12 months, Sharona needed a formula to take these costs
multiplied by the number of months applicable to each of years 2, 3, 4
and 5 and obviously not include any cost for the years past the
project's duration.
The formula I gave her, entered in cell E17 and copied down through
cells E18:E20 was

=(RLF)*MAX(0,($E$16-ROW(A1)*12)/12)
For a project duration of say 33 months, this would give values of 1 ,
..75, 0, and 0 and did away with the need for any of Sharona's IF
conditions.
--
Regards

Roger Govier


"Sharona77" <[email protected]> wrote in message
news:[email protected]...
> Thanks Roger- You were very helpful in solving my problem!!!!!
>
> S
>
> "Sharona77" wrote:
>
>> I made the following formula:
>> =IF(AND(F16>12,F16<25),(really large formula)/12*(F16-12),0)
>>
>> F16 is the # of months. What I need to do is be able to calculate
>> only for
>> the range of months listed, and if the # of months is higher, I need
>> to keep
>> the calculation to a 12 month period only, not calculate higher with
>> this set
>> of formulas. I will then have another line with the same basic
>> formula
>> calculating for the next year range, but only for 25-36 months, and
>> be 0 if
>> F16 is < 25 months.
>>
>> Ideas? I can't figure out how to calculate only the months for the
>> ranges-
>> it keeps calculating more if F16 is greater than the range.
>>
>> HELP!!