Calc formula

[Ron]

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

 

[Guest]

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.

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)