need a formula using nos 1-443 omitting (116-124 & 153-167)

[tyrone]

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

[John James]

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.

[paul]

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
[email protected]
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
>
>

[paul]

oops mines isnt quite right.....watch this space
--
paul
[email protected]
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
> [email protected]
> 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
> >
> >

[paul]

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
[email protected]
remove nospam for email addy!



"paul" wrote:

> oops mines isnt quite right.....watch this space
> --
> paul
> [email protected]
> 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
> > [email protected]
> > 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
> > >
> > >