Converting algorithm to formula ( embedded IF AND )

[Captain Hindsight]

Morning,

I have the following algorithm:

N</SPAN>

Y</SPAN>

Y</SPAN>

N</SPAN>

Y</SPAN>

Y</SPAN>

Y</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

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

</SPAN>

</SPAN>

</SPAN>

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[Captain Hindsight]

Yes, exactly.

But, only starting from the first cell in which the previous month's column's month ref = this months row ref.

e.g. if we were working in the row for deliveries in Jan 2016 (this row would be further down the sheet), then I only want to start applying the pattern in Feb 2016 (as there are no deliveries in month 1 as per the pattern).

 

[adam087]

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

Thank you, much neater than mine.

/AJ

 

[Captain Hindsight]

Right, so the months going down the page vertically will look like this.

Jan
Feb
Mar
Apr
May

In the column next to this, I have a number of deliveries e.g.

1
4
0
2
3

In column D I have just said if column C is greater than 0, return a 1. This 1 or 0 in column D just indicated if there have been any deliveries.

 

[XOR LX]

Actually I thought yours was neater! One LOOKUP versus three IF statements (not sure which one Excel would prefer, though )

 

[Captain Hindsight]

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

I am new to forums hopefully you were notified of my first response.

However I would like to stress that I do not want "Yes" or "No" returned.

In place of $C$2 needs to be used, and 0 in place on N.

Thanks for repsonses so far

 

[XOR LX]

You mean simply:

=IF(COLUMNS($A:A)<4,IF(MOD(COLUMNS($A:A),3)=1,0,$C$2),IF(MOD(COLUMNS($A:A),4)=0,0,$C$2)) ?

Regards

 

[Captain Hindsight]

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

Ok this formula works for the first month going vertically down:

=IF(COLUMNS($A:A)<4,IF(MOD(COLUMNS($A:A),3)=1,0,$C$2),IF(MOD(COLUMNS($A:A),4)=0,0,$C$2))

So that's brilliant, thanks for that.

However going down it needs to have some exceptions as per the intiial formula I posted i.e. only starting to be applied when the month column in format $E3 = the month row in format C$5

The other condition is that if the value in column D = 0, return nothing.

Apart from those two things, perfect!

 

[Captain Hindsight]

You mean simply:

=IF(COLUMNS($A:A)<4,IF(MOD(COLUMNS($A:A),3)=1,0,$C$2),IF(MOD(COLUMNS($A:A),4)=0,0,$C$2)) ?

Regards

See my comment above please for the exceptions I need to add to the formula. However yes I was just letting people know I needed values not text.

Thanks

 

[XOR LX]

"Ok this formula works for the first month going vertically down:"

Hmmm. I though you were indicating that this formula was to be dragged to the right, not down. In fact, dragged down that formula will not give you your desired results (unless by pure luck) - can you please clarify?

I'm afraid that the rest of it is becoming too hard to visualize without seeing an actual sheet, but hopefully you can adapt it to meet your needs (since you can see your actual set-up in front of you!)

Regards

 

[Captain Hindsight]

"Ok this formula works for the first month going vertically down:"

Hmmm. I though you were indicating that this formula was to be dragged to the right, not down. In fact, dragged down that formula will not give you your desired results (unless by pure luck) - can you please clarify?

I'm afraid that the rest of it is becoming too hard to visualize without seeing an actual sheet, but hopefully you can adapt it to meet your needs (since you can see your actual set-up in front of you!)

Regards

It works perfectly being dragged to the right.

I also need to be able to drag it down.

The two exceptions needed are:

1) If we are in row 6, that $D6>0
2) (Months are going horizontally in row 3 starting from column E, and vertically in column B starting from row 5) If we are in F5, that E5 = B5. This condition identifies when to start applying the pattern. It is set to look at the previous month ref in the column as the first month is 0 (as per the pattern).

Hope that makes sense and I would really appreciate a formula with these two things added.