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>

 

[XOR LX]

(self-deleted: double-post)

 

[Captain Hindsight]

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)
</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

This returns 0's.

Values in the table begin from E5, I removed the column which said if deliveries was >1 as your formula ignored this.

Dates horizontally begin from E3.
Vertically from B5.

Deliveries is in column C, beginning row 5.

I don't quite follow this:

IF(COLUMNS($A:A)<ROWS($1:1),

when would columns be greater than rows?

as we go along in one row so in that row it always looks up to one column, and when going down again each row only looks up to one column



great effort though so far i do appreciate your help

 

[XOR LX]

I have a sheet here with that formula pasted into E5 and copied across and down which gives precisely the same results as in your desired output table from thread #29.

I'm not sure I can do much more than that!

Regards

 

[XOR LX]

Also,

"I removed the column which said if deliveries was >1 as your formula ignored this."

No. The formula contains a clause which checks whether the corresponding number of deliveries is greater than zero, not one, as per your original instruction:

"In the formula I posted I also have the exception, 'if the value in column D = 0, return nothing' which is important."

Regards

 

[Captain Hindsight]

Also,

"I removed the column which said if deliveries was >1 as your formula ignored this."

No. The formula contains a clause which checks whether the corresponding number of deliveries is greater than zero, not one, as per your original instruction:

"In the formula I posted I also have the exception, 'if the value in column D = 0, return nothing' which is important."

Regards

I meant >0, but your formula looked up to column C which had number of deliveries, whereas column D was one I created which just said if column C > 0, return 1. It is this column I deleted which shouldn't have any impact.

I will have a play about with your formula and try to make it work, not sure what will be different on what you have created but it is something.

Best regards & thanks for persevering

 

[Captain Hindsight]

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),I F(MOD(1+COLUMNS($A:A)-ROWS($1:1),4)=0,0,$C$2))),0)


Regards

Ah I got it!

I changed the $D5>0 to $C5>0 and there was a space between one of the IF functions

Brilliant job

 

[XOR LX]

Ah, sorry! It was the post where the editor was really messing up, so must have slipped a space in there somehow!

Well spotted and I'm glad we got there (in the end)!

All the best

 

[Captain Hindsight]

Ah, sorry! It was the post where the editor was really messing up, so must have slipped a space in there somehow!

Well spotted and I'm glad we got there (in the end)!

All the best

Me again , I have one more question for you if you don't mind

I need to adapt the formula slightly, and think I am pretty much there with it. I have this:

=IF($C5>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,'Unbalanced cost calculations'!B$50))),0)

EDIT - I have put the formula on two lines because it keeps cutting it off

<ROWS($1:1),0,IF(COLUMNS($A:A)<3+ROWS($1:1),IF(COLUMNS($A:A)+ROWS($1:1)=2*ROWS($1:1),0,'Unbalanced color="#ff0000" calculations?!B$50))<font cost><ROWS($1:1),0,IF(COLUMNS($A:A)<3+ROWS($1:1),IF(COLUMNS($A:A)+ROWS($1:1)=2*ROWS($1:1),0,'Unbalanced color="#ff0000" calculations?!B$50))<font cost>Instead of looking at $C$2 it now looks at some numbers going horizontally on a different tab. I have also removed the condition about every 4th cell being 0 as this is not needed in this instance ....

My question is, instead of returning me 0s when one of the conditions in the formula above is not met, it returns me 'FALSE'.

Now I know this is because I have not specified to return 0, but when I try to do so where I think it should go (before the bracket I have bolded and highlighted red) and try to add ",0", it tells me I have entered too few arguments.

Can you help please?

Should be a quick one for you,
Thanks</ROWS($1:1),0,IF(COLUMNS($A:A)<3+ROWS($1:1),IF(COLUMNS($A:A)+ROWS($1:1)=2*ROWS($1:1),0,'Unbalanced></ROWS($1:1),0,IF(COLUMNS($A:A)<3+ROWS($1:1),IF(COLUMNS($A:A)+ROWS($1:1)=2*ROWS($1:1),0,'Unbalanced>