# Too Many If Statements-Ways to Solve a Problem

#### Gregg G

##### New Member
Yes, I know a tired topic, but I've been sitting for 3 hours trying to figure it out myself.
So, I have a column of numbers representing days starting from 1 and going to about 365 in column CJ starting with CJ65.
I have a value at E26, which represents the number of days that a team can go out and conduct surveys in the field. E26 can vary depending on resources.
I have 16 sites to consider.
Survey teams can spend “E26” number of days in the 16 sites consecutively.
What I am trying to get Excel to do is to note in a new column CK which sites are being survey on a range of days; if a site is being sampled it will show up with its site number otherwise Excel will return a 0.
For example if E26 is 2 days, from day 1-2 column CK will show 1 in each cell until CK3 when it will shift to the number 2, at CK5-6, the value will now be 3 until we get to CK31-32, which will return 16 and the all cells afterwards will return 0.
If E26 is 3 days, we’ll have day 1-3 showing 1 in column CK and CK47-48 showing 16 and all zeros afterwards.
Due to the limits on Excel for Mac’s nested IF statements, I thought I could try to get what I needed accomplished with the following, using AND or a + or an &, but that doesn’t work.
(IF(CJ65>\$E\$26,IF(CJ65>2*\$E\$26,IF(CJ65>3*\$E\$26,IF(CJ65>4*\$E\$26,IF(CJ65>5*\$E\$26,IF(CJ65>6*\$E\$26,0,6),5),4),3),2),1)) AND (IF(CJ65>7*\$E\$26,IF(CJ65>\$E\$26,IF(CJ65>9*\$E\$26,IF(CJ65>10*\$E\$26,IF(CJ65>11*\$E\$26,IF(CJ65>12*\$E\$26,0,12),11),10),9),8),7)) AND (IF(CJ65>13*\$E\$26,IF(CJ65>14*\$E\$26,IF(CJ65>15*\$E\$26,IF(CJ65>16*\$E\$26,0,16),15),14),13)))))

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
i'm not completely following the requirement
Any way you can load a sample onto a share like dropbox or onedrive
Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

So, here is a link to an Excel file:https://www.dropbox.com/s/r7dd79isgaokr78/What It Should Look Like.xlsx?dl=0. What you'll see on the left side is a version with 6 nested IF statements. I wanted to extend this to 16 nested IF statements, but Excel for Mac doesn't allow it. On the right side, you'll see a mock-up with just values in the cells for what the 16-nested version would have looked like if it worked. That is, any solution would have to look like the right hand side version. Any help you might offer would be awesome!

Does this work?

=ROUNDUP(H5/\$F\$5,0)

No, take a look at the Excel file. Roundup(H5/\$F\$5,0) does not work.

No, take a look at the Excel file. Roundup(H5/\$F\$5,0) does not work.

I have, it gives exactly the same results!

Have you tried it?

Result is on far right

Code:
``````[TABLE="width: 481"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]CURRENT SET UP WITH 3 MACHINES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cycle[/TD]
[TD]Sampling
Y/N[/TD]
[TD]Machine
sampled[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Days in Location[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]``````

It does not. N37 and all following should be zeros, that is after 16 and 16 in N35 and N36, the column should go to all zeros. Roundup does not do that.

It does not. N37 and all following should be zeros, that is after 16 and 16 in N35 and N36, the column should go to all zeros. Roundup does not do that.

It does if you take a second to tweak it!

=IF(ROUNDUP(H5/\$F\$5,0)>16,0,ROUNDUP(H5/\$F\$5,0))

So, your version does not look like what I need. See below. Thanks for trying!
G
 WHAT IT SHOULD LOOK LIKE WITH 16 MACHINES Cycle Sampling Y/N Machine sampled 1 1 1 2 1 1 3 1 2 4 1 2 5 1 3 6 1 3 7 1 4 8 1 4 9 1 5 10 1 5 11 1 6 12 1 6 13 1 7 14 1 7 15 1 8 16 1 8 17 1 9 18 1 9 19 1 10 20 1 10 21 1 11 22 1 11 23 1 12 24 1 12 25 1 13 26 1 13 27 1 14 28 1 14 29 1 15 30 1 15 31 1 16 32 1 16 33 0 0 34 0 0 35 0 0 36 0 0 37 0 0 38 0 0 39 0 0 40 0 0 41 0 0 42 0 0 43 0 0 44 0 0 45 0 0 46 0 0 47 0 0 48 0 0 49 0 0 50 0 0

<colgroup><col span="3"></colgroup><tbody>
</tbody>

So, your version does not look like what I need. See below. Thanks for trying!
G

Why? That is exactly what it returns!

Replies
3
Views
236
Replies
3
Views
444
Replies
0
Views
550
Replies
6
Views
130
Replies
1
Views
162

1,196,346
Messages
6,014,731
Members
441,843
Latest member
benji 71

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