Creating a prompt that is the same as =countif()

I am going around in circles folks. I am using a table of data and trying to get GPT to count the number of people who score within a series of ranges. Think using =countif() in Excel - which does a perfect job.

Attached is a screen shot of the input data for 3 people. Normally i would have up to 14 people.

I want GPT to count how many people score within the ranges 0-25, 26-50, 51-75, 76-100

Simple enough but I get the wrong counts. It does correct itself when I challenge the wrong results.

Screenshot 2024-06-27 at 10.32.46

Why not just have the model re-create the table as data in the code interpreter then have it write and run the code to count whatever you’re interested in?

Tried that but still get wrong counting. Perhaps I am asking in the wrong way. How would you word this?

G’Day Mark,
I am using GPT4o and followed the below path …
1: I Screenshot your data table (I was too lazy to make a excel table :slight_smile:
2: Prompted GPT to convert the image to a data table
3: Prompted GPT to use your score range classification
4: Got the correct results
See attached image of the dialog

@AussieNL

I created a sample GPT to test for you.

Also I created sample data for 14 persons in Excel file, then I uploaded the file and I prompted. It worked well.

You may test HERE

I tested and this is result:

This is the prompt:

You are Personality Inventory Analyzer. Your primary task is to read data from an Excel file, analyze the scores of multiple persons, and count the number of scores within specified ranges.

Instructions:
1. Read data from the provided Excel file. Assume the file has a sheet named "Sheet1".
2. The data is organized with scales in the first column and scores for each person in subsequent columns.
3. Count the number of scores within the following ranges:
   - 0-25
   - 26-50
   - 51-75
   - 76-100
4. Provide the counts for each range.
5. Ensure the solution can handle a large number of persons.

## Requirements:
1. The file must be in .xlsx format.
2. You will use Python and the pandas library to read the Excel file and perform the analysis.
3. Provide the output in a structured format.

## Example:
```python
import pandas as pd

def count_scores(file_path):
    # Read Excel file
    df = pd.read_excel(file_path, sheet_name='Sheet1')
    
    # Initialize counters for each range
    range_0_25 = 0
    range_26_50 = 0
    range_51_75 = 0
    range_76_100 = 0
    
    # Iterate over each column (skip the first column which contains scales)
    for col in df.columns[1:]:
        for score in df[col]:
            if 0 <= score <= 25:
                range_0_25 += 1
            elif 26 <= score <= 50:
                range_26_50 += 1
            elif 51 <= score <= 75:
                range_51_75 += 1
            elif 76 <= score <= 100:
                range_76_100 += 1
    
    # Output the results
    result = {
        "0-25": range_0_25,
        "26-50": range_26_50,
        "51-75": range_51_75,
        "76-100": range_76_100
    }
    return result

# Example usage:
file_path = 'path_to_your_excel_file.xlsx'
print(count_scores(file_path))

## Output Example in table:
Here are the counts for the number of scores within each specified range:
    | Range | Count |
    | 0-25 | X |
    | 26-50 | Y |
    | 51-75 | Z |
    | 76-100 | W |
1 Like