I need help with the following formula.
Colum C3 contains the month end date
Column A6 Contains a code
Column H6 contains the start date
Column I6 contains the end date
Colum J6 contains a value.

I have the formula
=IF(AND(I6<$C$3,A6<207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2)

I need to alter the formula so that if the end date is less than the month
end date then no value will show.
Thanks

Possibly this might suffice:
=IF(OR($C$3="",I6="",I6<$C$3),"",IF(AND(I6<$C$3,A6 <207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2))

The above adds the front IF checks on C3/I6 not being blank,
and your posted reqt for it to return blank if I6 is less than C6, viz.:
=IF(OR($C$3="",I6="",I6<$C$3),"",<your formula)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Funkyfido" wrote:
I need help with the following formula.
Colum C3 contains the month end date
Column A6 Contains a code
Column H6 contains the start date
Column I6 contains the end date
Colum J6 contains a value.

I have the formula
=IF(AND(I6<$C$3,A6<207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2)

I need to alter the formula so that if the end date is less than the month
end date then no value will show.
Thanks

Max

C3 or I6 will never be blank so this doesn't work for me.


"Max" wrote:

Possibly this might suffice:
=IF(OR($C$3="",I6="",I6<$C$3),"",IF(AND(I6<$C$3,A6 <207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2))

The above adds the front IF checks on C3/I6 not being blank,
and your posted reqt for it to return blank if I6 is less than C6, viz.:
=IF(OR($C$3="",I6="",I6<$C$3),"",<your formula)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Funkyfido" wrote:
I need help with the following formula.
Colum C3 contains the month end date
Column A6 Contains a code
Column H6 contains the start date
Column I6 contains the end date
Colum J6 contains a value.

I have the formula
=IF(AND(I6<$C$3,A6<207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2)

I need to alter the formula so that if the end date is less than the month
end date then no value will show.
Thanks

How about this:

=IF(I6<$C$3,"",IF((A6<207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2))


"Funkyfido" wrote:

Max

C3 or I6 will never be blank so this doesn't work for me.


"Max" wrote:

Possibly this might suffice:
=IF(OR($C$3="",I6="",I6<$C$3),"",IF(AND(I6<$C$3,A6 <207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2))

The above adds the front IF checks on C3/I6 not being blank,
and your posted reqt for it to return blank if I6 is less than C6, viz.:
=IF(OR($C$3="",I6="",I6<$C$3),"",<your formula)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Funkyfido" wrote:
I need help with the following formula.
Colum C3 contains the month end date
Column A6 Contains a code
Column H6 contains the start date
Column I6 contains the end date
Colum J6 contains a value.

I have the formula
=IF(AND(I6<$C$3,A6<207003),($C$3-I6)/(I6-H6)*J6,($C$3-I6-($C$3-I6)/7)*2)

I need to alter the formula so that if the end date is less than the month
end date then no value will show.
Thanks

C3 or I6 will never be blank so this doesn't work for me.

It's harmless to include the additional checks on C3/I6 being blank**.
The earlier suggestion (below) should still work fine:
=IF(OR($C$3="",I6="",I6<$C$3),"",<your formula)
since the 3rd check: I6<$C$3 will take care of your reqt
(I'm not sure that you did try it out?)

It could of course, be reduced to just this:
=IF(I6<$C$3,"",<your formula)

**the precaution is because blank cells are evaluated as zeros in formulas,
and dates are just numbers, so if I6 is blank and C3 contains a date, then
the check I6<$C$3 would misleadingly return TRUE.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---