using Large when the cell has more than 15 digits

M

Moxioron

Active Member
Joined
Mar 17, 2008
Messages
436
Office Version
  1. 2019
Hello.

I am trying to use Large to obtain the second largest number in a column with a condition. Since the total digits of numbers in that column = 16, I am getting #NUM! because I had to format the column as text in order for the numbers to display correctly.

Is there a way in Excel to keep the number in tact and format it as a number when it is greater than 15 digits so you can use formulas that are specific to values? Thanks.

=LARGE(FILTER($Q$4:$Q$100000,$B$4:$B$100000=$B4),2)
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
What version of Excel are you using? Your profile shows 2019 which doesn't have the filter function.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Tie-Breaking Question (Using Index/Match/Large)
Replies
3
Views
358
Fluff
Fluff
F
  • Solved
AverageIF less than 15 including values under 0 (negative numbers)
Replies
5
Views
245
Fluff
Fluff
B
  • Question
VBA to edit/update cells within a large table from a common "Dashboard"
Replies
0
Views
46
brianv
B
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
207
ItalianPlatinum
I
K
  • Question
Previously working very complex VBA now stopped
Replies
2
Views
95
karl10220
K

Forum statistics

Threads
1,215,325
Messages
6,124,254
Members
449,149
Latest member
mwdbActuary

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