creating a formula that will calculate the mode of a value in a table.

Z

zacharystucki

New Member
Joined
Sep 11, 2015
Messages
8
So:
I have a big table (approximately 53,000 rows of data and over 75 columns).
I want to calculate the mode for a specific subset of the data so I was using a vlookup to isolate the subset of data, then I was using the mode to calculate the mode.


Here is the formula I have been trying to use - =VLOOKUP(A2,Table3,MODE(IF(Sheet1!$BK:$BK=Sheet1!$BX:$BX,VLOOKUP(A2,Table2,26,FALSE),Sheet1!BK14),),FALSE)


If you break it down this part of the formula works: IF(Sheet1!$BK:$BK=Sheet1!$BX:$BX,VLOOKUP(A2,Table2,26,FALSE),Sheet1!BK14)
Once I add the mode it stops working.


I want to get this to work because it could be a big monetary boost for our company.
I am no VBA expert, but I do know a little about it so I am willing to look into using VBA as a solution.
 
Sort by date Sort by votes
Excel Workbook
ABCDE
1InitialCompany Nameproduct/service codeAllowableAllowable 2
112KKBLACK36415$ 2.61$ 3.00
277KKBLACK36415$ 2.61$ 3.00
536AJHBLACK36415$ 24.50$ 3.00
573SSBLACK36415$ 2.22$ 3.00
579SSBLACK36415$ 2.22$ 3.00
584SSBLACK36415$ 2.22$ 3.00
1374SSBLACK36415$ 2.61$ 3.00
1412SSBLACK36415$ 2.61$ 3.00
1768AJHBLACK36415$ 2.61$ 3.00
1874AJHBLACK36415$ 2.61$ 3.00
1972SSBLACK36415$ 2.70$ 3.00
1978JPEBLACK36415$ 2.61$ 3.00
2156JPEBLACK36415$ 2.61$ 3.00
2196AJHBLACK36415$ 2.61$ 3.00
2260JPEBLACK36415$ 2.22$ 3.00
2264AJHBLACK36415$ 2.61$ 3.00
2273AJHBLACK36415$ 2.61$ 3.00
2275AJHBLACK36415$ 2.61$ 3.00
2281AJHBLACK36415$ 2.61$ 3.00
2323SSBLACK36415$ 2.61$ 3.00
2467KKBLACK36415$ 2.61$ 3.00
2534SSBLACK36415$ 24.50$ 3.00
2619AJHBLACK36415$ 2.61$ 3.00
2695AJHBLACK36415$ 2.70$ 3.00
2726AJHBLACK36415$ 2.61$ 3.00
2792KKBLACK36415$ 2.61$ 3.00
2799SSBLACK36415$ 2.61$ 3.00
3084JPEBLACK36415$ 2.70$ 3.00
3129SSBLACK36415$ - $ 3.00
3252JPEBLACK36415$ 2.61$ 3.00
3282KKBLACK36415$ 2.61$ 3.00
3290KKBLACK36415$ 2.70$ 3.00
3301JPEBLACK36415$ 2.61$ 3.00
3367KKBLACK36415$ 2.61$ 3.00
3389KKBLACK36415$ 2.61$ 3.00
3425KKBLACK36415$ 2.61$ 3.00
3434JPEBLACK36415$ 2.61$ 3.00
3480SSBLACK36415$ 2.61$ 3.00
3604SSBLACK36415$ 2.61$ 3.00
3610JPEBLACK36415$ 2.61$ 3.00
DATA

6593269

I HOPE THIS WORKS>
If you want an example spreadsheet let me know and I will send it to you.
 
Last edited:
Upvote 0
Excel Workbook
ABC
1product/service codeDescriptionBLACK
236415product/service 1=VLOOKUP(A2,Table1,MODE(IF(Table1[Allowable]=Table1[Allowable 2],VLOOKUP(A2,Table23,3,FALSE),Table1[Allowable])),FALSE)
336416product/service 2
458340product/service 3This should return the number 2.61. (It needs to be duplicable for all the codes; returning the MODE in the Allowable column in the DATA tab for all the codes.)
558611product/service 4
659000product/service 5
759015product/service 6
859025product/service 7
959320product/service 8
1059514product/service 9
1159871product/service 10
1276377product/service 11
1376705product/service 12
1476775product/service 13
1576801product/service 14
1676802product/service 15
1776805product/service 16
1876810product/service 17
1976811product/service 18
2076812product/service 19
2176813product/service 20
2276814product/service 21
2376815product/service 22
2476816product/service 23
2576817product/service 24
MODE DATA
 
Upvote 0
Excel Workbook
ABC
1product/service codeDescriptionWHITE
236415product/service 1$3.00
336416product/service 2$2.86
458340product/service 3$119.70
558611product/service 4$77.87
659000product/service 5$126.45
759015product/service 6$158.35
859025product/service 7$48.77
959320product/service 8$155.85
1059514product/service 9$935.68
1159871product/service 10$136.31
1276377product/service 11$64.14
1376705product/service 12$92.43
1476775product/service 13$57.79
1576801product/service 14$125.42
1676802product/service 15$66.15
1776805product/service 16$144.01
1876810product/service 17$95.74
1976811product/service 18$183.66
2076812product/service 19$208.37
2176813product/service 20$122.83
2276814product/service 21$82.51
2376815product/service 22$85.55
2476816product/service 23$116.21
2576817product/service 24$98.38
2676818product/service 25$122.87
2776819product/service 26$89.79
2876820product/service 27$47.69
2976821product/service 28$93.73
3076825product/service 29$279.97
3176826product/service 30$165.95
3276827product/service 31$77.01
3376828product/service 32$54.13
3476830product/service 33$123.45
3576831product/service 34$119.83
3676856product/service 35$110.66
3776857product/service 36$47.47
3876945product/service 37$38.26
3976946product/service 38$33.08
4081002product/service 39$3.48
4193325product/service 40$26.37
4293976product/service 41$164.92
4396372product/service 42$22.79
4499000product/service 43$5.68
4599024product/service 44$0.00
4699201product/service 45$43.80
4799202product/service 46$74.78
4899203product/service 47$108.39
4999204product/service 48$164.93
5099205product/service 49$207.17
5199211product/service 50$19.98
5299212product/service 51$43.80
5399213product/service 52$72.68
5499214product/service 53$107.90
5599215product/service 54$145.53
5699217product/service 55$72.90
5799219product/service 56$136.15
5899221product/service 57$0.00
5999222product/service 58$0.00
6099223product/service 59$0.00
6199231product/service 60$0.00
6299232product/service 61$0.00
6399233product/service 62$0.00
6499234product/service 63$134.23
6599238product/service 64$73.26
6699241product/service 65$48.68
6799242product/service 66$91.32
6899244product/service 67$124.75
6999244product/service 68$184.64
7099245product/service 69$225.81
7199251product/service 70$49.39
7299252product/service 71$75.67
7399253product/service 72$115.59
7499255product/service 73$201.72
7599354product/service 74$99.94
7699355product/service 75$97.08
77J2790product/service 76$80.30
Price Schedule Grid
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Insert new row with incremental formula
Replies
8
Views
1K
Sheik
S
N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
231
NKUNZI
N
L
  • Solved
Formula being stored as text, when assigned from an array in VBA
2
Replies
10
Views
902
RobP
R
F
  • Solved
Combined formula help
2
Replies
11
Views
624
dreid1011
dreid1011
M
  • Question Question
Formula Calculation is Inadvertently Switching from Automatic to Manual...
Replies
0
Views
390
MNexcelguy19
M

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