# Formula to calculate the Unusal number in a range

#### sergiom920

##### New Member
Guys, I have a range of Numbers and i would like to get a formula to look at the range and give me the "ODD" or "Unusual" number in the range. In this case it would reference the cell for 90.

Jan Feb Mar Apr May Jun Jul
10 20 15 22 90 29 16

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You need to define unusual or odd here. If 90 is unusual because it is too large, then the max function would work. But is "largeness" the only type of "oddness" you can get?

Gene, "The Mortgage Man", Klein

Yes unusual would be a number that is probably greater than the average of the whole set..

So if i have a trend 10 15 20 14 25 100, then 100 would be the Unusual number. Also i would like to be able to get more than one number if possible..

so if i had 10, 15, 20, 30, 100, 20, 200, i would be able to retrieve both 100, and 200 because they fall outside the pattern

I can see some difficulties with excludling any number greater than the average. If all you want to do is exclude those that are really bigger than the rest of list, then use the function large. =large(range,1) will return the biggest number in the range (IOW it will return the same number as =max(range)). =large(range,2) will return the 2nd largest number in the range, etc.

Gene, "The Mortgage Man", Klein

Replies
0
Views
205
Replies
13
Views
461
Replies
3
Views
154
Replies
11
Views
575
Replies
1
Views
172

1,196,273
Messages
6,014,383
Members
441,818
Latest member
itsfaisalkhalid

### 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.

### Which adblocker are you using?

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

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