Too Many If Statements-Ways to Solve a Problem

G

Gregg G

New Member
Joined
Jun 6, 2015
Messages
6
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.
Any advice?
The formula I tried was this:
(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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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.
 
Upvote 0
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!
 
Upvote 0
Gregg G said:
No, take a look at the Excel file. Roundup(H5/$F$5,0) does not work.
Click to expand...

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]
 
Upvote 0
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.
 
Upvote 0
Gregg G said:
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.
Click to expand...

It does if you take a second to tweak it!

=IF(ROUNDUP(H5/$F$5,0)>16,0,ROUNDUP(H5/$F$5,0))
 
Upvote 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
CycleSampling
Y/N		Machine
sampled
111
211
312
412
513
613
714
814
915
1015
1116
1216
1317
1417
1518
1618
1719
1819
19110
20110
21111
22111
23112
24112
25113
26113
27114
28114
29115
30115
31116
32116
3300
3400
3500
3600
3700
3800
3900
4000
4100
4200
4300
4400
4500
4600
4700
4800
4900
5000

<colgroup><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Repeat VBA for each column
Replies
3
Views
214
Alex Blakenburg
A
P
  • Question
Excel Line graph - Y axis issues - seems to be summing the data
Replies
2
Views
151
panesai1
P
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
227
mst3kr
M
P
  • Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
194
POLACOLOCO
P
A
  • Solved
Duplicate rows based on call value and and extra column
2
Replies
12
Views
250
skillilea
S

Forum statistics

Threads
1,214,641
Messages
6,120,691
Members
448,978
Latest member
rrauni

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top