Data based on band

Z

Zubair

Active Member
Joined
Jul 4, 2009
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
Hi Experts,

I have data of 2000 customers, from which shown below 250 only, I need formula to bring volumes of actual and plan based on the banding

Please help.

Data Sheet

CustomerActuals volume in LtrPlan volume in LtrDelta % vs. Plan
Customer 453,0824,371-29.49%
Customer 263,1904,493-29.00%
Customer 493,0643,952-22.46%
Customer 432,9183,638-19.78%
Customer 393,8584,771-19.14%
Customer 542,7163,325-18.32%
Customer 212,9833,582-16.71%
Customer 403,1843,823-16.70%
Customer 382,8183,371-16.39%
Customer 483,0123,602-16.38%
Customer 413,3223,973-16.38%
Customer 513,1713,771-15.90%
Customer 373,3373,960-15.72%
Customer 133,1003,670-15.54%
Customer 463,1833,753-15.21%
Customer 274,0534,717-14.07%
Customer 602,7073,150-14.06%
Customer 443,4863,993-12.69%
Customer 534,0044,567-12.33%
Customer 423,1263,533-11.53%
Customer 593,1873,583-11.04%
Customer 303,4953,919-10.80%
Customer 363,7294,174-10.65%
Customer 2293,2433,628-10.61%
Customer 572,8613,187-10.23%
Customer 223,2153,581-10.23%
Customer 1323,0323,360-9.76%
Customer 1562,7383,029-9.60%
Customer 283,2873,632-9.50%
Customer 313,2863,605-8.85%
Customer 2202,9413,215-8.52%
Customer 633,2863,542-7.23%
Customer 1393,2053,454-7.21%
Customer 703,0293,254-6.92%
Customer 1273,4483,701-6.83%
Customer 2022,9213,116-6.25%
Customer 1472,9913,188-6.19%
Customer 143,1603,364-6.07%
Customer 2183,1733,377-6.04%
Customer 1432,6222,790-6.01%
Customer 1163,2683,471-5.86%
Customer 2443,0463,222-5.45%
Customer 1923,3553,546-5.40%
Customer 1613,7023,906-5.24%
Customer 1963,5913,787-5.19%
Customer 172,8643,020-5.17%
Customer 523,2623,433-4.97%
Customer 943,9424,144-4.88%
Customer 813,6673,850-4.75%
Customer 1333,5443,719-4.71%
Customer 1462,7452,880-4.69%
Customer 333,0483,192-4.53%
Customer 473,8384,019-4.49%
Customer 2453,3423,497-4.44%
Customer 1503,2733,421-4.32%
Customer 584,0654,248-4.31%
Customer 1573,1013,239-4.27%
Customer 1183,5603,707-3.97%
Customer 1553,6093,740-3.49%
Customer 1353,5113,634-3.38%
Customer 503,6263,749-3.27%
Customer 1223,3763,488-3.21%
Customer 1763,0893,191-3.19%
Customer 1423,8013,918-2.99%
Customer 353,0123,105-2.99%
Customer 1412,8772,962-2.86%
Customer 1533,2873,380-2.78%
Customer 83,6073,699-2.48%
Customer 2092,9212,991-2.33%
Customer 323,4973,577-2.24%
Customer 2313,9073,993-2.17%
Customer 2473,5473,623-2.10%
Customer 743,5753,651-2.08%
Customer 623,5423,610-1.89%
Customer 2013,4533,518-1.85%
Customer 153,3453,408-1.85%
Customer 93,5103,574-1.79%
Customer 343,8143,880-1.70%
Customer 2404,0244,090-1.61%
Customer 1203,9043,965-1.54%
Customer 1943,6383,686-1.30%
Customer 1443,3223,365-1.28%
Customer 2213,4413,485-1.26%
Customer 653,6793,725-1.23%
Customer 2383,6903,734-1.20%
Customer 113,5313,573-1.20%
Customer 1453,0723,108-1.16%
Customer 1313,6133,645-0.88%
Customer 74,0154,050-0.87%
Customer 713,1833,210-0.85%
Customer 1603,5713,601-0.84%
Customer 1883,4543,482-0.82%
Customer 1853,7583,788-0.80%
Customer 243,9884,015-0.68%
Customer 2343,9954,019-0.59%
Customer 1753,6443,666-0.58%
Customer 793,6383,658-0.54%
Customer 1523,5743,593-0.54%
Customer 1543,8723,889-0.44%
Customer 1244,4754,491-0.37%
Customer 293,7823,791-0.25%
Customer 824,2484,252-0.10%
Customer 853,2733,2710.05%
Customer 804,2144,2080.13%
Customer 53,7243,7150.24%
Customer 1823,7663,7560.25%
Customer 564,3334,3200.32%
Customer 2063,5223,5090.37%
Customer 1973,6203,6000.57%
Customer 2463,5873,5650.62%
Customer 2173,4873,4521.01%
Customer 194,2384,1941.03%
Customer 693,7573,7161.11%
Customer 2373,6223,5771.26%
Customer 1383,1593,1181.32%
Customer 2124,0754,0141.53%
Customer 233,6293,5731.55%
Customer 1513,6323,5751.59%
Customer 1863,5073,4511.60%
Customer 1174,1764,1041.74%
Customer 2413,8383,7711.78%
Customer 754,2314,1521.90%
Customer 163,7713,7001.92%
Customer 2133,5123,4412.06%
Customer 204,0984,0122.15%
Customer 1584,1924,1022.20%
Customer 2253,7173,6332.31%
Customer 2273,8483,7592.37%
Customer 23,6693,5822.43%
Customer 2503,9693,8722.51%
Customer 2393,9163,8182.54%
Customer 2153,8743,7762.61%
Customer 1663,9263,8262.62%
Customer 1373,6953,6002.64%
Customer 1153,7473,6492.70%
Customer 2034,2194,1082.70%
Customer 1143,9703,8652.73%
Customer 613,5383,4432.75%
Customer 1293,8843,7772.83%
Customer 1004,4514,3272.85%
Customer 1993,6853,5832.86%
Customer 1054,1113,9863.15%
Customer 1133,6623,5493.20%
Customer 1213,8163,6983.21%
Customer 2224,6034,4553.32%
Customer 2263,2433,1383.34%
Customer 1913,7133,5863.53%
Customer 934,7754,6113.54%
Customer 554,7704,6063.55%
Customer 1033,5843,4583.64%
Customer 1773,9993,8563.71%
Customer 643,8023,6623.82%
Customer 2423,3733,2473.89%
Customer 123,8503,7053.93%
Customer 2004,3314,1634.04%
Customer 673,8633,7114.11%
Customer 1114,3594,1864.12%
Customer 2103,6183,4744.13%
Customer 1983,7173,5704.14%
Customer 2043,4943,3554.16%
Customer 783,8843,7224.33%
Customer 2323,7783,6154.51%
Customer 874,4044,2054.74%
Customer 1803,9853,8004.85%
Customer 1814,1743,9794.89%
Customer 1195,0054,7704.91%
Customer 664,3274,1234.97%
Customer 774,0113,8145.17%
Customer 1593,3483,1745.46%
Customer 984,0733,8625.48%
Customer 2484,0223,8125.51%
Customer 1834,9644,7025.58%
Customer 254,3134,0775.78%
Customer 2304,2013,9715.80%
Customer 2114,6724,4115.92%
Customer 1014,3874,1356.11%
Customer 44,4814,2206.18%
Customer 2195,5235,2026.18%
Customer 2364,5394,2696.33%
Customer 994,4944,2256.38%
Customer 964,0163,7716.50%
Customer 734,4114,1346.70%
Customer 1024,6834,3866.77%
Customer 2164,5694,2796.78%
Customer 1623,8113,5696.78%
Customer 2334,5054,2107.03%
Customer 954,5714,2717.03%
Customer 1494,2153,9377.07%
Customer 1255,1184,7757.18%
Customer 975,8385,4477.19%
Customer 1065,7545,3657.24%
Customer 2144,2693,9807.25%
Customer 834,6204,3077.27%
Customer 13,5693,3247.39%
Customer 2434,0683,7867.44%
Customer 2074,3774,0677.62%
Customer 1304,1143,8207.68%
Customer 64,5614,2317.80%
Customer 1654,7614,4167.82%
Customer 1904,1323,8248.06%
Customer 1685,0354,6578.12%
Customer 2494,8844,5168.14%
Customer 1933,8743,5738.44%
Customer 1744,6724,3058.53%
Customer 1873,8423,5388.59%
Customer 183,8193,5148.68%
Customer 2234,8364,4508.69%
Customer 1714,1853,8488.76%
Customer 1483,7993,4918.82%
Customer 1644,7734,3808.96%
Customer 685,3044,8549.28%
Customer 1784,0553,7099.32%
Customer 1954,3013,9339.35%
Customer 1095,2184,7599.66%
Customer 1634,8894,4569.72%
Customer 1724,7464,3249.75%
Customer 845,0584,6039.88%
Customer 1404,5694,15210.04%
Customer 2055,1144,63610.32%
Customer 1235,2814,78710.33%
Customer 1123,6013,26310.35%
Customer 2244,8644,40610.40%
Customer 2085,1414,65110.55%
Customer 1794,7654,30510.69%
Customer 1704,2493,83710.74%
Customer 1044,6374,18510.78%
Customer 724,5744,12610.85%
Customer 1075,1224,61411.00%
Customer 924,9054,40211.44%
Customer 2354,9424,42911.58%
Customer 1264,9794,46111.61%
Customer 894,5924,11211.67%
Customer 764,6804,18911.72%
Customer 1084,8414,33011.78%
Customer 915,3654,76312.64%
Customer 1895,3124,70612.89%
Customer 1284,3083,81213.01%
Customer 1694,5534,02613.08%
Customer 1675,9695,27413.18%
Customer 906,1505,41113.64%
Customer 1844,6114,05513.71%
Customer 1105,5234,84713.93%
Customer 885,3734,69014.58%
Customer 866,0655,24315.67%
Customer 34,0743,46217.66%
Customer 22829,13719,26751.23%
Customer 13418,93112,15155.81%
Customer 13623,02414,14062.83%
Customer 1014,4048,11377.54%
Customer 17337,83819,87490.39%
Grand Total1,085,4771,023,7356.03%


Result - Column F to J (Number of sites formula shown below
Band for formulaBand for informationActuals volume in LtrPlan volume in LtrNumber of sites
-20.00%​
>-20%3
-10.00%​
-10% ~ -20%23
-5.00%​
-5% ~ -10%20
-1.00%​
-1% ~ -5%41
-0.01%​
-0.01% ~ -1%15
1.00%​
1% ~ 5%8
5.00%​
5% ~ 10%57
10.00%​
10% ~ 20%50
20.00%​
>20%33

=COUNTIF($D$2:$D$251,"<"&$F2)-SUM($J$1:J1)
=COUNTIF($D$2:$D$251,"<"&$F3)-SUM($J$1:J2)
=COUNTIF($D$2:$D$251,"<"&$F4)-SUM($J$1:J3)
=COUNTIF($D$2:$D$251,"<"&$F5)-SUM($J$1:J4)
=COUNTIF($D$2:$D$251,"<"&$F6)-SUM($J$1:J5)
=COUNTIF($D$2:$D$251,"<"&$F7)-SUM($J$1:J6)
=COUNTIF($D$2:$D$251,"<"&$F8)-SUM($J$1:J7)
=COUNTIF($D$2:$D$251,"<"&$F9)-SUM($J$1:J8)
=COUNTIF($D$2:$D$251,"<>"&$F10)-SUM($J$1:J9)



 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hi,

Convert the COUNTIF to SUMIF like this COUNTIF($D$2:$D$251,"<"&$F2, <RANGE with Actual volumes>) and the sum part to SUM($H$1-H1) and repeat this in same way for planned volumes
 
Upvote 0
Solution
How do you feel about using a helper column ?

The main table is called tblData the summary table is called tblBanding

20210802 VLookup True.xlsx
ABCDEFGHIJKLM
1CustomerActuals volume in LtrPlan volume in LtrDelta % vs. PlanBands
2Customer 4530824371-29.49%UnknownBand for formulaBand for informationActuals volume in LtrPlan volume in LtrNumber of sites
3Customer 2631904493-29.00%Unknown-100.00%Unknown9,33612,8163
4Customer 4930643952-22.46%Unknown-20.00%>-20%--0
5Customer 4329183638-19.78%>-20%-10.00%-10% ~ -20%62,94567,57920
6Customer 3938584771-19.14%>-20%-5.00%-5% ~ -10%142,594146,77141
7Customer 5427163325-18.32%>-20%-1.00%-1% ~ -5%56,81057,15015
8Customer 2129833582-16.71%>-20%-0.01%-0.01% ~ -1%30,03929,9448
9Customer 4031843823-16.70%>-20%1.00%1% ~ 5%223,272216,80057
10Customer 3828183371-16.39%>-20%5.00%5% ~ 10%1,309,7571,232,41851
11Customer 4830123602-16.38%>-20%10.00%10% ~ 20%138,159123,17428
12Customer 4133223973-16.38%>-20%20.00%>20%--0
13Customer 5131713771-15.90%>-20%
14Customer 3733373960-15.72%>-20%
15Customer 1331003670-15.54%>-20%
16Customer 4631833753-15.21%>-20%
17Customer 2740534717-14.07%>-20%
18Customer 6027073150-14.06%>-20%
19Customer 4434863993-12.69%>-20%
20Customer 5340044567-12.33%>-20%
21Customer 4231263533-11.53%>-20%
22Customer 5931873583-11.04%>-20%
23Customer 3034953919-10.80%>-20%
24Customer 3637294174-10.65%>-20%
25Customer 22932433628-10.61%>-20%
26Customer 5728613187-10.23%>-20%
27Customer 2232153581-10.23%>-20%
28Customer 13230323360-9.76%-10% ~ -20%
29Customer 15627383029-9.60%-10% ~ -20%
30Customer 2832873632-9.50%-10% ~ -20%
31Customer 3132863605-8.85%-10% ~ -20%
32Customer 22029413215-8.52%-10% ~ -20%
33Customer 6332863542-7.23%-10% ~ -20%
34Customer 13932053454-7.21%-10% ~ -20%
Data
Cell Formulas
RangeFormula
J3:J12J3=SUMIFS(tblData[Actuals volume in Ltr],tblData[Bands],[@[Band for information]])
K3:K12K3=SUMIFS(tblData[Plan volume in Ltr],tblData[Bands],[@[Band for information]])
L3:L12L3=COUNTIFS(tblData[Bands],[@[Band for information]])
E2:E34E2=VLOOKUP($D2,tblBanding[[Band for formula]:[Band for information]],2,TRUE)
 
Upvote 0
The summary is exactly appearing as I needed, thanks Alex for your input as well, can I ask 1 more requirement, shown below :

Band for formulaBand for informationActuals volume in LtrPlan volume in LtrNumber of sites
-20.00%​
>-20%9,33612,8163
-10.00%​
-10% ~ -20%74,71087,26923
-5.00%​
-5% ~ -10%62,94367,57820
-1.00%​
-1% ~ -5%142,593146,77341
-0.01%​
-0.01% ~ -1%56,81057,15315
1.00%​
1% ~ 5%30,04029,9458
5.00%​
5% ~ 10%223,270216,80057
10.00%​
10% ~ 20%224,282208,68250
20.00%​
>20%261,493196,72033
1,085,4771,023,735250

Based on the above band I need customer as well if possible, means based on column F "Band for formula" respective customer should appear in H like below,

Band for formulaBand for informationCustomerActuals volume in LtrPlan volume in LtrNumber of sites
-20.00%​
>-20%Customer 453,0824,3713
Customer 263,1904,493
Customer 493,0643,952
-10.00%​
-10% ~ -20%23
-5.00%​
-5% ~ -10%20
-1.00%​
-1% ~ -5%41
-0.01%​
-0.01% ~ -1%15
1.00%​
1% ~ 5%8
5.00%​
5% ~ 10%57
10.00%​
10% ~ 20%50
20.00%​
>20%33
 
Upvote 0
Hi,

This looks like an awful lot like you're trying to create some kind of Pivot table.
What do you mean with "appear" ? should it automatically come up as a result?
 
Upvote 0
The above table required for site owners to review top 10 and bottom 10 % change, each owner's name is linked with the customer and all results are shared in a single file, so based on the banding we are focusing 1st of negative sites less than -10%, so will select only 1st 2 bands to review customers.

Team is not feel ease with pivot table.
 
Upvote 0
appear means while selecting band, respective customer with volumes required as shown below as an example -20%

Band for formulaBand for informationCustomerActuals volume in LtrPlan volume in LtrNumber of sites
-20.00%>-20%Customer 453,0824,3713
Customer 263,1904,493
Customer 493,0643,952
-10.00%-10% ~ -20%23
 
Upvote 0
There has to be a column missing to be allow selection by Site Owner.
Options using the Helper column.
  • let them filter the table directly
  • create a pivot and let them use that
  • hide the pivot but use the pivot to feed into a presentation sheet.
    (you would have to have some idea of how what the maximum number of rows you are going to need in each output category)
  • If you upgrade to 365 there may be some additional options.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top