2

I am trying to come up with a formula that will split three rows of numbers evenly until one cell reaches a max number and then continue to sum the same three rows but split all the new values in two and no longer give the cell that's reached it's number any more.

example:

A1=1
A2=2
A3=3
B1=5
B2=7
B3=9
C1=4
C2=8
C3=4461
D3=20
G1=1500 formula MIN(1500,SUM(A1:E3)/3)
G2=2250 and G3=2250 formula IF(G1=1500,SUM(A1:E3/2),SUM(A1:E3/3))

what I need is once G1 reaches 1500 G2 and G3 to continue to sum but now split everything that's new in two instead of by 3.

For example before the condition was true all three columns are to split these values by 3 and total correctly, once G1 hit 1500 G2 and G3 should continue to sum but split anything new by two because G1 is no longer in need to increase since it's hit the number it needed to. (4520-1500=3020/2=1510).

Is there a way to do this with another formula or to add to what I have existing? What am I doing wrong?

0

Try this formula in G2, copy down to G3

=IF($G$1=1500,(SUM($A$1:$E$3)-$G$1)/2,SUM($A$1:$E$3)/3)
  • Teylyn, thank you thank you thank you! that work and is exactly what i was looking for! – jokerfinsec Jun 5 '15 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.