IF and MIN functions

JN · 07-18-2005, 01:05 PM

Hi,

I've been trying to fix the following formula but can't figure out why.

IF(OR(AG2<0,AE2<="07/29/05"),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))

What I am trying to do here is if the "Amt remained" is less than 0, OR
"Date" is less than 07/29/05, I want colum AH to show 0. Otherwise, apply the
calculations, which splits the amount into 2005 and 2006. However, I can't
make the formula show 0 for the dates that is less than 07/29/05. I am not
sure if it has to do with the MIN().

AE AG AH
AI
Date Amt Remained 2005 Portion
2006 Portion
2 1/22/93 $66,102 $2,246
$63,856
3 7/31/06 $27,349 $11,587
$15,762

Thanks!

**Morrigan** · 07-18-2005, 01:28 PM

Try replace "07/29/05" with 38562

ie. IF(OR(AG2<0,AE2<=38562),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))

Just a reminder, n your formula you also excluded the date of 07/29/05.

Hope this helps.

Originally Posted by JN

Hi,

I've been trying to fix the following formula but can't figure out why.

IF(OR(AG2<0,AE2<="07/29/05"),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))

What I am trying to do here is if the "Amt remained" is less than 0, OR
"Date" is less than 07/29/05, I want colum AH to show 0. Otherwise, apply the
calculations, which splits the amount into 2005 and 2006. However, I can't
make the formula show 0 for the dates that is less than 07/29/05. I am not
sure if it has to do with the MIN().

AE AG AH
AI
Date Amt Remained 2005 Portion
2006 Portion
2 1/22/93 $66,102 $2,246
$63,856
3 7/31/06 $27,349 $11,587
$15,762

Thanks!

Bob Phillips · 07-18-2005, 02:05 PM

Try this

IF(OR(AG2<0,AE2<=--"2005-07-29"),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE(--"2005
-12-31",--"2005-07-29")/NETWORKDAYS(--"2005-07-29",AE2))

or better yet, put the dates in cells and use the cell refrence.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"JN" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I've been trying to fix the following formula but can't figure out why.
>
>
IF(OR(AG2<0,AE2<="07/29/05"),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")
),"07/29/05")/NETWORKDAYS("07/29/05",AE2))
>
> What I am trying to do here is if the "Amt remained" is less than 0, OR
> "Date" is less than 07/29/05, I want colum AH to show 0. Otherwise, apply
the
> calculations, which splits the amount into 2005 and 2006. However, I can't
> make the formula show 0 for the dates that is less than 07/29/05. I am not
> sure if it has to do with the MIN().
>
> AE AG AH
> AI
> Date Amt Remained 2005 Portion
> 2006 Portion
> 2 1/22/93 $66,102 $2,246
> $63,856
> 3 7/31/06 $27,349 $11,587
> $15,762
>
>
> Thanks!
>

JN · 07-18-2005, 03:05 PM

Thanks. I used a cell reference at first, but it didn't work. So I typed out
the date in the formula, and it still doesn't work.

"Bob Phillips" wrote:

> Try this
>
> IF(OR(AG2<0,AE2<=--"2005-07-29"),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE(--"2005
> -12-31",--"2005-07-29")/NETWORKDAYS(--"2005-07-29",AE2))
>
> or better yet, put the dates in cells and use the cell refrence.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JN" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I've been trying to fix the following formula but can't figure out why.
> >
> >
> IF(OR(AG2<0,AE2<="07/29/05"),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")
> ),"07/29/05")/NETWORKDAYS("07/29/05",AE2))
> >
> > What I am trying to do here is if the "Amt remained" is less than 0, OR
> > "Date" is less than 07/29/05, I want colum AH to show 0. Otherwise, apply
> the
> > calculations, which splits the amount into 2005 and 2006. However, I can't
> > make the formula show 0 for the dates that is less than 07/29/05. I am not
> > sure if it has to do with the MIN().
> >
> > AE AG AH
> > AI
> > Date Amt Remained 2005 Portion
> > 2006 Portion
> > 2 1/22/93 $66,102 $2,246
> > $63,856
> > 3 7/31/06 $27,349 $11,587
> > $15,762
> >
> >
> > Thanks!
> >
>
>
>

JN · 07-18-2005, 03:05 PM

Can I use a cell reference in this formula? That way it's easier to update
the date since I need to change the date on weekly basis.

Or is there any way to approach to this formula?
Thanx

"Morrigan" wrote:

>
> Try replace "07/29/05" with 38562
>
> ie.
> IF(OR(AG2<0,AE2<=38562),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))
>
> Just a reminder, n your formula you also excluded the date of
> 07/29/05.
>
>
> Hope this helps.
>
>
>
> JN Wrote:
> > Hi,
> >
> > I've been trying to fix the following formula but can't figure out
> > why.
> >
> > IF(OR(AG2<0,AE2<="07/29/05"),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))
> >
> > What I am trying to do here is if the "Amt remained" is less than 0,
> > OR
> > "Date" is less than 07/29/05, I want colum AH to show 0. Otherwise,
> > apply the
> > calculations, which splits the amount into 2005 and 2006. However, I
> > can't
> > make the formula show 0 for the dates that is less than 07/29/05. I am
> > not
> > sure if it has to do with the MIN().
> >
> > AE AG AH
> > AI
> > Date Amt Remained 2005 Portion
> > 2006 Portion
> > 2 1/22/93 $66,102 $2,246
> > $63,856
> > 3 7/31/06 $27,349 $11,587
> > $15,762
> >
> >
> > Thanks!
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=388067
>
>

**Morrigan** · 07-18-2005, 03:41 PM

IF(OR(AG2<0,AE2<=value(<reference cell>)),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))

[QUOTE=JN]Can I use a cell reference in this formula? That way it's easier to update
the date since I need to change the date on weekly basis.

Or is there any way to approach to this formula?
Thanx

"Morrigan" wrote:
[color=blue]
>
> Try replace "07/29/05" with 38562
>
> ie.
> IF(OR(AG2<0,AE2<=38562),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))
>
> Just a reminder, n your formula you also excluded the date of
> 07/29/05.
>
>
> Hope this helps.
>