Excel: Formula 1

[wayneryeven]

Hi All
Need some help with a formula:

I have 2 rows of data

In row 1/2 i have each day of the week and a number below:
Mon Tues Wed Thus Fri Sat Sun Mon Tues Wed Thurs Fri
3 3 3 3 3

In the next row what i want to do is simply look at the above row and exclusing Sat/Sun want to enter a 1 if there is a 3 on the next day. Thus for example Row 3 would have

1 1 1 1 1


As there is a 3 under Monday we put a 1 under previous Friday and so on and so forth.

The last friday doesnt have a 1 as the next Monday (not shown) does not have a 3.

So simple i hope! Unfortunately i cant remove the Sat and Sun- suffice to say we can ignore them- i.e. Friday would always look at the next Monday.

Whats the best way to achive this?

i had began thinking along the lines of:

=IF(N$3="Sat","",IF(Q$14=3,1,0))- but realised Sat/Sun would increment the cell reference too much...

All ideas and suggestions will be gladly welcomed!

Thanks guys,

 

[SkipVought]

hi,
[tt]
C1: =IF(AND(B2=3,B$1<>"Sat",B$1<>"Sun"),1,0)
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wayneryeven]

Hey Skip
Perfect, though im going to throw a spanner in the works.

At present under Friday it looks to the next day (saturday)and so on so forth for each day always looking to the next.

Whilst we enter a 1 if there is a 3 the next day less its a Sat or Sun i want it to ignore Sat/Sun now entireley.

Thus:
Friday should look to the Monday cell to check if there is a 3,

Saturday should look to the Monday but ignore it anyway as its a Sat,

Sunday look at the Monday but ignore it as its a Sun,

Monday should then look at the TUESDAY for presence of a 3,

Tuesday to the Wednesday etc etc.

I know its an added level of complexity, it may not even be possible to do?

Cheers guys,

 

[xlhelp]

wayner1980,

Just wondering....did you play around with what Skip provided and see if you can replace the Zero in the formula with something else?

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[wayneryeven]

Hey
Yes i did- i think though the issue lies with the lookup cell reference- in Skips example "B2". if its a Friday then it shouldnt look to Sat or Sun, it should look to Monday. Thus rather than B2 it would be E2 based on:

B2- Current day
C2 Satday
D2 Sunday
E2 Monday

Hope this makes sense?

 

[AnotherHiggins]

In A3, then copied to the left:
[tab][COLOR=blue white]=If(Or(A1 = "Sat", A1 = "Sun"), "", If(A1 = "Fri", If(D2 = 3, 1, ""), If(B2 = 3, 1, "")))[/color]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

BTW - I kind of guessed as to how to deal with weekends because I couldn't tell from your example what you expected to see.

That's because the preview window uses the same "width" for each character whereas the actual post has a different font.

The upshot is that in the preview pane you'd see this:
[tt]"W"
"i"
^[/tt]
Notice that both characters take up the same width.

But without a little help that would look like this in a post:
"W"
"i"[tt]
^[/tt]
Notice that the lower case i is narrower than the capital W.

You can force your example to line up properly by surrounding it with [ignore][tt][/tt][/ignore].

Example:​

If I see this in the preview pane (without the [ignore][tt][/tt][/ignore] tags):[tt]
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
3 3 3 3 3 3 3 3 3 3[/tt]

Then this is what will actually be posted:
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
3 3 3 3 3 3 3 3 3 3

Kind of a mess, right?

But if I see this in the preview pane (with the [ignore][tt][/tt][/ignore] tags):[tt]
[ignore][tt][/ignore]
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
3 3 3 3 3 3 3 3 3 3[ignore][/tt][/ignore][/tt]


Then this is what will be posted:
[tt]
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun
3 3 3 3 3 3 3 3 3 3[/tt]

If my solution in the last post doesn't work, please repost your example with the [ignore][tt][/tt][/ignore] tags.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wayneryeven]

hey higgins
Thanks for the great tip on displaying- i never knew that.

Ok getting there now- your spot on that im trying to deal with weekends. Your solution is nearer the mark- we are almost there.

Your solution has it that if its 3 on Monday Sunday gets a 1. However i need this 1 to be put to Friday. Kind of think that Sat or Sun cant have any "people" assigned under it.

Thus if Monday has 3 Friday has 1.
Sat and Sun can Never have a 1.

Thus we are always looking 1 day ahead, until friday where we have to look to the next Monday.

Got me?

I think we got this one nearly sorted.

I appreciate your time and effort so far, stirling work.

Cheers man,

 

[AnotherHiggins]

My formula should do what you're asking. It will never return anything for Sat or Sun.

It is intended to go into A3 where A1 = "Mon".

Here's the formula broken indented to (maybe) make it easier to follow the arguments of each statment:
=IF(
OR(
A1 = "Sat",
A1 = "Sun"),
"",
IF(
A1 = "Fri",
IF(
D2 = 3,
1,
""),
IF(
B2 = 3,
1,
"")))

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wayneryeven]

hey higgins
Cheers for that! Nearly nearly there now. Ok last issue...

It lies with this part of the code:

IF(D2 = 3,1,"")

If A1 is Friday D2 is the reference for the Monday cell. Now if i copy this formula across, the Sat and Sun increments this cell reference meaning that on Monday (cell D2) it is looking at G2 whereas it should be looking at E2 (Tuesday).

Confusing- yes, understandable- i hope so!

I need to copy this formula across, so in actual fact if its a Sat or Sun i want the formula to stay looking at D2, if its not a Sat or Sun then it should look to the next Day/Cell.

I know this adds a level of complexity- i appreciate any pointers you have- what you have provided thus far is much appreciated.

 

[AnotherHiggins]

Have you even tried it?

Seriously. Go ahead, drag it to the right. It won't do what you're asking about.

You'll notice that the IF(D2 = 3,1,"") part is buried fairly deep in the formula. It only ever comes into play if Row 1 in the same column as the formula = "Fri".

Otherwise it looks at the next column to the right? That's the If(B2 = 3, 1, "") part.

Gimmie a second, I'll break down the logic even more....

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Here's another look at the formula - I've actually taken the time to color-match the corresponding parentheses to make it easier to follow the logic:

=IF(
OR[red]([/red]
A1 = "Sat",
A1 = "Sun"[red])[/red],
"",
IF[green]([/green]
A1 = "Fri",
IF[blue]([/blue]
D2 = 3,
1,
""[blue])[/blue],
IF[purple]([/purple]
B2 = 3,
1,
""[purple])[/purple][green])[/green])

Or to put it in plain English:
=If A1 = "Sat" OR A1 = "Sun"
then
[tab]don't return anything
otherwhise
[tab]if A1 = "Fri"
[tab]then
[tab]|[tab]if D2 = 3
[tab]|[tab]then
[tab]|[tab][tab]1
[tab]|[tab]otherwise
[tab]|[tab][tab]don't return anything
[tab]otherwise
[tab]|[tab]if B2 = 3
[tab]|[tab]then
[tab]|[tab][tab]1
[tab]|[tab]otherwise
[tab]|[tab][tab]don't return anything

And since doesn't seem like you're going to do it, here's an example of what my formula will return. Is this what you want?
[tt]
Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon
3 3 3 3 3 3
1 1 1 1
[/tt]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[wayneryeven]

hey higgins
Apologies for the delay- weekend took priority on this one! OK i had another look at this- my fault, i did try and copy but i substituted wrong cell Reference in the formula (the actual Cell Refs arent A1, B2 etc but i broke down for simplification).

That did the job perfectly. Thanks so much for all your help, understanding and patience. Star awarded but if there were an option to award ten i would!

Thanks again man,