nested if function, please help!!!!

[quillmaster]

Hi,
i have some data of no of calls and i am trying to seggreage them based on the number of minutes spent on them, i am using nested if condition but it is giving me problems

can somebody help.[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]146[/TD]
[TD]=IF(AND(D1)<59,1,IF(AND((D1)>59,(D1)<119),2,IF(AND((D1)>119,(D1)<179),3,IF(AND((D1)>179,(D1)<240),4,5))))[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]68[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]189[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]248[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
the above formula is giving me 3 in every coloum....can anybody correct this.

i want to categories the seconds on the left most coloum to be categorised into right most coloum of i minute call ( between 0-59 sec), 2 minute call (60 to 119 sec), 3 minute calls ( 120 to179 sec) etc.

 

[bbott]

Try something like this:

=MATCH(D1,{0,60,120,180,240},1)

 

[West Man]

A possible alternative: =INT(D1/60)+1

 

[quillmaster]

nope, it still gives me 3... i dont know why....

 

[bbott]

nope, it still gives me 3... i dont know why....

You're getting 3 for all of the lines or just the first? Both my solution and West Man's solution work on my machine.

Is your worksheet calculation set to automatic or manual?

 

[quillmaster]

it was set to automatic but then i switched to manual but still it is implementing the answer of the first cell....
what i am doing is, since it is a large dataset, entering the formula in the first cell and then pressing ctrl+d after selecting the entire coloum.... i think it is more of a formatting issue..... can you please help me what to do here i am using excel 2013...

 

[bbott]

When you select one of the copied cells, what does it say in the formula bar?