Converting algorithm to formula ( embedded IF AND )

Captain Hindsight

New Member
Joined
Oct 9, 2013
Messages
46
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>
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Not going to lie, struggling to understand what is required here. This may be my lack of understanding :(

/AJ
 
Upvote 0
I have to admit I'm completely baffled as well, even after the third re-read.
 
Upvote 0
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?
 
Upvote 0
Well, I think this will do it, but it's not very flexible if you need changing patterns...
Excel 2010
ABCDEFGHIJK
11234567891011
2YNNNYNNNYNN

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
A2=LOOKUP(MOD(COLUMN()-1, 4), {0,1,2,3}, {"Y","N","N","N"})

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



/AJ
 
Upvote 0
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
 
Upvote 0
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
1
2
3
4
5
6
7
8
9
10
11
2
Y
N
N
N
Y
N
N
N
Y
N
N

<TBODY>
</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.
Worksheet Formulas
Cell
Formula
A2
=LOOKUP(MOD(COLUMN()-1, 4), {0,1,2,3}, {"Y","N","N","N"})

<TBODY>
</TBODY>

<TBODY>
</TBODY>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top