PromptBase
🤖 GPT
The promts sets the right gudaince and setting to help you make awsome and great excel function that everybood can use!
...more
$3.99
After purchasing, you will gain access to the prompt file which you can use with GPT or the app builder. You'll receive 20 free generation credits with this purchase. By purchasing this prompt, you agree to our terms of service.
Over 1 month ago
Prompt Details
Model
Chat - GPT-3.5 (gpt-3.5-turbo)
Token size
140 ($0.00030 / call)
Example input
Column A contains product name, column B contains price, column C contains sales volum, column D contains cost, column E contains group category, please sett up formulas that gives med the 10 lowest earners, and the top 10 earners, the one that that have the biggest deviation in each group category, and please feel free to sett up other useful formulas to analysis the dataset
Example output
Sure, I can help you with that. Here are some formulas you can use to analyze your dataset:
To find the 10 lowest earners:
In cell F2, enter the formula =SMALL(B2:B100,1)
Copy the formula down to cells F3:F11
This will give you the 10 lowest earners in column B.
To find the top 10 earners:
In cell G2, enter the formula =LARGE(B2:B100,1)
Copy the formula down to cells G3:G11
This will give you the top 10 earners in column B.
To find the product with the biggest deviation in each group category:
In cell H2, enter the formula =INDEX(A2:A100,MATCH(MAX(IF(E2:E100="Group1",D2:D100-B2:B100)),D2:D100-B2:B100,0)))
Copy the formula down to cells H3:H4 (assuming you have two group categories)
This will give you the product with the biggest deviation in each group category. You can change the "Group1" in the formula to match the name of each group category.
Other useful formulas to analyze the dataset:
Total revenue: In cell I2, enter the formula =SUM(B2:B100*C2:C100)
Total cost: In cell J2, enter the formula =SUM(D2:D100*C2:C100)
Total profit: In cell K2, enter the formula =SUM(B2:B100C2:C100)-SUM(D2:D100C2:C100)
These formulas will give you the total revenue, cost, and profit for the entire dataset. You can also modify them to calculate the totals for each group category by using the IF function to filter by group category.
I hope this helps! Let me know if you have any questions.