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