material per part always wanting to charge for full material usage formula

[Glenjamin]

Hi all,

Hoping someone can help with this.

I have

Job quantity - Cell B5
Material cost - Cell D3
Parts per material quantity - Cell D4

Lets say a sheet of material at £100 can manufacture 10 parts, my job quantity is 1-10. I still want to charge for the full sheet of material even at 1 part. Same if the job quantity is 11-20 i'd still want to charge for 2 sheets of material (£200) and so on.

How can this be achieved?

Hopefully this is a good enough explanation of what i need.

Thanks for any help,

Glen

 

[Fluff]

How about

=ROUNDUP(B5/D4,0)*D3

 

[kweaver]

Something like this?

Book6
	A	B	C	D
3			Material Cost:	100
4			Parts/Qty:	10
5	Joby Qty:	11	Charge:	200
Sheet18

Cell Formulas
Range	Formula
D5	D5	=ROUNDUP(B5/D4,0)*D3

 

[Glenjamin]

How about

=ROUNDUP(B5/D4,0)*D3

Hate it when its that easy

had this rounddown formula on the go already

=SUM((D11*Lists!$B$20),(F11),IF($B$5<=G11,0,(F11*ROUNDDOWN($B$5/G11,0))))

Think i need to walk away from this sheet for a while!!

Cheers

Glen

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[kweaver]

Hate it when its that easy

had this rounddown formula on the go already

=SUM((D11*Lists!$B$20),(F11),IF($B$5<=G11,0,(F11*ROUNDDOWN($B$5/G11,0))))

Think i need to walk away from this sheet for a while!!

Cheers

Glen

Stepping back can often help. Also, another set of eyes helps as well. That's why everyone is here!