Price modeling

LGraf

I have a dataset with prices on a dozen different products that need to be grouped by country and analyzed for mean, median, mode, count and range (max/min) in order to establish a pricing model. New data will regularly be added to the underlying dataset.

All of these functions could be setup in the base sheet's Sheet Summary but since there are 5 or 6 analyses for dozens of countries this would require manual input of a couple hundred formulas. The result would also be difficult to navigate although a linked report or dashboard would make it more user friendly.

A report allows grouping and has built in summaries including average, maximum, minimum and count but it can only run one of those per column (prices) at a time and also lacks some of the needed functions.

Maybe there is a better way of setting up via a dashboard? It would be great to have dropdowns that the user can select criteria from such as specific country/s, or specific numerical parameters (means above X).

I would appreciate advice or recommendations on how to best setup and execute.

Lucas Rayala

Hi @LGraf, To set up and execute the analysis of prices grouped by country and calculate mean, median, mode, count, and range in Smartsheet, you can utilize a combination of features such as reports, formulas, and dashboards. Here's a recommended approach:

1. Create a base sheet: Set up a base sheet where you enter and maintain the dataset with prices for different products, along with the corresponding country information. Include columns for country, product, and price.
2. Define named ranges: Define named ranges for the columns containing prices and countries. This will make it easier to reference these ranges in formulas later.
3. Use formulas for analysis: In the base sheet, use formulas to calculate the desired statistical measures such as mean, median, mode, count, and range. For example, you can use functions like AVERAGE, MEDIAN, MODE, COUNT, MAX, and MIN. Apply these formulas within separate columns dedicated to each statistical measure, and reference the price column based on the selected country. This will ensure the calculations are specific to each country.
4. Create a report: Create a report in Smartsheet to display the grouped data by country. In the report, select the desired columns, including the country column and the columns with the statistical measures. Group the report by the country column to organize the data accordingly. This report will provide a summarized view of the data for each country.
5. Create a dashboard: Build a dashboard in Smartsheet that includes dropdown controls for users to select specific criteria such as country or numerical parameters. Link the dropdown controls to filter the data displayed in the report. This way, users can easily select a country or specify numerical thresholds for means above a certain value. The linked report will update dynamically based on the selected criteria, providing a user-friendly interface for analyzing the data.

By following this approach, you can automate the analysis of prices grouped by country and easily calculate various statistical measures. The combination of reports and dashboards will enhance the usability and navigation, allowing users to interactively select criteria and view the desired results.

LGraf

Hi Lucas, I have approximately 60 countries that would each need 6 formulas (AVERAGE, MEDIAN, MODE, COUNT, MAX, and MIN) so we're talking roughly 360 formulas. I can copy and paste a base IF formula and just change the "country" but even doing that accurately 354 would still take me quite a while. This would also require insertion of 60 new country rows and 6 new data columns directly in my pricing sheet, which I would prefer not to have (although they could be hidden). I could avoid that by adding the formulas into the sheet summary but then I'll need to copy over formulas one by one as opposed to six by six, slowing me down even more. Is there not a more efficient way of doing this?

LGraf

Actually, it would be 360 formulas per product, of which I currently track 11. So, up to 3,960 formulas.

There must be an automated way of doing this.