IF Function - Help Required!

[atkinsd91]

Hello,

Am new here, very first post in fact, so hello to you all!

I was hoping someone might be able to assist me with the formula I've attached... basically, why it is that it's returning 'FALSE'?

Just by reading the formula you can hopefully tell what I'm trying to do, but will say anyway.

Basically I'm going to put 'A's in cells in row 2 for particular dates... so therefore if there is an 'A' in there I want it to check whether that 'A' lands on a Saturday or a Sunday. If it's a Saturday then the 'fixed' value should be placed in the cell before. If it is a Sunday then it should be placed 2 cells before (to also skip Saturday of course). For those who work in finance I'm sure you can see exactly what I'm trying to do and why I'm doing it! (Also if you do, please suggest a better way to accomplish what I'm trying if you know of one!)

Anyway, all help is much appreciated!

Thanks,
Daniel

IF Function.JPG

[Sam Capricci]

Looks like your saying that if M2 = A and next criteria is if M1 = saturday L6 = J6 and I believe right here it stops working.
Maybe you want something like =IF(AND(M2="A",M1="SATURDAY"),J6,IF(AND(M2="A",M1="SUNDAY"),L6,J6)) Though not sure if this covers your issues w/o a sheet to work with. Pictures can be tough to work with.

[atkinsd91]

Thanks Sambo, but doesn't fully work as it's just saying to put the value into M6, whereas if it is A + Saturday/Sunday then I wish for it to put the value 1 or 2 cells to the left respectively. When I try to alter your one, as in bold, it returns back with false again.. is it that the formula doesn't work because it's trying to place a number in a cell where the formula doesn't exist? If that makes any sense at all..?

=IF(AND(M2="A",M1="SATURDAY"),L6=J$6$

[kspeese]

It may be easier if you include a helper cell somewhere. For example, do something like in F1 that figures out what corresponding day is "A." In Cell F1 for example =LOOKUP("A",M2:N2,M1:N1) (you can make the font white and no one will see it)

It will come back with Saturday or Sunday, then the other formulas you enter in K & L reference the day listed in F1.

So, in L6 for example, =IF(F1="Saturday", J6,"")
In K6 =IF(F1="Sunday",J6,"")

Without seeing a sample excel sheet and seeing what you want each cell to do, I can't be of much more help.

[atkinsd91]

Thanks kspeese! I'll have to review your reply in more detail tomorrow though.

However.. can no one see the link I put at the very bottom of my original post? It shows the formula I used and the data used with it?

[FDibbins]

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

Having said that, if you are working with dates and day names, if you use =weekday() to reference the date, this will give you a calue of 1-7, depending on the format you select (weekday(ref,2) will give you Sat=6. You could then perhaps incorporate <6 to give you weekdays?

[kspeese]

Thanks kspeese! I'll have to review your reply in more detail tomorrow though.

However.. can no one see the link I put at the very bottom of my original post? It shows the formula I used and the data used with it?

We can see it, but it's not entirely clear what you want to do. Your formula looks like one cell is looking to see if conditions are met elsewhere, then trying to alter another cell. Someone could do that in VBA, but I don't think it's possible in a formula.

"L6=J$6$" is a criteria, it's not able to make cell L6 the value of J6. In your example "=IF(AND(M2="A",M1="SATURDAY"),L6=J$6$" it basically met the conditions of M1 and M2, so it then is checking to see if the values in L6 and J6 are the same. because they weren't, you got "FALSE."