An accumulating column with a maximum

[lloydbee]

After searching for days on end through old threads and playing the trial and error game here I am with my first post and feeling guilty like all newbies should. I imagine this would be fairly straight forward but it has escaped me so far.
Lets see if I can make my question clearer than mud....
Windows7/Office2010


I have a column 'D7:D59' containing (accounting) that accumulates at 5%
=D7*1.05
I also have column 'E7:E59' accumulating (number) which is a division of the accounting column but would like this to stop once it reaches a chosen or imput figure (E3)
=D8/100

Example :
a number is entered into 'E3" say 200
'D7' is $5000, 'E7' is (5% times 100) and would equal 50
'D8' is $5250 (with 5% gain), 'E8' would equal 53 (decimal point rounding up)
and so on until approximately 'E36' where I would like it to stop or max out at the input agreed value that was 200... At which point also that the 5% gains in 'D' would only accumulate at 5% of 'E3'(200) multiplied by 100

Any help would be much appreciated...

 

[maneesh2312]

Hi,

Assuming that D7 has value 5000, E7 has formula "=D7/100" and E3 has value 200, I wrote the below formula in

D8 as "=IF(D7>($E$3*100),"",(D7+(D7*5%)))"
E8 as "=IF(E7>$E$3,"",D8/100)"

and just dragged in to any number of rows you desire.

Hope it help you

Thanks
Maneesh

 

[lloydbee]

Hi,
Assuming that D7 has value 5000, E7 has formula "=D7/100" and E3 has value 200, I wrote the below formula in

D8 as "=IF(D7>($E$3*100),"",(D7+(D7*5%)))"
E8 as "=IF(E7>$E$3,"",D8/100)"
Thanks
Maneesh

Thanks for the reply Maneesh,
it's close... The 'D' column works ok until 'E' reaches 200 then all fields are blank in the remaining 'E' column...

 

[energman58]

I think it should be

D8 as "=IF(D7>($E$3*100),D7+($E$3*5),(D7+(D7*5%)))"

E8 as "=IF(E7>=$E$3,$E$3,D8/100)"


If I understood the question properly

 

[lloydbee]

Thanks energman,
your 'D' column seems to have worked but the 'E' column only shows what is entered into 'E3'... rather than accumulating untill it reaches that number.

Maneesh's ""=IF(E7>$E$3,"",D8/100)"" seems close but stops showing (stops accumulating and displaying once E3's total is reached) at the entered amount as does yours but looks like i need a combination of them both.

Many thanks to you both for your help, patience and knowledge.

 

[lloydbee]

Still trying to get this sorted...
energman your one limits the accumulation to the entered E3 but just fails to accumulate first at 'lower than' or 'up to' the entered E3 figure

 

[energman58]

How about

=min(D8/100,$E$3)

?

 

[lloydbee]

How about

=min(D8/100,$E$3)

?

Beautiful, works perfectly.
Nice code so simple looking, your time and reply is very much appreciated.
Hoping oneday I can return the favour to someone else...
Thanks.