Converting algorithm to formula ( embedded IF AND )

Captain Hindsight · Oct 15, 2013

Morning,

I have the following algorithm:

N

Y

N

Y

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

XOR LX · Oct 15, 2013

"I also need to be able to drag it down."

If by that you mean that, when you drag it down, the same repeating pattern as in the row above is reproduced, then yes, you can.

"If we are in row 6, that $D6>0"

Ok. Think I get that.

"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)."

But not sure about this one. Do you mean that every previous value in a row will be compared to the corresponding entry in column B? But won't that only happen if that entry is either 0 or equal to the value in C2?

Without that last condition, in E3:

=IF($D3>0,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 · Oct 15, 2013

XOR LX said:
"I also need to be able to drag it down."

If by that you mean that, when you drag it down, the same repeating pattern as in the row above is reproduced, then yes, you can.

"If we are in row 6, that $D6>0"

Ok. Think I get that. I mean that if the value in column D is 0 (no deliveries), I don't want the pattern to be applied I want 0's all the way long.

"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)."

But not sure about this one. Do you mean that every previous value in a row will be compared to the corresponding entry in column B? But won't that only happen if that entry is either 0 or equal to the value in C2? I mean that, if you imagine a grid with months going along the top (Jan - Dec) and going vertically (Jan - Dec) with the pattern being applied going down to fill in the grid, that I want the pattern to start being applied where the previous months column ref (e.g. Feb), is equal to the current months row ref (would be March). The reason for this is that I don't want to see any values, until the month of the delivery i.e. if the pattern begins in June, I want to see nothing before July (because the first part of the pattern is an N, there would be nothing in June).

Without that last condition, in E3:

=IF($D3>0,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

please see the comments I have inserted into your answer.

Thanks again for your help so far

XOR LX · Oct 15, 2013

So can you confirm your previous example: "If we are in F5, that E5 = B5."?

This seems to contradict what you have just said about the column and row headers as, unless I'm mistaken, E5 is not a column header - you said these were in row 3 (?)

Regards

Captain Hindsight · Oct 15, 2013

Sorry!!

**E3 not E5

I'm not trying to make it MORE confusing, honest

XOR LX · Oct 15, 2013

"If we are in F5, that E3 = B5"

Ok, but is this still correct? Are you sure you don't want the pattern to start, and be repeated, for all months which are after a certain date? Currently, as with the above criteria applied to every single cell, this is what you'd have (in E5):

=IF($B5=D$3,IF($D5>0,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)),0),"")

Are you sure it shouldn't be something like:

=IF($B5>=D$3,IF($D5>0,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)),0),"")

or perhaps (not sure which way round these row/column rules should be):

=IF($B5<=D$3,IF($D5>0,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)),0),"")

If that doesn't help, I'm afraid I think I'm going to have to call it a day!

Regards

Captain Hindsight · Oct 15, 2013

XOR LX said:
"If we are in F5, that E3 = B5"

Ok, but is this still correct? Are you sure you don't want the pattern to start, and be repeated, for all months which are after a certain date? Currently, as with the above criteria applied to every single cell, this is what you'd have (in E5):

=IF($B5=D$3,IF($D5>0,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)),0),"")

Are you sure it shouldn't be something like:

=IF($B5>=D$3,IF($D5>0,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)),0),"")

or perhaps (not sure which way round these row/column rules should be):

=IF($B5<=D$3,IF($D5>0,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)),0),"")

If that doesn't help, I'm afraid I think I'm going to have to call it a day!

Regards

That last formula is very close!

I am now getting values returned in the correct cell, and on the first row it is in the correct pattern.

However as the pattern is NYY, NYYY, NYYY
in row 2 (Feb 16) I am now getting YY, NYYY, NYYY
then in row 3 I am geyying Y, NYYY, NYYY
row 4 I am getting NYYY, NYYY

etc.

This pattern needs to begin to be applied, from where $B5 = D$3

So close now!!

so that in Feb 16 column, Jan 15 (row), we are getting the first part of the pattern (the 0).

For visual:

______JFM AMJJ ASON < months

Jan 16: NYY NYYY NYYY
Feb 16: 0NY YNYY YNYY
Mar 16 00N YYNY YYNY

As you can see the pattern begins to be applied from the month of introduction.

XOR LX · Oct 15, 2013

Sorry - completely lost now. Unless you can provide a link to a sheet with your desired results I'll have to wish you the best of luck with it.

Regards

Captain Hindsight · Oct 15, 2013

XOR LX said:
Sorry - completely lost now. Unless you can provide a link to a sheet with your desired results I'll have to wish you the best of luck with it.

Regards

How do I do that?

I took two screenshots of what it is and what I want but I don't have them as URL.

Captain Hindsight · Oct 15, 2013

XOR LX said:
Sorry - completely lost now. Unless you can provide a link to a sheet with your desired results I'll have to wish you the best of luck with it.

Regards

I want it to look like:

Batch 1

0

1

2

3

4

5

1

239.555

1

2

3

4

5

6

7

8

9

10

11

Dec-15

Jan-16

Feb-16

Mar-16

Apr-16

May-16

Jun-16

Jul-16

Aug-16

Sep-16

Oct-16

Month of introduction

Date

Delivered

>0 ?

1

Dec-15

1

239.555

0

239.555

0

239.555

2

Jan-16

3

1

0

239.555

0

239.555

0

239.555

3

Feb-16

2

1

0

239.555

0

239.555

0

239.555

4

Mar-16

2

1

0

239.555

0

239.555

0

5

Apr-16

4

1

0

239.555

0

239.555

6

May-16

3

1

0

239.555

0

239.555

7

Jun-16

0

8

Jul-16

3

1

0

239.555

0

9

Aug-16

2

1

0

239.555

10

Sep-16

0

11

Oct-16

0

Currently looks like:

Batch 1

0

1

2

3

4

5

1

239.555

1

2

3

4

5

6

7

8

9

10

11

Dec-15

Jan-16

Feb-16

Mar-16

Apr-16

May-16

Jun-16

Jul-16

Aug-16

Sep-16

Oct-16

Month of introduction

Date

Delivered

>0 ?

1

Dec-15

1

239.555

0

239.555

0

239.555

2

Jan-16

3

1

239.555

0

239.555

0

239.555

3

Feb-16

2

1

0

239.555

0

239.555

4

Mar-16

2

1

239.555

0

239.555

5

Apr-16

4

1

239.555

0

239.555

6

May-16

3

1

239.555

0

239.555

7

Jun-16

0

8

Jul-16

3

1

239.555

9

Aug-16

2

1

239.555

10

Sep-16

0

11

Oct-16

0

XOR LX · Oct 15, 2013

Hi,

Try this:

=IF($D5>0,IF(COLUMNS($A:A)<ROWS($1:1),0,IF(COLUMNS($A:A)<3+ROWS($1:1),IF(COLUMNS($A:A)+ROWS($1:1)=2*ROWS($1:1),0,$C$2),IF(MOD(1+COLUMNS($A:A)-ROWS($1:1),4)=0,0,$C$2))),0)

Regards

Converting algorithm to formula ( embedded IF AND )

Captain Hindsight

New Member

XOR LX

Well-known Member

Excel Facts

Captain Hindsight

New Member

XOR LX

Well-known Member

Captain Hindsight

New Member

XOR LX

Well-known Member

Captain Hindsight

New Member

XOR LX

Well-known Member

Captain Hindsight

New Member

Captain Hindsight

New Member

XOR LX

Well-known Member

Similar threads

Forum statistics

Share this page

Converting algorithm to formula ( embedded IF AND )

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock