Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Finding Non Zero Smallest Numbers

Find smallest numbers in the range ignoring zeros.
I want it returned as value "1", all others - as value "0".
Example: range A1:J1 contains - 5,0,7,3,6,5,8,0,9,3
desirable result in the range A2:J2 - 0,0,0,1,0,0,0,0,0,1

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Address Of Smallest 5 Numbers (return The Value In Column 1 For The Smallest 5 Numbers)
I am using the following array equation to return the value in column 1 for the smallest 5 numbers. It works for small 1 and 3 but i get a #NUM! for 2 , 4 , and 5. The smallest 5 numbers are:




View Replies!   View Related
Finding Smallest Value In A Column
In column A, I have a list of names.
In column B, I have a list of values.
I want column C to show me which name has the smallest value from column B.
In other words, if there are 5 names, I want ONE name to give me a value of 10 (for the smallest value), and the rest of the names I want to show zero.

I also have in column D another list of values.
In column E, I want to show first, second and third place amongst the list of values from column D. The rest of the names I want to show zero.

View Replies!   View Related
Finding 10 Smallest Unique Values In A List
I have been using the SMALL function to find 10 smallest values in a list, however, some of the values are the same, is there a way of only having the 10 smallest unique values within a list?

View Replies!   View Related
Averaging Smallest, Non-zero Numbers In A Row
I'm struggling to get the syntax right for this formula. I've been trying variations on the following formula but can't get it right:


Columns AP:AZ:

Any column in this range could have a zero value.

What I need: The average of the 8 lowest non-zero numbers in range AP:AZ

View Replies!   View Related
Finding Common (repeated) Numbers In Columns Of Numbers
I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months.

If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.

View Replies!   View Related
Finding What Numbers Are In One Column But Not The Other
I have a large list of numbers in two columns. I need to know what numbers are in column A but don't appear in column B. For example if the numbers are 1 2 and 3 in column A but column B only has 1 and 2 I need to know that 3 is missing.

the only thing I can think of is doing =IF(A1=B1,"TRUE","FALSE") however typing this a thousand times would not be practical.

Is there a macro that can check column A against column B and tell me which ones are missing?

View Replies!   View Related
Finding Numbers From String
I am trying to find numbers from a string. I have for example words "EUR Fwd 9x12" and "Eur Fwd 11x15" And i want to write a function that reads the first number from a string if there is only one number before "x" or two numbers if there are 2 numbers. So I have tried to build following function:

Function NumbersInString(Word As String) As Integer
Dim i As Integer
Dim FirstNumberInString As Integer, SecondNumberInString As Integer
For i = 1 To Len(Word)
If IsNumeric(Mid(Word, i, 1)) Then
FirstNumberInString = Mid(Word, i, 1)
If IsNumeric(Mid(Word, i + 1, 1)) = False Then
Exit Function
SecondNumberInString = Mid(Word, i + 1, 1)
End If
End If

NumbersInString = FirstNumberInString & SecondNumberInString
End Function

View Replies!   View Related
Finding A Value Between Two Given Numbers In One Cell
I have another post here on this forum, but I'm afaid the formula is getting so complex that nobody is able to fully understand what I want. Instead I want to find a value between two numbers and add it to some IF sentences. It will do what I want, even if it's not that elegant.

I've looked at the SUMIF function but it did not do exactly what I wanted. It finds a number or adds numbers only if they are in range it seems.

What I want is the following:
Return sum between 500 and 1000 in one cell.

View Replies!   View Related
Finding An Average Couple Numbers
I am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.

I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.

View Replies!   View Related
Finding The Sum Within A Range Of Numbers
So I have multiple columns of numerical data,

I have to find the sum of all numbers between 10 and 40 within a column, NOT cell 10 to 40 but the actual number 10 and 40.

How do I do that?

Is it SUMIF? I cannt seem to grasp this.

View Replies!   View Related
Finding Which Numbers Make A Total
I do a lot of work in excel to do with accounts and this often needs checking against sage. When the invoices/petty cash sheets are put into sage the total amount is put in, but in my spreadsheets I need to split the reciepts. So I was wondering if there was a formula/VBA code, that if I only knew the total of the invoice would find which cells added up to this total?

View Replies!   View Related
Finding Sums Of Different Numbers Of Cells
I am building an inventory simulation and have run into a problem. What i want is, when i change a number in cell H4, i want excel to find the sum of C25 and the cells "H4" up. If H4 is 5 then i need the sum of C20:C25...if H4 is 10 i need C15:C25. Does anyone have any thoughts on how to do this? I have attached a sample sheet to make it more clear.

View Replies!   View Related
Finding Country Based On Phone Numbers
I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.

I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.

View Replies!   View Related
Finding The Middle Cell In A Column Of Numbers
I am looking for a formulas to first find the middle number in a column of numbers eg 1,2,3,4,5 3 is the middle (similar to median) thats where the calculations start...

it then assigns values of minus to the numbers above the middle and plus values to the numbers below the middle
1 -50
2 -50
3 0
4 +50
5 +50

now when it comes to even numbers eg 1,2,3,4,5,6 if i use median it divide 3 & 4 and comes up with 3.5 ........ i want it to recognize 3 and 4 as the middle numbers
and assign plus and minuses above and below the middle numbers
1 -50
2 -50
3 -25
4 +25
5 +50
6 + 50

View Replies!   View Related
Finding Number Closest To Zero (including Negative Numbers)
a spreadsheet in Excel. I have names with scores. Then I have the winning score. I need a formula to find the score closest to zero and to display the name of the winner.

Ex: Names A1:A4 and Scores B1:B4. Winning Score in B6 and list name in B7.
Ana 16
Bob 2
Charles 8
David 11

Winning Score 10

Answer should be 11 which is David, since David is only -1 away compared to the others.

View Replies!   View Related
Smallest Number Which Is Not Zero
How can i easy return the smallest number higer than 0 from a set of numbers?

View Replies!   View Related
Smallest Value In Range Linked To Another Value.
D3 = SMALL(H6:H45, COUNTIF(H6:H45,0) +1)

to determine smallest value in range excluding 0 is it possible to link that smallest value to another value like a name?

Eg. H6 = 5 K6 = Ben
H7 = 4 K7 = John
H8 = 9 K8 = Ted
Thefore D3 = John

View Replies!   View Related
The Smallest Value That Is Greater Than Or Equal To
I am looking for a function like MATCH if the match type were set to -1. However my data is sorted in ascending order. I am mining data from a Pivot Table, and it has dates across the top. Of course the pivot table will have the data sorted in ascending order from left to right. I want to find the first date that is greater than today. With weekends and holidays I can't just use TODAY()+1. Is there a function that can do what I am asking? Also I do not want to change the pivot table itself.

View Replies!   View Related
Largest And Smallest Sequences
I am using excel 2007 and I need help with the following if anyone would be kind enough. I have a dynamic array that consists of stream of 1's and 0's. I want to be able to find the largest sequence of 1's and the smallest sequence of 1's. So at a point in time the array mite be 111110001101111000 and I want to be able to dertermine the longest contiguous sequence of 1's. But as I say the array is dynamic and so I need to do that after each input.

View Replies!   View Related
LOOKUP Return Smallest Value
I have a large set of numerical data, with each column having a text heading in the first row.

I want to use a function similar to HLOOKUP that can return the numerical value in a certain row of a column that has a specified heading....AND if there is more than one numerical value that meets the criteria, I want it to return the lowest numerical value.

My numerical data cannot be sorted into ascending order.

View Replies!   View Related
Smallest Number In The Column
Currently we are using the formula below to get an average of column E in a spreadsheet. I was wondering how to modify the formula to get the smallest number in that column rather than the average.

=IF(ISERROR(SUMIFS(E:E,A:A,K2,B:B,K3,C:C,K4,D:D,K5)),"Not Available",AVERAGEIFS(E:E,A:A,K2,B:B,K3,C:C,K4,D:D,K5))

View Replies!   View Related
Return 3 Lowest/Smallest
I found this spreadsheet on ozgrid, but can not locate the original post to move my question to -- mods please move if you can.

As for the question:
The spreadsheet attached searches an array, and shows the top 3 scoring names. How do I modify it to show 3 lowest scoring names without changing the rank field?

View Replies!   View Related
Returning Second Smallest Unique Value
I'm creating a spreadsheet and have had difficultly creating a function that returns the second smallest unique value.

For example if my data set is:

2, 2, 3, 3, 4, 5, 6

I'd want the function to return "3". I've tried playing around with the SMALL(array, k) function and trying to use a COUNTIF function for "k".

View Replies!   View Related
Find Smallest In A List
I am trying to find the smallest values in a list. that part is easy (use SMALL). But, I want the ref to the cell where that value is in, not the value itself. Additional problem occurs when more than one of these values are the same. Example: A1 = 1; A2 = 3 ; A3 = 2 ; A4 = 2 ; etc. If I want the smallest, I want "A1" as result, for the 2nd smallest I want "A3", for the 3rd smallest I want "A4". So you see that a MATCH formula will not work beceause some of the values are the same.

View Replies!   View Related
Return Smallest Value, Larger Than Specified Value
I know function Vlookup finds the largest value less than or equal to the given value. However, If I want to finds the smallest value larger than or equal to the given value, How can I do it? assuming I do not want to use VBA.

View Replies!   View Related
Smallest Prime Factor Of A Number
Is there any formula witch i can use to find the smallest prime factor of a number?

Example: In A1 i write 30 (30=2*3*5 (the prime factors))

in B1 i write a formula, and the result will be 2 (smallest prime factor)

What is the formula?

View Replies!   View Related
Smallest Number In A List/Bigger Than X
Is there a forumula that will allow me to find the smallest number in A1:A10 that is also larger than the number in B1?

View Replies!   View Related
Smallest Number From List But Greater Than 0
I'm using this =SMALL(A:A,1) in a cell but i need it to ignore the number 0.

Because where theres no data it shows 0, but i need it to pick the lowest from the places where that data actually shows. something like this


So i need t get that 54.2 out of there.

View Replies!   View Related
Lookup Nth Largest/Smallest Value
Is there a way to reverse a lookup function?

I have a s/s attached, basically B is like an output of figures after some calculations. And I am trying to find from largest to smallest values and then having column E reflect the Letter beside that number...

tried using match but messed up.

View Replies!   View Related
Return Nth Greatest/Smallest Value
I want to ask if there is a way to find the next greatest maximum without a ton of auxiliary coding....Is there a function which does that in Excel? For example from the following list it would give 45:


View Replies!   View Related
Multiple Criteria Lookup For Smallest Value
I have a workbook with 4 sheets with Carrier rates based upon weight breaks and cities. I.E. Carrier 1 services City 1 @ 1.30 per LB for 0 -100LBS and 1.55 for 101 - 199LBS. What I need is a forumla so that someone can input the city and weight on sheet 1 and it will search the 4 sheets and return the cheapest Carrier in the event that there is two carriers with the same rate it would return both. It would also need to return the rate for that city and weight.

For example

User inputs "City 1" "112 LBS" formula searches the 4 carrier sheets and returns values "Carrier 1" & "1.55" as this is the cheapest carrier and the rate they charge per LB for this city.

View Replies!   View Related
Find Smallest And Largest Non Zero Date In A Range
I am having some data running into 60 (C:BJ) columns and 200 rows. It is the record of dates query raised and answered with refernce to query number in the A coulmn. I have zero value as entry for the unused cells in this range due to some constraints. I need to find the smallest and the largest non-zero date in this range for analysis purpose. I plan to use the difference between these 2 dates to calculate turn around time for a query.

Problem is to find the smallest and the largest non zero date within range C6:BJ6 which would be used as input in the Networkdays formula.

View Replies!   View Related
Find 0 Or Smallest Negative Number From Range
I am trying to write code that will locate either a 0 or the smallest negative number in a range, eg,

example 1,
8, 6, 2, 0, -0.5, -2
the result would be 0

example 2,
8, 6, 2, -0.5, -2
the result would be -0.5

View Replies!   View Related
Sum Smallest Value Per Row If Value Exists In Column A
The whole "story" is explained, in details, inside the attached WB.

View Replies!   View Related
Find Smallest Number With Multiple Criteria
I need a formula that will give me the smallest number based on multiple criteria.

For example, I want to find all:

1. Product A
2. 1 Liter
3. Smallest Price

In other words, out of all 1 Liter Product A's, I want to see the smallest price point.

I tried a sumproduct but I can't get it to work. Something like =sumproduct((A1:A100="Product A")*(B1:B100="1L")*(small(C1:C100,1)))

How can I get the small formula working within the sumproduct? Or is there another workaround?

View Replies!   View Related
Highlight Smallest & Largest Values
I have a work document with multiple rows of information. In each row I need to locate the maximum and minimum values. (The rows aren't long - there are about 10 values in each). I then would like to turn the font of the lowest value in each row red - and the font of the highest value in each row blue. I would also like to be able to skip blank or unimportant rows.

View Replies!   View Related
Compare Smallest Value With Cell Above & Calculate
I'm having trouble with the "sister" formula I need to add. I've tried the function SMALL, but apparently not in the right format.... Here is some data again, with the answers I'm seeking & notes in Col C:

mon....6....(first data pt, so nothing here)
tue.....8.....33.33 (this is 2/6ths higher than 6)
wed...10....66.66 (this is 4/6th higher than 6)
thurs...8....33.33 (same as tuesday)
fri.......6.....00.00 (Equal to the lowest data pt, ie 6)
sat.....5.....(finally a vlaue less than 6, so: "ERR")
sun.....7.....40.00 (7 is 2/5th higher than 5)
mon....15....200.00 (10/5ths higher than 5)
tue.....4......(a new lowest value; "ERR")
wed....8......100.00 (4/4ths greater than 4)

That is, any value in Col B that is GREATER than the lowest, SMALLEST value that has preceeded it - in chronological order - will reflect the % by which it is greater. When a new lowest value appears in Col B, a simple text "ERR" would appear, and THAT new low value will be the new "target" the subsequent values in B are compared to. (In the example, once 5 appears, we stop looking at 6...)

View Replies!   View Related
Find Smallest Absolute Value And Row Number With A Macro
I've a large excel file contain a sheet of values. With a Macro I would like to
1) find the smallest absolute value in a certain column
2) find the row number where this value was found
3) Copy certain values from this row to another place.

View Replies!   View Related
Return Cell Address Of Largest & Smallest Value In Column
How would I return a cell reference (address) to a cell that contains the largest number in a list?

I tried using "Address(large....) where I get the correct column, but the returned row # is the actual value in the cell (the highest # in the list).

I eventually will want to delete the highest number to leave the cell blank.

View Replies!   View Related
Set Chart/Graph Minimum/Maximum Scale To Nth Smallest & Largest Value
I am trying to manually format a few graphs in my sheet. I, however, leave the major units of the axes to be automatically calculated. The code that I use is given below:

With ActiveChart.Axes(xlValue)
.MinimumScale = Range("Min").Value
.MaximumScale = Range("Max").Value
End With

Now what this does is that it sets the maximum of the scale exactly equal to maximum of the range being plotted and hence a few parts of my graph overlap with the border of the plot area. Is there any way in which I can round the max and min scale of y-axis to the next highest and lowest major unit respectively or may be add and subtract a value proportional to the major unit of the y-axis. I cannot add a constant in the code given above since I that does not suite my requirements.

View Replies!   View Related
Apply Credits To Running Balance, Pick Smallest Balance
For the small database in my example workbook, I would like to apply credits earned at a later date to the oldest charges and create a "To Date" balance. After creating the "To Date" balance, I would like to select the smallest "To Date" balance as a way to identify the first charge that has no payment. Please see the attached for more clarity and additional information. The last column shows the desired results.

View Replies!   View Related
Gathering The Sum Of Negative Numbers & Positive Numbers In A Column
I have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.

View Replies!   View Related
Extracting Numbers From Alphanumeric Strings (unite The X Amount Of Numbers)
Is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?

DATA output should be
asd67,h876 --------> 67876
2,3,ujdj5&34 -------> 23534
909k86m34 --------> 9098634

View Replies!   View Related
Macro Code To Change Positive Numbers Into Negative Numbers
I know ASAP has a feature to do this but I need the code in a bigger macro that I wrote.

EX: -1 needs to be 1

View Replies!   View Related
Extracting Numbers From Text And Turning The Numbers Into Rows Of Data
The format of the text in which I need to extract numbers is as follows:

23411268 - 23411270

Need to extract the following:


These numbers have to be listed in three seperate rows.

View Replies!   View Related
Convert Numbers Stored As Text To Numbers (attached File)
In the ID column of the attached excel file whenever I convert the numbers stored as text to numbers it results in a weird transformation.

e.g. an ID of 480610141001 becomes 4.8061E+11. When I do the same process in the name column, which has similar numbers, it will give the correct result following the same process.

View Replies!   View Related
Convert Downloaded Web Page Numbers Seen As Text To Numbers
See attached file. A colleague is downloading rows of data from a website which contains a number field Excel is currently treating as Text after being pasted in. My spreadsheet includes just a sample of the many rows of data however as you can see the VALUE function refuses to convert these text values to numbers. How these might be converted and why the VALUE function refuses to work in this case?

View Replies!   View Related
Compare 2 Columns For Numbers In Mixed Text & Numbers
I need to compare two colums by number decription for example m344 in one column and fsh344-1 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.

View Replies!   View Related
Deleting Rows Matching Numbers From Numbers In Column
I want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?

View Replies!   View Related
Delete Numbers And Replace With Numbers From Adjacent Column
Is it possible to delete a column of numbers then replace those numbers from adjacent columns and the adjacent columns are then filled with the numbers adjacent to them

View Replies!   View Related
Copyright 2005-08, All rights reserved