Complex formula

[wayneryeven]

Hey all,
Been stuck on this formula for past hour!







Item DemDate DemQty SupDate SupQty Supply
1 10/08/2011 62 20/08/2011 500 0
1 11/08/2011 106 20/08/2011 500 500
1 26/08/2011 106 20/08/2011 500 0
2 26/08/2011 100 20/08/2011 100 100

The SupDate indicates when a supply of an item is due (and appears beside each item which cant be changed). Thus for above there is a SupQty of 500 units due for Item 1 on the 20/08.

I need to write a formula in Supply column (F) that does this:
FOR THAT ITEM, if SupDate<DemDate then Supply = SupQty
If SupDate > DemDate AND < ext DemDate (for that item) Supply= SupQty
Otherwise Supply= 0

Very complex and difficult to understand, i hope i have explained it somewhat.

Thanks for all input,

 

[GlennUK]

Can you write this:

FOR THAT ITEM, if SupDate<DemDate then Supply = SupQty
If SupDate > DemDate AND < ext DemDate (for that item) Supply= SupQty
Otherwise Supply= 0


in English? ( what does "< ext DemDate" mean? )

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[wayneryeven]

< less than?

I have got it working i think...

=IF(A4=A3,IF(AND(F4>=B4,F4<B5),G4,0),IF(F4<=B4,G4,0))

I now need to get it so that if A4=A3 DOES NOT equal then Supply=SupQty.

Any ideas how i could cover that in the formula?

 

[Andrzejek]

Your request does not make much sense:
[tt]
FOR THAT ITEM,
if [blue]SupDate < DemDate[/blue] then [blue]Supply = SupQty[/blue]
If [blue]SupDate > DemDate[/blue] AND < ext DemDate (for that item) [blue]Supply = SupQty[/blue]
Otherwise Supply= 0[/tt]

I hope this text will align so you can see what I mean....

Have fun.

---- Andy

 

[Andrzejek]

"if A4=A3 DOES NOT equal then Supply=SupQty" -- What?

DOES NOT equal -> to what?

Have fun.

---- Andy

 

[kjv1611]

wayner1980,

Take your time to type what you need in clear concise statements, and you'll have a far better chance at getting the desired results. Frankly, if you do that, you might see how to fix it yourself anyway.

Whatever you do, please specify what your final working solution is - both the formula and describe what it's doing. It's rather difficult to fully comprehend your request so far, because it seems to have discrepancies... such as what Andy pointed out in his last post.

 

[wayneryeven]

This worked (some columsn added). I had too many arguments in my IF statement:

=IF(A2=A1,IF(AND(F2>=B2,F2<B3,H1=0),G2,0),IF(F2<=B2,G2,G2))

Thanks for the input and feedback. Like many things, sometimes its easy to have the vision and gesticulate looking at the problem, but conveying this in written form is difficult.

For archival, if anyone wishes to see the working solution and demo please let me know.

Thanks