Need to increase/decrease 2 numbers(a whole number and a %) dependent on a goal number

https://goo.gl/zPnwzz

Sorry for the poor title description but it's hard for me to explain what I am needing to do. I'm sure the solution is simple but for the life of me I can not figure it out. I have everything explained for the most part in the sample spreadsheet above. For a more detailed description see below.

This is a weight training spreadsheet and basically what I need is if you have a goal of 10 reps, and you do 12 that will increase you're new max for the next work out by a defined amount which in this case is 5 for every rep you do over the goal, so if you have a max of 300 you do 12 reps with a goal of 10 it will increase you working max to 310 for the next week of your training cycle. I need to do this same thing only with a percentage. So if 60% with a goal of 10 reps and you get 12 reps, I would like to have excel automatically increase my next week by a defined number, in the case of the percentages it would be .025% so if you get 12 reps it would take your next weeks percentages up to 65%. I also should note that if you get under the goal reps the same amount of weight/percentages will be subtracted for you next week to auto regulate training volume.

Thanks for any help and I hope this is not a super stupid question, I can normally find my way around excel fairly well but this has me stumped!

Main Program.xlsx
Here is the whole spreadsheet I use, it is the sheet labeled "Off-Season" and the dark blue boxes are the one used to enter you number of reps hit, be it over or under the goal.

Thanks for the quick responses! Please let me know if I can help with anything else.

Reps is short for Repetitions, how many times you do a movement, if there is any other terminology you don't that is foreign to you I'm happy to explain. I replied with with my current excel sheet I'm using and it goes out to 5 weeks, hopefully that will be of help.

sample.xlsx

yes, each week builds on the last week to auto-regulate the amount of weight you lift per movement, this is it in it's simplest terms.

I uploaded a new sample spreadsheet to show better the cells that are effected and try to explain it to the best of my ability in the spreadsheet.

https://youtu.be/vf0K80ibukQ?t=5m27s this video may explain it a little better or at least you can visually see what i'm going for on my spreadsheet which i'm basing of this guys. You'll see when he types a number in the 12 in the red cell on week 1, it automatically adjusted week 2s load based upon week 2's updated percentage.

Hopefully that helps clarify things a little more, should have sent that in the first place.

No because F10 and F6 are not percentages so when it calculates out it gives some huge number as a percentage.

is there anyway to use an IF statement to get the desired result?

I'm sorry I wasn't looking at the sample spreadsheet when I responded. Looking now that is exactly what I wanted to do! Thank you so much! I had figured it would probably be something super simple and I was over complicating.

It took me a while, but I think I get it now. (Actual reps - Goal reps) x 0.025.

12 - 10 = 2 2 x 0.025 = 0.050 (or 5%) 60% + (5%) = 65%

8 - 10 = -2 -2 x 0.025 = -0.050 (-5%) 60% + (-5%) = 55%

Does it look like I'm on the right track?

Originally Posted by XLPadawan

It took me a while, but I think I get it now. (Actual reps - Goal reps) x 0.025.

12 - 10 = 2 2 x 0.025 = 0.050 (or 5%) 60% + (5%) = 65%

8 - 10 = -2 -2 x 0.025 = -0.050 (-5%) 60% + (-5%) = 55%

Does it look like I'm on the right track?

Yes, that's exactly what I needed to accomplish.