Lambda - 00006 - CENTER OF GRAVITY

LAMBDA_CENTEROFGRAVITY.txt

/*
--------------------- CENTER OF GRAVITY ---------------------
*/
CENTEROFGRAVITY = LAMBDA(xLongitudes, yLatitudes, Volume,
    
/* The Center of Gravity method helps you find a central point that minimizes the transportation distance to all your destinations. 
Think of it as finding the balance point on a seesaw, where all weights (shipments) are balanced.

blog_web_link: https://excelexciting.com/how-to-calculate-center-of-gravity-to-find-a-distribution-center-using-microsoft-excel/

SYNTAX: =CENTEROFGRAVITY(xLongitudes,yLatitudes,Volume)

author,"Faraz Shaikh, Microsoft® MVP",
wesite,"www.ExcelExciting.com",
Created,"20240720",
*/
    LET(
        _author, HSTACK("Crafted By Faraz Shaikh, Microsoft®MVP", "www.ExcelExciting.com"),
        
        /*Weighted Sum of Longitudes*/
        _weighted_sum_of_longitudes, SUMPRODUCT(xLongitudes, Volume),
        /*Horzintal Stacking Longitudes*/
        _1HStack, HSTACK("Weighted Sum of Longitudes", _weighted_sum_of_longitudes),
        
        /*Weighted Sum of Latitudes*/
        _weighted_sum_of_latitudes, SUMPRODUCT(yLatitudes, Volume),
        /*Horzintal Stacking Latitudes*/
        _2HStack, HSTACK("Weighted Sum of Latitudes", _weighted_sum_of_latitudes),
        
        /*Total Weight*/
        _total_weight, SUM(Volume),
        /*Horzintal Stacking Weight*/
        _3HStack, HSTACK("Total Weight", _total_weight),
        
        /*Center of Gravity Longitude*/
        _CoG_longitude, (_weighted_sum_of_longitudes / _total_weight),
        /*Horzintal Stacking Weight*/
        _4HStack, HSTACK("Center of Gravity Longitude", _CoG_longitude),
        
        /*Center of Gravity Latitude*/
        _CoG_latitude, (_weighted_sum_of_latitudes / _total_weight),
        /*Horzintal Stacking Weight*/
        _5HStack, HSTACK("Center of Gravity Latitude", _CoG_latitude),
        
        /*Join Cordinates*/
        _join_cordinates, TEXTJOIN(", ", , ROUND(_CoG_longitude,6), ROUND(_CoG_latitude,6)),
        /*Horzintal Stacking Weight*/
        _6HStack, HSTACK("Cordinates", _join_cordinates),
        _finalStacking, VSTACK(_author, _1HStack, _2HStack, _3HStack, _4HStack, _5HStack, _6HStack),
        _result, _finalStacking,
        _finalStacking
    )
);