Rest of the Year Calculation

[Gfunc]

HI,

I am trying to make a cormula that calculates what someone would be on time
to achieve, based on their current number and then project what they would be
on time to hit based on how many months are left in the year.

For June, it being half way, the number is multiplied by 2, that one is
easy...

For some reason, I am having a hard time figuring out what the rest of the
calculations would be for each month.

Any ideas would be greatly appreciated

 

[Glenn]

HI,

I am trying to make a cormula that calculates what someone would be on time
to achieve, based on their current number and then project what they would be
on time to hit based on how many months are left in the year.

For June, it being half way, the number is multiplied by 2, that one is
easy...

For some reason, I am having a hard time figuring out what the rest of the
calculations would be for each month.

Any ideas would be greatly appreciated

Actually, for June it would be divided by 6 (June is the 6th month) and
multiplied by 12. Hope that helps you get the rest of the way.

 

[Gfunc]

I came up with this:

=IF(R2="August",S5*(12/8),(IF(R2="September",S5*(12/9),(IF(R2="October",S5*(12/10),(IF(R2="November",S5*(12/11),(IF(R2="December",S5*(12/12),(IF(R2="June",S5*(12/6),(IF(R2="July",S5*(12/7))))))))))))))

unfortunately I can only get 7 on there...

Gfunc

 

[joeu2004]

I came up with this:
=IF(R2="August",S5*(12/8),(IF(R2="September",S5*(12/9),
(IF(R2="October",S5*­(12/10),(IF(R2="November",S5*(12/11),
(IF(R2="December",S5*(12/12),(IF(R2="J­une",S5*(12/6),
(IF(R2="July",S5*(12/7))))))))))))))
unfortunately I can only get 7 on there...

First, it would be easier if R2 contained a date (e.g. 8/31/2008)
instead of text. You can use the custom format "mmmm" is you want to
see just the name of the month.

With that change, your formula can be simplified as follows:

=S5*12/month(R2)

That gives the same result as your IF() expression. I cannot say that
it gives you a meaningful result. Your requirements are not clear to
me.

 

[Glenn]

I came up with this:

=IF(R2="August",S5*(12/8),(IF(R2="September",S5*(12/9),(IF(R2="October",S5*(12/10),(IF(R2="November",S5*(12/11),(IF(R2="December",S5*(12/12),(IF(R2="June",S5*(12/6),(IF(R2="July",S5*(12/7))))))))))))))

unfortunately I can only get 7 on there...

Gfunc

If you actually have the text names of the month in R2, use the following:

=S5*12/((((FIND(LEFT(R2,3),"JanFebMarAprMayJunJulAugSepOctNovDec"))-1)/3)+1)

 

[Roger Govier]

Hi Try
=S5*12/MONTH(--("1"&R2&"2008"))