Converting algorithm to formula ( embedded IF AND )

[Captain Hindsight]

Morning,

I have the following algorithm:
[TABLE="width: 505"]
<TBODY>[TR]
[TD]N</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

The part I have bolded repeats infinitely afterwards. I need to convert this into an excel formula.

Where a 'Y' occurs, I want excel to return me the value in cell C2.

I have the following formula:

=IF(AND($D$7<>0,OR(D$3=$B5,C$3=$B5,D5+C5+B5=(2*$C$2))),$C$2,0)

which gives me the NYYY, NYYY, NYYY etc.

I have done this by saying if the previous month's column cell = the current month's row cell, return a value to get the first 'NY' part. I then repeated this to get to 'NYY', then have the condition that if the sum of the previous 3 cells = 2*the value in C2, give me a value to get NYYY, NYYY, NYYY etc.

I need to add in something to get that first part of the algorithm above (not in bold).

Any suggestions?

Thank you
[TABLE="width: 505"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[adam087]

Not going to lie, struggling to understand what is required here. This may be my lack of understanding

/AJ

 

[XOR LX]

I have to admit I'm completely baffled as well, even after the third re-read.

 

[Captain Hindsight]

Sorry people, I'll try to explain again.

I'll give it some context.

I have a delivery schedule, with months running horizontally along the top of the page, and vertically down the page.
The months running vertically have a 1 or a 0 next to them, which is to show whether any product has been delivered (1 if yes).

There is a pattern of which deliveries occur in months, which is as follow: No, Yes, Yes, No, Yes, Yes, Yes. i.e. in month 1 no product is delivered, in month 2 some product is delivered etc.

I want to make a formula for this which can be applied to the whole page.

When a delivery occurs, I want excel to return me the value in cell C2 which could be, for example, £100 i.e. every time there is a delivery it costs me £100.

Does this help?

 

[Captain Hindsight]

Please see my response below, I hope that helps.

Thanks

 

[Captain Hindsight]

I have to admit I'm completely baffled as well, even after the third re-read.

Please see my response below, I hope that helps.

Thanks

 

[adam087]

Well, I think this will do it, but it's not very flexible if you need changing patterns...
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
Y	N	N	N	Y	N	N	N	Y	N	N

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]2[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=LOOKUP(MOD(COLUMN()-1, 4), {0,1,2,3}, {"Y","N","N","N"})[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



/AJ

 

[XOR LX]

Leaving the cost calculations aside for just one minute, can we just concentrate on this part first:

"There is a pattern of which deliveries occur in months, which is as follow: No, Yes, Yes, No, Yes, Yes, Yes."

Do you mean that you would like a formula such that, for example, when you enter it into a cell (wherever it may be in the sheet) and then copy to the right (or is it down?) indefinitely it will produce this repeating sequence?

Regards

 

[Captain Hindsight]

Well, I think this will do it, but it's not very flexible if you need changing patterns...
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1
2	Y	N	N	N	Y	N	N	N	Y	N	N

<TBODY>
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]11
[/TD]

</TBODY>

Sheet3​

/AJ

Thank you for your response. However I need values rather than "Y" or "N".

Y or N is to indicate whether a value needs to be returned.

Also, The final part 'NYYY' repeats infinitely, so it would look like NYY, NYYY, NYYY, NYYY, NYYY, NYYY etc.

In the formula I posted I also have the exception, 'if the value in column D = 0, return nothing' which is important.
[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="bgcolor: #dae7f5"]A2
[/TH]
[TD="align: left"]=LOOKUP(MOD(COLUMN()-1, 4), {0,1,2,3}, {"Y","N","N","N"})
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[XOR LX]

This will get you your pattern:

=IF(COLUMNS($A:A)<4,IF(MOD(COLUMNS($A:A),3)=1,"No","Yes"),IF(MOD(COLUMNS($A:A),4)=0,"No","Yes"))

However, I'm still completely non-plussed as to from where these values (column D?) are to be drawn.

Regards