Multiple conditions in Excel - Stack Overflow

admin管理员组

文章数量:1122832

I'm working on an Excel project and I need to assign scores to athletes based on their time and gender. I don't have a helper table, and I want to do this using a formula that takes both gender and time into account.

Problem Description:

I have a table with two columns: Time: The time it took for the athlete to complete the event (in seconds). Gender: The gender of the athlete (Male or Female).

Scoring Rules:

For Male athletes:

If the time is <= 23.7224, they get 10 points.
If the time is > 23.7224 but <= 24.6348, they get 9 points.
If the time is > 24.6348 but <= 25.5472, they get 8 points.
If the time is > 25.5472 but <= 26.4596, they get 7 points.

And so on with decreasing points (6, 5, 4, 3, 2, 1) as the time increases.

For Female athletes:

The rules are similar but with different time thresholds:

If the time is <= 23.868, they get 10 points.
If the time is > 23.868 but <= 24.786, they get 9 points.

And so on.

Example:

For a Male athlete with a time of 24.5 seconds, he should receive 8 points, as his time falls between the thresholds for 8 points (24.6348 to 25.5472). For a Female athlete with a time of 25.5 seconds, she should receive 7 points, as her time falls between the thresholds for 7 points (26.4596 to 27.372).

What I Tried:

I am trying to use IF, but I’m not sure how to structure the formula correctly since the time ranges are different based on gender.

Here is an example of what I've tried, but it doesn’t seem to work correctly:

=IF(D2="Male", 
    IF(C2<=23.7224, 10, 
        IF(C2<=24.6348, 9, 
            IF(C2<=25.5472, 8, 
                IF(C2<=26.4596, 7, 0)))), 
    IF(D2="Female", 
        IF(C2<=23.868, 10, 
            IF(C2<=24.786, 9, 
                IF(C2<=25.704, 8, 0)))))

However, this gives incorrect results.

I'm working on an Excel project and I need to assign scores to athletes based on their time and gender. I don't have a helper table, and I want to do this using a formula that takes both gender and time into account.

Problem Description:

I have a table with two columns: Time: The time it took for the athlete to complete the event (in seconds). Gender: The gender of the athlete (Male or Female).

Scoring Rules:

For Male athletes:

If the time is <= 23.7224, they get 10 points.
If the time is > 23.7224 but <= 24.6348, they get 9 points.
If the time is > 24.6348 but <= 25.5472, they get 8 points.
If the time is > 25.5472 but <= 26.4596, they get 7 points.

And so on with decreasing points (6, 5, 4, 3, 2, 1) as the time increases.

For Female athletes:

The rules are similar but with different time thresholds:

If the time is <= 23.868, they get 10 points.
If the time is > 23.868 but <= 24.786, they get 9 points.

And so on.

Example:

For a Male athlete with a time of 24.5 seconds, he should receive 8 points, as his time falls between the thresholds for 8 points (24.6348 to 25.5472). For a Female athlete with a time of 25.5 seconds, she should receive 7 points, as her time falls between the thresholds for 7 points (26.4596 to 27.372).

What I Tried:

I am trying to use IF, but I’m not sure how to structure the formula correctly since the time ranges are different based on gender.

Here is an example of what I've tried, but it doesn’t seem to work correctly:

=IF(D2="Male", 
    IF(C2<=23.7224, 10, 
        IF(C2<=24.6348, 9, 
            IF(C2<=25.5472, 8, 
                IF(C2<=26.4596, 7, 0)))), 
    IF(D2="Female", 
        IF(C2<=23.868, 10, 
            IF(C2<=24.786, 9, 
                IF(C2<=25.704, 8, 0)))))

However, this gives incorrect results.

• excel
• excel-formula
• multiple-conditions

Share Improve this question Follow edited Nov 22, 2024 at 9:32 MLlamas xWF 4533 bronze badges asked Nov 21, 2024 at 19:50 AdidekAdidek 311 bronze badge 2

• 1 Create a lookup table with the Min time in one column and the points in the next then use a lookup formula. – Scott Craner Commented Nov 21, 2024 at 20:17
• "I don't have a helper table" — So, why not create one? (or, better, two - one for each set of criteria) It would certainly make things easier if you ever need to change the scores/times – Chronocidal Commented Nov 26, 2024 at 9:41

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

=11-XMATCH(C2,IF(D2="Male",SEQUENCE(10,,23.7224,0.9124),SEQUENCE(10,,23.868,0.918)),1)

It appears your steps between 10,9,8, etc. are linear (if not, provide more data). Therefore I used SEQUENCE to get the values (one for Male and Female). If we match against that, we can subtract that from 11 to get your points.

I won't solve your formula, but I might learn you how to investigate what's wrong with your formula. You can do this using formula evaluation.
You can find this in the "Formulas" ribbon, "Formula auditing" menu:

It looks as follows:

Using the "Evaluate", "Step in" and "Step out" buttons, you might investigate step by step where your formula goes wrong.

本文标签： Multiple conditions in ExcelStack Overflow