Formula to Change Output Depending on Value of Another Cell

[basketcase01]

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.

[AliGW]

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.

[basketcase01]

ok thanks, sorry.

[AliGW]

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.

[GeoffW283]

To begin, just a couple of comments on your existing formulas:

The final "if" in your Z4 formula is redundant and in fact if B4 is 5.45 it will return what is probably an unexpected value of FALSE. Also the quotes around 0.3, 0.2 and 0.1 are, at best, superfluous - the formula as you have it is returning text not numbers. Your Z4 formula can be rewritten more simply as:

=IF(B4>=12.5, 0.3, IF(B4>=5.5, 0.2, 0.1))

Similarly for your B5 formula the final "if" is redundant. It can be written as:

=IF(C4>36, B4-Z4*(C4-36), B4)

Lastly, if you wished to avoid the need for your hidden Z4 formula you could consolidate the Z4 logic into the B5 formula as follows:

=IF(C4>36, B4-IF(B4>=12.5, 0.3, IF(B4>=5.5, 0.2, 0.1))*(C4-36), B4)

That said, keeping them separated may keep the separate formulas more readily understandable.

Now to your actual question - I am having difficulty seeing the logic that you want. With reference to your example, if B4 is 12.4999 you are happy with your current algorithm which subtracts a fixed multiple (2) of 0.2 from B4 and yields B5=12.0999. Also, if B4=12.5000, just 0.0001 larger than the last example, your current algorithm will subtract a fixed multiple (2) of 0.3 from B4 and yield B5 = 11.9 which is already a discontinuity. Is this OK and what you expect? Now you want to change the algorithm to subtract first a multiple of 0.3 and then an additional multiple of 0.2. This is going to yield an even bigger discontinuity.

In your example, exactly what value do you expect for B5 when B4 = 12.5?

Can you explain in more general terms what you are trying to achieve? That might help provide enough insight to be able to offer a solution - or maybe someone else can see what I can't and jump in.

[basketcase01]

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

[GeoffW283]

I'm not a golfer but the background explanation really does help - thanks.

OK, I suspect there is a simpler way, but I have resorted to a large look up table.
It deals with handicaps from 1 to 20 and scores in the range 36-15 to 36 + 15.

It works for your example of H=12.5 and score=39, but I am not sure I have this totally right just yet. You should check carefully for various scores where handicaps are close to 12.5 and close to 5.5

Let me know where there are still problems.

See the attached workbook.

[basketcase01]

Cheers Geoff.

I went with using a lookup table for my spreadsheet and it works like a charm. Many thanks.

[GeoffW283]

Glad to help - thanks for the feedback.

[AliGW]

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