Help Needed: Working Out Price Bands..?

**NJ106** · 02-08-2007, 07:17 AM

Firstly, "hello" to everyone.. I've tried googling this etc but can't find any answers so here I am..

Ok, so here's the deal.. Trying to set up a database for the Gas Bill.. Say we get charged £1 per unit for the first 0-100units, then we'll be charged £0.75 for every unit after that (discounted price)

Any help on the formula..? I've tried countless methods but just can't get anywhere

Thanks

**Carim** · 02-08-2007, 07:47 AM

Hi,

See attached ...

**NJ106** · 02-08-2007, 08:13 AM

wow, I love this forum!

thanks for the help, life saver, pretty simple when you think about it haha..

I'll be using this forum a LOT!

thanks again

**Carim** · 02-08-2007, 08:15 AM

Glad it fixed your problem ...

Thanks for the feedback

**NJ106** · 02-08-2007, 08:47 AM

Sorry if i'm being cheeky now..

But is there also a formula i could use to find out how much I've been charged per unit for the two price bands?

So, say I receive an invoice saying "Units used = 200" & "Cost = £150"
and i know the price bands stand at 0-100units and 101+units

(simple maths shows that the costs would be 0-100 = 1 and 101+ = 0.5

but is there a way in typing in the final value and getting all the info back?

Cheers

**Carim** · 02-08-2007, 08:53 AM

Do you mean dividing price by quantity to get average unit cost ...?

**NJ106** · 02-08-2007, 08:57 AM

Not entirely sure..

A cell where I will manually enter the invoice charge (say £150)
A cell where I will manually enter the number of units used (say 200)
Two cells where I'll enter unit band 1 (0-100) and unit band 2 (100+)

So then all i need to do, is type my Invoice Charge (£150) and it will automatically tell me the cost per unit for 0-100 and the cost per unit 100+?

If this is too deep don't worry about it, cheers!

**Carim** · 02-08-2007, 09:01 AM

I see what you mean ...
But there are two unknowns ... both prices ... unless there is a known relationship between the two prices say second price is 75% of first price ...