software

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

quot;Roger Govierquot; wrote:

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

Hi Sharona

Still not sure I understand things fully from your explanation, but of
course it is quite obvious to yoult;bggt;

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

quot;Roger Govierquot; wrote:

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

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

S

quot;Sharona77quot; wrote:

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

Hi

For the sake of completeness in the Google archives, Sharona mailed her
file direct.
Her quot;really large formulaquot; (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 Govierquot;Sharona77quot; gt; wrote in message
...
gt; Thanks Roger- You were very helpful in solving my problem!!!!!
gt;
gt; S
gt;
gt; quot;Sharona77quot; wrote:
gt;
gt;gt; I made the following formula:
gt;gt; =IF(AND(F16gt;12,F16lt;25),(really large formula)/12*(F16-12),0)
gt;gt;
gt;gt; F16 is the # of months. What I need to do is be able to calculate
gt;gt; only for
gt;gt; the range of months listed, and if the # of months is higher, I need
gt;gt; to keep
gt;gt; the calculation to a 12 month period only, not calculate higher with
gt;gt; this set
gt;gt; of formulas. I will then have another line with the same basic
gt;gt; formula
gt;gt; calculating for the next year range, but only for 25-36 months, and
gt;gt; be 0 if
gt;gt; F16 is lt; 25 months.
gt;gt;
gt;gt; Ideas? I can't figure out how to calculate only the months for the
gt;gt; ranges-
gt;gt; it keeps calculating more if F16 is greater than the range.
gt;gt;
gt;gt; HELP!!