Using excel to calculate the Modal Average

[original PM]

And you know what it appears it is really not easy.

So I have a load of cost centres and I want to find out the normal charge for service x

So I have put together a pivot and yes it will calculate the median for each cost centre

But can it tell me which value occurs the most for each cost centre???

No it would appear not....

Can anyone help?

Thanks

Is it possible to do in SQL also?

[NotAllThere]

MODE.SNGL or MODE.MULT don't help you?

[gnarledcontractor]

SQL Solution:

First create a CTE (common table expression)
You can use the PERCENTILE_DISC function (if you are using SQL 2012 and newer) - https://docs.microsoft.com/en-us/sql...ql-server-2017 - to obtain the median value by group.
If you want the mode you should use the ROW_NUMBER window function PARTITIONING BY your Group(s) and ordering by the COUNT of occurrences. This will calculate the number of occurrences of the value From this you can query the CTE where ROW_NUMBER = 1 which will
give you the value that occurs most for each group (in your case cost centre).

[original PM]

Thanks all - I continued googling after posting this, had to sign up to an online help source

and it can be done using this formula

+MODE(IF(Sheet1!$A:$A=E2,Sheet1!$B:$B))

would be easier if it was an option on the pivot table instead of only providing the mean average...

cheers for the replies though - some rep on the way!

also i will look at mode.mult

[gnarledcontractor]

Since I had some time.. Here's the sql solution with some admittedly rough test data.

DECLARE @CostCentreOne UNIQUEIDENTIFIER = NEWID();
DECLARE @CostCentreTwo UNIQUEIDENTIFIER = NEWID();
DECLARE @CostCentreThree UNIQUEIDENTIFIER = NEWID();


CREATE TABLE #CostCentreValues
(
    CostCentreId UNIQUEIDENTIFIER,
    CostCentreName VARCHAR(255),
    ServiceCharge VARCHAR(255),
    ChargeValue INT
);
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreOne, 'CostCentreOne', -- CostCentreId - uniqueidentifier
    'Service Charge 1',              -- ServiceCharge - varchar(255)
    10                               -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreOne, 'CostCentreOne', -- CostCentreId - uniqueidentifier
    'Service Charge 2',              -- ServiceCharge - varchar(255)
    15                               -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreOne, 'CostCentreOne', -- CostCentreId - uniqueidentifier
    'Service Charge 3',              -- ServiceCharge - varchar(255)
    20                               -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreOne, 'CostCentreOne', -- CostCentreId - uniqueidentifier
    'Service Charge 4',              -- ServiceCharge - varchar(255)
    20                               -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreOne, 'CostCentreOne', -- CostCentreId - uniqueidentifier
    'Service Charge 5',              -- ServiceCharge - varchar(255)
    25                               -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreOne, 'CostCentreOne', -- CostCentreId - uniqueidentifier
    'Service Charge 6',              -- ServiceCharge - varchar(255)
    25                               -- ChargeValue - int
    );


INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreTwo, 'CostCentreTwo', -- CostCentreId - uniqueidentifier
    'Service Charge 1',              -- ServiceCharge - varchar(255)
    5                                -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreTwo, 'CostCentreTwo', -- CostCentreId - uniqueidentifier
    'Service Charge 2',              -- ServiceCharge - varchar(255)
    25                               -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreTwo, 'CostCentreTwo', -- CostCentreId - uniqueidentifier
    'Service Charge 3',              -- ServiceCharge - varchar(255)
    25                               -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreTwo, 'CostCentreTwo', -- CostCentreId - uniqueidentifier
    'Service Charge 4',              -- ServiceCharge - varchar(255)
    35                               -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreTwo, 'CostCentreTwo', -- CostCentreId - uniqueidentifier
    'Service Charge 5',              -- ServiceCharge - varchar(255)
    40                               -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreTwo, 'CostCentreTwo', -- CostCentreId - uniqueidentifier
    'Service Charge 6',              -- ServiceCharge - varchar(255)
    40                               -- ChargeValue - int
    );






INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreThree, 'CostCentreThree', -- CostCentreId - uniqueidentifier
    'Service Charge 1',                  -- ServiceCharge - varchar(255)
    15                                   -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreThree, 'CostCentreThree', -- CostCentreId - uniqueidentifier
    'Service Charge 2',                  -- ServiceCharge - varchar(255)
    15                                   -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreThree, 'CostCentreThree', -- CostCentreId - uniqueidentifier
    'Service Charge 3',                  -- ServiceCharge - varchar(255)
    15                                   -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreThree, 'CostCentreThree', -- CostCentreId - uniqueidentifier
    'Service Charge 4',                  -- ServiceCharge - varchar(255)
    50                                   -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreThree, 'CostCentreThree', -- CostCentreId - uniqueidentifier
    'Service Charge 5',                  -- ServiceCharge - varchar(255)
    55                                   -- ChargeValue - int
    );
INSERT INTO #CostCentreValues
(
    CostCentreId,
    CostCentreName,
    ServiceCharge,
    ChargeValue
)
VALUES
(   @CostCentreThree, 'CostCentreThree', -- CostCentreId - uniqueidentifier
    'Service Charge 6',                  -- ServiceCharge - varchar(255)
    65                                   -- ChargeValue - int
    );


SELECT DISTINCT
       CostCentreValues.CostCentreId AS CostCentre,
	   CostCentreValues.CostCentreName,
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY CostCentreValues.ChargeValue ASC) OVER (PARTITION BY CostCentreValues.CostCentreId) AS MedianPerCostCentre
FROM #CostCentreValues AS [CostCentreValues]
ORDER BY CostCentreValues.CostCentreId

;WITH Mode AS (SELECT CostCentreId, CostCentreName, ChargeValue, COUNT(*) AS [Count],
RANK()  OVER(PARTITION BY CostCentreId ORDER BY COUNT(*) DESC, CostCentreId) AS [RowNum] FROM #CostCentreValues
GROUP BY CostCentreId,
CostCentreName,
         ChargeValue)

SELECT Mode.CostCentreId,
		Mode.CostCentreName,
       Mode.ChargeValue,
       Mode.RowNum FROM Mode WHERE Mode.RowNum = 1




DROP TABLE #CostCentreValues;