Calc formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron - 29 Mar 2006 11:16 GMT

Hi how would I write a formula to calculate the following

cell b2 contains 0.02169 cell B3 contains 0.01590 cell D2 contains 5860 and
column G contains numbers from 1 to 20845

Units Rate 1 0.02169 (first 5,860 units) 5860 £127.10
Units Rate 2 0.01590 20845 14985 £238.26
Total
£365.36

It is to calculate my gas bill where the first 5860 units cost .02169 and
the rest cost .01590
I can work it out manually but how do I write a formula that will calculate
it in excell?

Thanks Ron

Reply to this Message

Andy - 29 Mar 2006 12:03 GMT

Hi

I've put your total usage (20845) into D3 and come up with this:
=(MAX(D3-D2,0)*B3)+IF(D3>D2,D2*B2,D3*B2)

It's not pretty but it does the job!
Andy.

> Hi how would I write a formula to calculate the following
>
[quoted text clipped - 12 lines]
>
> Thanks Ron

Reply to this Message

Bob Phillips - 29 Mar 2006 12:35 GMT

=ROUND(D2*B2+MAX(0,D3-D2)*B3,2)

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> Hi how would I write a formula to calculate the following
>
[quoted text clipped - 12 lines]
>
> Thanks Ron

Reply to this Message