Formula to Change Output Depending on Value of Another Cell

so this is what i have

I have these in a hidden cell "z4"
if b4- Less than or equal to 5.4 then 0.1
if b4- Between 5.5 and 12.4 then 0.2
if b4- greater or equal to 12.5 then 0.3

=if(B4>=12.5,"0.3", if(B4>=5.5,"0.2", if(B4<=5.4,"0.1")))

So if:
cell b4 is starting value
cell c4 is new value inputed

in b5 a formula
If cell C4 is greater than 36 then (b4-(z4x(c4-36) if c4 is less than 36 then b5 stays the same as b4

=if(C4>36,B4-z4*(C4-36),if(C4<=36,B4))

What i would like to do is come up with a formula instead of the one above so when deducting the multiplies of )3,0.2.or 0.1's if the value in z4 changes during the process the make the adjustment

eg b4=12.5 z4=0.3 so if i input a score of 38 my formula would take 0.6(2x0.3) away from b4 giving new value 11.9 into b5

what i need is (help lol) to subtract the 0.3 so it becomes 12.3 then 0.2 (because it has changed category(z4) in the middle of the formula)

I hope ive made sense and thanks for any help.

Welcome to the forum!

Sorry, but your title does not comply with our forum rules. I will change it for you this time, but in future we require titles that explain your issue, please.

Not sure I have it quite right, but if not, you can edit it yourself.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

Thank geoffw283, ive tidied up my formulas. My original post the b5 should read d4 so its all on same row.

What i have so far

It is a spreadsheet for a golf league. B4 is the starting handicap for a golfer. I then enter his score into c4, depending what his score his handicap(b4) is/isnt amended and goes into d4 ready for the next score to be entered(e4) and so on.

The problem is:

In golf if your handicap is 0 to 5.4 you are category 1 and your handicap is cut 0.1 for every number above 36. 5.5-12.4 is category 2, 0.2 taken off and 12.5 is category 3 so 0.3. The issue is if a golfer has a handicap of 12.5 he is a cat 3 so has 0.3 reductions on scores over 36. If he scores 39pts he needs 3 x 0.3 somethings taken from his 12.5 handicap. As soon as his handicap drops below 12.5 he becomes a category 2 and reductions change.

b4 (12.5).........c4 (39).........d4 (12.5-0.3-0.3-0.3=11.6)

but after the first 0.3 taken from his 12.5 handicap his category changes so the second and third reduction should be 0.2 not 0.3

b4 (12.5)........c4 (39)..........d4 (12.5-0.3=12.2-0.2-0.2=11.8)

Hope this makes it easier to understand although i doubt it.
Thanks

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.