#### quillmaster

##### New Member
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.
 146 =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)))) 3 68 189 248 112

<tbody>
</tbody>
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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### bbott

##### Well-known Member
Try something like this:

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

#### West Man

##### Well-known Member
A possible alternative: =INT(D1/60)+1

#### quillmaster

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

#### bbott

##### Well-known Member
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?

Last edited:

#### quillmaster

##### New Member
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

##### Well-known Member
When you select one of the copied cells, what does it say in the formula bar?

Replies
2
Views
169
Replies
3
Views
166
Replies
4
Views
589
Replies
3
Views
220
Replies
8
Views
349

1,191,535
Messages
5,987,157
Members
440,082
Latest member
belodelokelo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back