IF Statement in Excel
Thread Starter
Join Date: Jun 2006
Location: BRISTOL!
Age: 39
Posts: 526
Likes: 0
Received 0 Likes
on
0 Posts
IF Statement in Excel
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
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
Join Date: Jan 2007
Location: Mostly in my own imagination
Posts: 481
Received 361 Likes
on
168 Posts
..| ...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.
btw, I tried it and it works
--|---------------------------------------
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.
btw, I tried it and it works
Join Date: Sep 2002
Location: UK
Posts: 34
Likes: 0
Received 0 Likes
on
0 Posts
=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