Macro of formula for making "Pace List??" / for taxes

[Salman1988]

Hey Guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
After a great help with the first post, I am back for some more hope you can help me out like the first time.

I hope I have the correct term in English. I am looking for a Macro for making a "Pace List".. its a bit like this
"http://www.google.nl/imgres?um=1&hl=nl&sa=N&biw=1366&bih=611&tbm=isch&tbnid=gaBsxYfOArhpEM:&imgrefurl=http://recruitmentmatters.nl/2009/06/16/rectificatie-prijsstijging-nuwerk-is-maar-17/&docid=I3fAlVB1qHQa4M&imgurl=http://recruitmentmatters.nl/wp-content/uploads/2009/06/image76.png&w=640&h=358&ei=Wf60T-jMEIrB0QWD5IUM&zoom=1&iact=hc&vpx=791&vpy=329&dur=1477&hovh=168&hovw=300&tx=185&ty=162&sig=102195202017236966520&page=1&tbnh=93&tbnw=167&start=0&ndsp=20&ved=1t:429,r:18,s:0,i:109"<o></o>
It can also be used for taxes etc.<o></o>

What I want is the following <o></o>
- I have 4 quarters (3 months each ofcouse )
- Every quarter I have X amount of electricity usage
- The electricity usage has to go through 4 zones each quarter separate , but having de last zone in mind.
- So if one zone is full, it will have to go though the other zone untill its full <o></o>

For example
zone 1 0-10000
zone 2 10000-50000
zone 3 50000-10 000 000
Zone 4 10 000 000 - no ending
<o></o>
B3 = 20 000 Volume of electricity quarter one
C3 = 15 000 Volume of electricity quarter two
D3 = 30 000 Volume of electricity quarter three
E3 = 60 000 Volume of electricity quarter four
<o></o>
Spreading of volume quarter one
F3 Zone 1 - 10 000 (zone 1 full)
G3 Zone 2 - 10 000
H3 Zone 3 -
I3 Zone 4 -
<o></o>
Spreading quarter two
j3 Zone 1 - Zone 1 already full
k3 Zone 2 - 15 000 ( but having a shadow volume of the fist quarter so total volume is actually 25000)
l3 Zone 3 -
m3 Zone 4 -
<o></o>
Spreading quarter three
n3 Zone 1 - Zone 1 already full
o3 Zone 2 - 25 000 (10 000 of quarter one and 15 000 quarter 2 = 25 000. So of the 30.000 only 25.000 can be filled, and 5000 has to go to zone 3)
p3 Zone 3 - 5 000 (because zone 2 is full, the rest goes to zone 3)
q3 Zone 4 -
<o></o>
Spreading quarter four
r3 Zone 1 - Zone 1 already full
s3 Zone 2 - Zone 2 already full
t3 Zone 3 - 60 000 (stays in zone three 60000+5000 = 65 000, no need of zone 4)
u3 Zone 4 -<o></o>

* This for every ID in Column A (till last row)


The stroy behind this is;
The electric tax goes over a year. Normally you have one year volume it is easy to go through the boxes. But now I want to have a yearly check with the difference that I have the volume quarterly and the volume has to close every quarter according to the zones.

So I have to check if one of the zones is already full in quarter 1, 2 or 3 before filling the next quarter.It is a lot of info, I have a document if needed. Hope you guys can help me out.<o></o>

Kind regards<o></o>

 

[Salman1988]

Hey Guys,

If you started on this one, thanks for the effort. But got the fomula's working. If you have faster way. Let met know


=MIN(B3,$Z$1)
=MIN(B3-F3,$Z$2)
=MIN(B3-SUM(F3,G3),$Z$3)
=B3-SUM(F3,G3,H3)

=IF(F3>=$Z$1,0,MIN($Z$1-F3,C3))
=IF(G3>=$Z$2,0,MIN($Z$2-G3,C3-J3))
=IF(H3>=$Z$3,0,MIN($Z$3-H3,C3-SUM(J3,K3)))
=C3-SUM(J3,K3,L3)
=IF(SUM(F3,J3)>=$Z$1,0,MIN($Z$1-SUM(F3,J3),D3))
=IF(SUM(G3,K3)>=$Z$2,0,MIN($Z$2-SUM(G3,K3),D3-N3))
=IF(SUM(H3,L3)>=$Z$3,0,MIN($Z$3-SUM(H3,L3),D3-SUM(N3,O3)))
=D3-SUM(N3,O3,P3)
=IF(SUM(F3,J3,N3)>=$Z$1,0,MIN($Z$1-SUM(F3,J3,N3),E3))
=IF(SUM(G3,K3,O3)>=$Z$2,0,MIN($Z$2-SUM(G3,K3,O3),E3-R3))
=IF(SUM(H3,L3,P3)>=$Z$3,0,MIN($Z$3-SUM(H3,L3,P3),E3-SUM(R3,S3)))
=E3-SUM(R3,S3,T3)

Kind regards