Formula help needed

[Bart]

lets say I have the number 770

I need a formula to help me break the 770 into pieces. I need 200 of the 770
to be multiplied by 0.120. I need 500 of the 770 multiplied by 0.10. And the
remainder of the 770 which is 70 to be multiplied by 0.095. So in the end
it'll look like this....

200 KWH @ .120 24.00
500 KWH @ .100 50.00
70 KWH @ .095 6.65

can this be done?

thanks

 

[Bearacade]

Hopefully this will help:

A1 is where you input your value, in this case is 770

A2 is =IF(A1>200, 200, A1)

A3 is =IF(A1<200, , IF(A1>700, 500, A1-200))

A4 is =IF(A1<700, , IF(A1>700, A1-700,))

Select A2:A4, format cells, goto Custom and put in General "KWH"

B2 is .120

B3 is .100

B4 is .095

Select B2:B4, format cells, goto Custom and put in "@" 0.000

C2 is =A2*B2

C3 is =A3*B3

C4 is =A4*B4

C5 is =SUM(C2:C4)

Select C2:C5, formate Cells, goto Number and OK

 

[Sandy Mann]

Just another way:

A1: 700

B1: =MIN(A1,200)
B2: =IF(A1>200,MIN(A1-200,500),0)
B3: =IF(A1>700,(A1-700),0)

C1: 0.12
C2: 0.1
C3: 0.095

D1: B1*C1

and copy down ro D3 using the fill handle

D4: SUM(D13)

or if you want to do it all in one cell:

=MIN(A1,200)*0.12+IF(A1>200,MIN(A1-200,500)*0.1,0)+IF(A1>700,(A1-700)*0.095,0)


--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bart]

thanks

 

[Bart]

thanks

Just another way:

A1: 700

B1: =MIN(A1,200)
B2: =IF(A1>200,MIN(A1-200,500),0)
B3: =IF(A1>700,(A1-700),0)

C1: 0.12
C2: 0.1
C3: 0.095

D1: B1*C1

and copy down ro D3 using the fill handle

D4: SUM(D13)

or if you want to do it all in one cell:

=MIN(A1,200)*0.12+IF(A1>200,MIN(A1-200,500)*0.1,0)+IF(A1>700,(A1-700)*0.095,0)


--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[joeu2004]

I need a formula to help me break the 770 into pieces. I need 200
of the 770 to be multiplied by 0.120. I need 500 of the 770 multiplied
by 0.10. And the remainder of the 770 which is 70 to be multiplied
by 0.095. So in the end it'll look like this....
200 KWH @ .120 24.00
500 KWH @ .100 50.00
70 KWH @ .095 6.65

If you want a table of that form and if you actually mean "the first
200", "the next 500" and "above 700", which is typical of utility
pricing, the following might work for you if A1 contains the total
(770):

A2: =min(200,A1)
A3: =max(0,min(500,A1-200))
A4: =max(0,A1-700)

B2:B4: @

C2: 0.120
C3: 0.100
C4: 0.095

D2: =A2*C2
Copy D2 to D34

If you prefer simply to combine the result, the following might work
for you:

=0.120*A1 - 0.020*max(0, A1-500) - 0.005*max(0, A1-700)

If that is too confusing, the following is equivalent:

=0.120*min(200,A1) + 0.100*max(0,min(500,A1-200)) + 0.095*max(0,A1-700)