Multiple IFs/Calc. Weeks

[Beth]

Hello all. This can't possibly be as hard as I'm making
it. I have a forecast sheet that needs to calculate goals
throughout the month. This is done by weeks. i.e. for the
month, Bob Smith needs to sell 100,000 units. The first
week of the month, Bob needs to sell 100,000 per week to
reach his goal, but as the month wears on, I want to
automate the formula to calculate that currently, Bob has
sold 50,000 units and has 2 weeks left to hit his 100,000
unit goal, so he needs to sell 25,000 units per week to
get there.
I thought about doing =if(today()<10/6/2004,4,0); but
that only gives me one result, and I'd like to be able to
point at one cell reference only to truly automate it -
i.e. Total Goal-Current Sales/Number of Weeks. The problem
I'm having is not knowing how (or if it's even possible)
to nest multiple IF's, so I could do something that will
give me the result in one cell for if today()<10/6/2004,4
as well as if today()<10/11/2004,3 and if
today<10/16/2004,2 etc. Even thought of doing
=networkingdays, but I would still have 4 different
results/cell references to reconcile for >26, <20, etc.
Thanks for taking the time to read this, and any
suggestions are most appreciated.

Beth

 

[Govind]

Hi Beth,

Lets assume your weeday starts on Monday, do you have all the mondays of
hte month hardcoded somewhere .If not , enter the first day of the month
in a cell say A1 and enter this formula in cells A2 to A6:

In A2 enter =
=IF(WEEKDAY(A1,2)=1,A1,DATE(YEAR(A1),MONTH(A1),DAY(A1)+8-WEEKDAY(A1,2)))
In A3 enter =
=IF(MONTH(DATE(YEAR(A2),MONTH(A2),DAY(A2)+7))=MONTH($A$1),DATE(YEAR(A2),MONTH(A2),DAY(A2)+7),"")
and copy A3 down and paste it till A6.

This will give you all the mondays in the month you had specified in
cell A1.

Lets say you enter the target in cell B1 and units sold till now in
cell B3, use this formula in cell C1

=IF(TODAY()<A2,(B1-B2)/4,IF(TODAY()<A3,(B1-B2)/3,IF(TODAY()<A4,(B1-B2)/2,IF(TODAY()<A5,(B1-B2),""))))

Let me know of any questions.

regards

Govind.

 

[Ron Rosenfeld]

Total Goal-Current Sales/Number of Weeks

Your problem is not well defined, and that may be part of the difficulty.

For example, most months do not have an integer number of weeks. If you wanted
to calculate required daily sales, then the formula would be something like:

=(Total_Goal-Current_Sales)/
(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-TODAY())

I suppose you could multiply that number by 5,6 or 7 (depending on the number
of days in a work week) to get some approximation, but it would only be that.
That may be all you need.

And the required amount should differ depending on whether you run the formula
at the beginning or the end of the day, so depending on required precision, you
could really get carried away here.

You may need to think more about what you are going to do with this number and
what it really means.


--ron

 

[OZDOC1050]

IN CELL A1 PLACE THE START DATE
IN CELL A2 PLACE THE TARGET UNITS
IN CELL A3 PLACE THE NUMBER OF MONTHS ( 1 )
IN CELL A4 PLACE CURRENT UNIT SALES
IN CELL A5 =(A2-A4)/(DAYS360(NOW(),EDATE(A1,A3))/7)

SEE NOTES ON EDATE BELOW
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

A
1 14/10/2004
2 100,000
3 1
4 24,000
5 23,130


THIS IS ONE WAY AND YOU COULD REMOVE A3 IF WAS A SET MONTH, HOPE IT HELPS

R

PETE

 

[OZDOC1050]

IN CELL A5
=(A2-A4)/((((NETWORKDAYS(A1,(EDATE(A1,A3))))-NETWORKDAYS(A1,NOW())))/5)

USING WORKING DAYS
WHICH IS PROBABLY WHAT U R AFTER
R
PETE

--
(][ This Email has been scanned by Norton AntiVirus. ][)

IN CELL A1 PLACE THE START DATE
IN CELL A2 PLACE THE TARGET UNITS
IN CELL A3 PLACE THE NUMBER OF MONTHS ( 1 )
IN CELL A4 PLACE CURRENT UNIT SALES
IN CELL A5 =(A2-A4)/(DAYS360(NOW(),EDATE(A1,A3))/7)

SEE NOTES ON EDATE BELOW
If this function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

A
1 14/10/2004
2 100,000
3 1
4 24,000
5 23,130


THIS IS ONE WAY AND YOU COULD REMOVE A3 IF WAS A SET MONTH, HOPE IT HELPS

R

PETE


--
(][ This Email has been scanned by Norton AntiVirus. ][)

Hello all. This can't possibly be as hard as I'm making
it. I have a forecast sheet that needs to calculate goals
throughout the month. This is done by weeks. i.e. for the
month, Bob Smith needs to sell 100,000 units. The first
week of the month, Bob needs to sell 100,000 per week to
reach his goal, but as the month wears on, I want to
automate the formula to calculate that currently, Bob has
sold 50,000 units and has 2 weeks left to hit his 100,000
unit goal, so he needs to sell 25,000 units per week to
get there.
I thought about doing =if(today()<10/6/2004,4,0); but
that only gives me one result, and I'd like to be able to
point at one cell reference only to truly automate it -
i.e. Total Goal-Current Sales/Number of Weeks. The problem
I'm having is not knowing how (or if it's even possible)
to nest multiple IF's, so I could do something that will
give me the result in one cell for if today()<10/6/2004,4
as well as if today()<10/11/2004,3 and if
today<10/16/2004,2 etc. Even thought of doing
=networkingdays, but I would still have 4 different
results/cell references to reconcile for >26, <20, etc.
Thanks for taking the time to read this, and any
suggestions are most appreciated.

Beth