IF Statement in Excel

[planecrazy.eu]

Hey All...

I need and IF Statement that can put a "1" in the colum after the week sales value if it is the best selling CD that week, or if its not put a 0?

And does anyone know how i can print the album name of the best selling single that week in a table? like week one = , etc?

Cheers all

PS: HERE IS THE ROUGH TABLE

ARTIST | WEEK ONE | FAV | WEEK 2 | FAV
U2 | 200 | WANT 1 HERE IF IT IS
UB40 | 100 | ITS NOT THE FAVORITE SO A 0

[Sue Vêtements]

..| ...A.. | ...B.. | .C. | ...D.. | .E
--|---------------------------------------
1 | ARTIST | WEEK 1 | FAV | WEEK 2 | FAV
2 | U2.... | 200... | 1.. |....... |
3 | UB40.. | 100... | 0.. |....... |
4 | ABBA.. | 150... | 0.. |....... |



Put the following formula into C2 and drag it down to the cells below (C3:C4 in this case)

=IF(B2 = MAX(B$2:B$4),"1","0")


If you want to get clever, then set up a range to replace the B$2:B$4 part.

If you want to get really clever, you can use the xldown function to define the range, but I forget the syntax for that one.


:8



btw, I tried it and it works ;)

[vindaloo]

If you want to get clever, then set up a range to replace the B$2:B$4 part.
If you want to get really clever, you can use the xldown function to define the range, but I forget the syntax for that one.

To define a dynamic range for column B that will expand or contract depending on the data, define a Name (e.g. week1) that refers to:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

This defines a range that starts at cell B2, with a height as the number of populated cells in column B (assuming no gaps and minus 1 to allow for the column heading) and a width of 1.

Then use this named range in your formula:

=IF(B2 = MAX(WEEK1),"1","0")

xlDown is a VBA constant that is used to specify the direction for the END property of a range, e.g.

Range("$B$B").End(xldown).Row

This could be used (as part of a user-defined function) instead of COUNTA in the above formula.

Vindaloo

[planecrazy.eu]

Thanks for the feedback "ANSWER" =)

Works a treat, thanks

[Saintsman]

If you get stuck again try this lot:

http://www.mrexcel.com/board2/

if they don't know the answer nobody will.