adding true/false validation to formula

[AHALEY]

My dilemma is this. I am working on a sheet that will calculate a Handicap for each of our bowlers. It is a value, less their average, multiplied by a percentage. I have all that worked out (easily) and it is set.

Now, I need to limit the amount of handicap given as a MAX. For Men it is 50, for Women it is 55. In addition, the handicap needs to be a whole number, rounded down to the nearest whole number.

Here is what I have currently as a formula. It is calculating for a Women, and limits the amount of handicap to 55, and it is working, however, I need to only execute this if Column E is TRUE, or if Column E is FALSE or blank, to calculate based on 50.

Settings is my tab for the conditions that may change from tournament to tournament. Settings!$B$8 contains 55 for women, and Settings!$B$7 contains 50 for men.

=ROUNDDOWN(IF(((Settings!$B$4-LaneAssign!D2)*Settings!$B$5)>Settings!$B$8,Settings!$B$8,(Settings!$B$4-LaneAssign!D2)*Settings!$B$5),0)

Any help inserting the TRUE / FALSE check for column E would be greatly appreciated.... Everything I've tried so far just returns either an Error or FALSE in the formula.

Thanks!

 

[baitmaster]

Couple of comments

Your formula appears to be trying to recreate the MIN calculation. Use Min to simplify what you are doing, e.g.
=rounddown(min((Settings!$B$4-LaneAssign!D2)*Settings!$B$5,settings!B8),0)

To further simplify, use named ranges instead of cell references. Its difficult for us to read what this formula is doing, named ranges will really help, and you will get better responses from the forum
=rounddown(min((Settings!$B$4-LaneAssign!D2)*Settings!$B$5,womensRate),0)

Now stick your IF statement around the outside
=IF([column E = true],[formula for women],[similar formula for men])

If you are struggling, its always best to write each logical step in a separate cell, then merge all your formulas into one once your answer is right: first get your T/F working, then get you male rate working, then your female, then your IF statement. Once done, embed each low-level formula into the relevant part of the IF statement. Trust me, its much easier than trying to write one massive formula in one go