Function IF

[gexxy]

Hi to all, I am italian and I write from Spain

=IF(1<=I6<=6;"1"),IF(7<=I6<=12;"2");"3")

I would:
if I6 is > = 1 and < = 6 write 1 in I7
If I6 > = 7 and > = 12 write 2 in I7
If none write 3 in I7

Why doesn't work??

If I have in A1 a date, for example monday 21 april 2011, I want that in A2 it write 1 (first day of week), if it is tuesday write 2, and so on... how make it?
It has to work with excel of other language where for example in spanish monday is lunes and so on...
Thanks

[Sean Thomas]

why not use excel function weekday?

A2 =Weekday(A1)

[oeldere]

See the attached file.

[swhitesell]

Here is one example, it does require refering to another sheet or range of cells though.

weekday-sample.xlsx

It is either this method, chaning if statements together, or writing VBA code to do this I believe.

[gexxy]

oeldere, it works, but if I don't write a date it results 6 , why !??! I want an empty cell if I don't put a date

and in the other function it have 2 IF:
if I6 is > = 1 and < = 6 write 1 in I7
If I6 > = 7 and > = 12 write 2 in I7
If none write 3 in I7

not only < 7

Thanks!

[oeldere]

Please Login or Register  to view this content.

[gexxy]

oeldere,
OK

but I speak about 2 different things:
1) FUNCTION DAY OF THE WEEK:
=DAY.WEEK(A1;2)
it works perfectly !!!, but if I don't write a date it results 6 , why !??! I want an empty cell if I don't put a date

2) FUNCTION IF:
if I6 is between 1 and 6, excel has to write 1 in I7
If I6 is between 7 and 12, excel has to write 2 in I7
If more then 12, excel has to write 3 in I7

Thanks!

[oeldere]

You can use the same solution for that.

Please Login or Register  to view this content.

[gexxy]

It give me error

[gexxy]

OK now it works!!!

Now the second problem is:

FUNCTION IF:
if I6 is between 1 and 6, excel has to write 1 in I7
If I6 is between 7 and 12, excel has to write 2 in I7
If more then 12, excel has to write 3 in I7

Thanks!

[oeldere]

I reply on your #7 (point 1))

maybe in spanish weekday is called differant.

or more likely, change the collon (,) in semi collon (;)

[gexxy]

yes the problem was to change the collon (,) in semi collon (;)

Now the second problem is:

FUNCTION IF:
if I6 is between 1 and 6, excel has to write 1 in I7
If I6 is between 7 and 12, excel has to write 2 in I7
If more then 12, excel has to write 3 in I7

Thanks!

[gexxy]

...and the third and last problem is:
If A1=1 or A2=1 or A3=1 or A4=1 or A5=1, then write 1 in A6

[oeldere]

Use Vlookup for that.

See the attached file.

[oeldere]

In addition to your 3 question.

Please Login or Register  to view this content.

I don't understand the question => in your example is A1 a date (and will therefore never be 1)

[gexxy]

Use Vlookup for that.

See the attached file.

This works perfectly!!
But I can't copy it for my 300 cells because it changes the table number
For example (in my language) this works
=CERCA.VERT(I3;formule!A1:B4;2;1)
but if I copy it in the cell below it goes out so:
=CERCA.VERT(I4;formule!A2:B5;2;1)

but it is wrong because the table is in A1:B4 !!!
It should be coorect write:
=CERCA.VERT(I4;formule!A1:B4;2;1)
but I Should have all by hand?!?
how to copy all with always the same table?
Thanks

[oeldere]

Please Login or Register  to view this content.

See the red text ($)

[gexxy]

In addition to your 3 question.

Please Login or Register  to view this content.

I don't understand the question => in your example is A1 a date (and will therefore never be 1)

Perfect!
Now it works all!
Only this has the same problem:

Please Login or Register  to view this content.

If no numbers in a1 or a2 or a3 or a4 or 15 then No number in A6
If there is number, then

Please Login or Register  to view this content.

because my formula is:

Please Login or Register  to view this content.

I need 1 if it is true, 2 if it is false, no number if no number in 1 or more of 5 cells

[oeldere]

Please Login or Register  to view this content.

Please test it, it should work.

It's an OR statement, so if everything is empty, a6 is empty also.

[gexxy]

I need 1 if it is true, 2 if it is false, no number if no number in 1 or more of 5 cells

Please Login or Register  to view this content.

[vlady]

another approach I think is

another approach is

=IF(COUNTIF(A1:A5,1)>0,1,2)
or
=if(count(A1:A5)>0,1,2)

don't know how to write it in your language.

[gexxy]

another approach I think is

another approach is

=IF(COUNTIF(A1:A5,1)>0,1,2)
or
=if(count(A1:A5)>0,1,2)

don't know how to write it in your language.

It's not correct...it leaves 2 if there isn't numbers in 5 cells
I want empty space if no numbers

[vlady]

change the 2 to ""

[oeldere]

Please Login or Register  to view this content.

[gexxy]

I solved so:
=SE(O(AI3="";AJ3="";AK3="";AL3="";AM3="");"";SE(O(AI3=1;AJ3=1;AK3=1;AL3=1;AM3=1);1;"2"))

Is it correct?
THANKS!!!

[vlady]

=if(count(A1:A5)=0,"",if(countif(A1:A5,1)=5,1,2))

[gexxy]

other question please:

I have 3 column:
In column A for example I have these numbers: 1,2,5,6
In column B I have : 3,4,7,9
In column C I have: 8,10,11,12

If I write in D1 one of the numbers in column A, then in E1 excel has to write "1"
If I write in D1 one of the numbers in column B, then in E1 excel has to write "2"
If I write in D1 one of the numbers in column C, then in E1 excel has to write "3"

The columns have to stay in another page, not in the same page of D1 and E1
Thanks!

[gexxy]

someone can help me please?

[vlady]

try

Please Login or Register  to view this content.

-array formula
you have to hit ctrl+shift+enter not just enter, you'll notice the formula will be enclosed with curly braces.