Formula to spread number across
 

Hi,

I want to set up a formula which divides the remaining dollars left by
remaining workdays left for a period specified. If the date goes over
12/31/05, I want it to stop calculate.

For example, the remaining dollar is 30,000. The period is 03/01/05 to
01/01/06.

Thanks!

A
1 3/1/05
2 1/1/06
3 30000

=A3/(NETWORKDAYS(A1,MIN(A2,DATEVALUE("12/31/05"))))

Note Networkdays has an optional array argument to include state,federal,and
floating holidays,if you want these excluded.



"JN" wrote:

Hi,

I want to set up a formula which divides the remaining dollars left by
remaining workdays left for a period specified. If the date goes over
12/31/05, I want it to stop calculate.

For example, the remaining dollar is 30,000. The period is 03/01/05 to
01/01/06.

Thanks!

Thanks. Can you tell me what the second part of the formula does - MIN and
Datevalue? This will allow me to change the formula in the future. Thanx.

"JMB" wrote:

A
1 3/1/05
2 1/1/06
3 30000

=A3/(NETWORKDAYS(A1,MIN(A2,DATEVALUE("12/31/05"))))

Note Networkdays has an optional array argument to include state,federal,and
floating holidays,if you want these excluded.



"JN" wrote:

Hi,

I want to set up a formula which divides the remaining dollars left by
remaining workdays left for a period specified. If the date goes over
12/31/05, I want it to stop calculate.

For example, the remaining dollar is 30,000. The period is 03/01/05 to
01/01/06.

Thanks!

Please disregard my other response. The formula works well. Thanx!

"JMB" wrote:

A
1 3/1/05
2 1/1/06
3 30000

=A3/(NETWORKDAYS(A1,MIN(A2,DATEVALUE("12/31/05"))))

Note Networkdays has an optional array argument to include state,federal,and
floating holidays,if you want these excluded.



"JN" wrote:

Hi,

I want to set up a formula which divides the remaining dollars left by
remaining workdays left for a period specified. If the date goes over
12/31/05, I want it to stop calculate.

For example, the remaining dollar is 30,000. The period is 03/01/05 to
01/01/06.

Thanks!

you're welcome!

"JN" wrote:

Please disregard my other response. The formula works well. Thanx!

"JMB" wrote:

A
1 3/1/05
2 1/1/06
3 30000

=A3/(NETWORKDAYS(A1,MIN(A2,DATEVALUE("12/31/05"))))

Note Networkdays has an optional array argument to include state,federal,and
floating holidays,if you want these excluded.



"JN" wrote:

Hi,

I want to set up a formula which divides the remaining dollars left by
remaining workdays left for a period specified. If the date goes over
12/31/05, I want it to stop calculate.

For example, the remaining dollar is 30,000. The period is 03/01/05 to
01/01/06.

Thanks!