if anyone can help me with the following formula i would be very gratefull

i'm doing a spreadsheet for a sheep draw, which involves 6 stock agents, who
have different numbers each week (eg as below)

Agent Number of Sheep No of Pens Req Pen
Numbers
1 2,000 49
1 to 49
2 3.000 74
50 to 123
3 3,000 74
this is the problem
4 3,000 74
i need to to omit
5 3,000 74
(116-124)
6 3,000 74

Total Yarding 17,000 ie 41 sheep per pen Total Pens 419

i need help with the formula for pen allocations. Pens 1-443 omitting
(116-124 & 153-167).

Thanks

Hi tyrone,

Understanding the question was the hardest bit. Here's your answer,

1. Place your data in the range A2:C6
2. In D2 enter
=SUM($C$2:C2)
3. In E2 enter
=IF(D2<116,D2,IF(D2<153,D2+9,D2+24))
4. Copy the formulae in D2 and E2 down to the rows below

It'll help if your questions are a bit clearer.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531390

sorta the same but asuming less!
row 1 headers
col a for agents
col b for number sheep
col c pens required
col d total pens required
col e results

c2 enter formula =ROUNDUP(B2/41,0) copy down
cell d2=c2,d3=d2+c3,copy down
cell e2==IF(B2="","","Pens 1-
"&IF(D2<=115,D2,IF(AND(D2=116,D2<=143),D2+9,D2+24 )))
cell e3=IF(B3="","","Pens
"&D2+1&"-"&IF(D3<=115,D3,IF(AND(D3=116,D3<=143),D3+9,D3+24 ))) copy down

if you want tell me your email address and i can send my sheet




--
paul

remove nospam for email addy!



"John James" wrote:


Hi tyrone,

Understanding the question was the hardest bit. Here's your answer,

1. Place your data in the range A2:C6
2. In D2 enter
=SUM($C$2:C2)
3. In E2 enter
=IF(D2<116,D2,IF(D2<153,D2+9,D2+24))
4. Copy the formulae in D2 and E2 down to the rows below

It'll help if your questions are a bit clearer.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531390

oops mines isnt quite right.....watch this space
--
paul

remove nospam for email addy!



"paul" wrote:

sorta the same but asuming less!
row 1 headers
col a for agents
col b for number sheep
col c pens required
col d total pens required
col e results

c2 enter formula =ROUNDUP(B2/41,0) copy down
cell d2=c2,d3=d2+c3,copy down
cell e2==IF(B2="","","Pens 1-
"&IF(D2<=115,D2,IF(AND(D2=116,D2<=143),D2+9,D2+24 )))
cell e3=IF(B3="","","Pens
"&D2+1&"-"&IF(D3<=115,D3,IF(AND(D3=116,D3<=143),D3+9,D3+24 ))) copy down

if you want tell me your email address and i can send my sheet




--
paul

remove nospam for email addy!



"John James" wrote:


Hi tyrone,

Understanding the question was the hardest bit. Here's your answer,

1. Place your data in the range A2:C6
2. In D2 enter
=SUM($C$2:C2)
3. In E2 enter
=IF(D2<116,D2,IF(D2<153,D2+9,D2+24))
4. Copy the formulae in D2 and E2 down to the rows below

It'll help if your questions are a bit clearer.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531390

sorta the same but asuming less!
row 1 headers
col a for agents
col b for number sheep
col c pens required
col d total pens required
col e altered pen nos
col f nice text output

c2 enter formula =ROUNDUP(B2/41,0) copy down
cell d2=c2,d3=d2+c3,copy down
cell e2=IF(D2<116,D2,IF(D2<153,D2+9,D2+24)),thanks to John James copy down
cell f2=IF(B2="","","Pens 1- "&E2)
cell f3=IF(B3="","","Pens "&E2+1&"-"&E3) copy down

if you want tell me your email address and i can send my sheet
--
paul

remove nospam for email addy!



"paul" wrote:

oops mines isnt quite right.....watch this space
--
paul

remove nospam for email addy!



"paul" wrote:

sorta the same but asuming less!
row 1 headers
col a for agents
col b for number sheep
col c pens required
col d total pens required
col e results

c2 enter formula =ROUNDUP(B2/41,0) copy down
cell d2=c2,d3=d2+c3,copy down
cell e2==IF(B2="","","Pens 1-
"&IF(D2<=115,D2,IF(AND(D2=116,D2<=143),D2+9,D2+24 )))
cell e3=IF(B3="","","Pens
"&D2+1&"-"&IF(D3<=115,D3,IF(AND(D3=116,D3<=143),D3+9,D3+24 ))) copy down

if you want tell me your email address and i can send my sheet




--
paul

remove nospam for email addy!



"John James" wrote:


Hi tyrone,

Understanding the question was the hardest bit. Here's your answer,

1. Place your data in the range A2:C6
2. In D2 enter
=SUM($C$2:C2)
3. In E2 enter
=IF(D2<116,D2,IF(D2<153,D2+9,D2+24))
4. Copy the formulae in D2 and E2 down to the rows below

It'll help if your questions are a bit clearer.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531390