FUN WITH CHATGPT- my first experience using artificial intelligence (that I know of).

FUN WITH CHATGPT- my first experience using artificial intelligence (that I know of).

As a supplier quality engineer, I spent several months developing a risk assessment of suppliers for my current position which began in March 2022. I created metrics for various elements of supplier behavior with a scale to apply.

1. average per month for a rolling 12 month period which gets rated as
>.71 or greater per incident per month = 3
.70 to .30 per month = 2
< .29 to .01 per month = 1
0 per month = 0

2. highest number within that 12 month period
4 & up = 3
3 to 4 =2
1 to 2 = 1
0 = 0

3. Potential to shut down production
3 = high risk
2 = medium risk
1 = low risk
0 = no risk

4. PPAP Process Followed
PPAP correctly followed = 0
PPAPs not followed = 1
Per SQE

5. Corrective Actions
100% on time and effective = 0
Late 8Ds = 1

6. Finally, feedback from the Buyers for their respective suppliers for Responsiveness (RMAs & Quotes) Communications.

Admittedly, some metrics are subjective. With each element was given scoring values, the scoring values are added up and the higher the resulting number, the higher the risk the supplier is for my company’s production. This meant instead of dealing with all 116 suppliers all the time, my focus can be on the top 30. Nine are HIGH risk and the remaining 21 are medium risk.

I have three spreadsheets that work on this
I have a rejection workbook which feeds information to the risk assessment workbook (the one we are working on for this formula). Then the risk assessment feeds information to the incoming inspection workbook to inform the incoming inspection workbook which is used to inform receiving staff which suppliers’ parts need inspection. Suppliers not at medium or high risk are “dock to stock.” The inspection is done on a statistical level with higher risk suppliers getting MORE parts inspected at the dock and medium risk fewer per quantity received.

Anyway, the two columns for “average per month for a rolling 12 month period” and “highest number within that 12 month period” get populated by an easy formula. The Average is of course a value divided by 12. The “highest” is a formula to pull in the highest number (=max()).

But the Rating Column for each was, until recently, manually entered. I.e., I had to scroll down the column, looke at the values in columns E (average) and G (max) and enter the appropriate rating number in columns D and F respectively based on the scale noted above. How to automate this?

I am a bit above average with spreadsheet formulas and know how to do Look-up and “If-Then” on a one factor control number. But this was going to be far more difficult with multiple elements. For Column E (average per month for 12 months), as an example, the parameters are:
>.71 or greater per incident per month = 3
.70 to .30 per month = 2
< .29 to .01 per month = 1
0 per month = 0

I tossed the question out to my Facebook friends and got a few suggestions.

From Jeff:
Try: =IF(E1=0, 0, IF(AND(E1<=0.29, E1>=0.01), 1, IF(AND(E1<=0.69, E1>=0.30), 2, IF(E1>0.69, 3, “Invalid Input”))))

From Seth:
=IF(E1=0, 0, IF(AND(E1>=0.01, E1<=0.29), 1, IF(AND(E1>=0.30, E1<=0.69), 2, IF(E1>0.69, 3, “”))))

From Ed:
=IF(A26>0.71,3,IF(A26>0.3,2,IF(A26>=0.01,1,0))).

Some seem close, but no success.

Then Christian suggested “Copy paste your question into ChatGPT.” So I pasted the parameters into ChatGPT and all I got was a regurgitation from it something like “it looks like you are trying to ..”
That’s what I said! Christian responded that you have to TELL it you want a formula. He said he uses it often on his economics models (he is an economist).

So I typed:

how can i turn this information into an excel formula?
if E18>.70, 3
if E18<.71 and >.28, 2
if E18<.29 and >.01, 1
if E18 = 0,0

The resosponse was immediate:

=IF(E18>0.7, 3, IF(AND(E18<=0.7, E18>0.28), 2, IF(AND(E18<=0.28, E18>0.01), 1, IF(E18=0, 0))))

I copied it to Notepad, modified the cell numbers for my particular spreadsheet columns D & E and plugged it into the relevant cell- And it worked! So I dragged the formula down the column, then revised the formula on Notepad for columns F & G. Now the only two manual entries I need to do is put the supplier rejections into the Rejections sheet each week and monthly move the rolling twelve month window over one cell.