# Set Selection - Address Of Smallest Number?

Mar 21, 2012
very simple and short code.

Code:

Dim selection As Range

Dim minimum As Integer

Sub Sequence()

Set selection = Range(Cells(4, 3), Cells(6, 5))

minimum = Application.WorksheetFunction.Min(selection)

' and now I need an address of that smallest number for this:

' IF (column number + row number) > value of that smallest number

' do

End Sub

It's not exactly the program I need, but it's simple.

View 9 Replies
ADVERTISEMENT
Jan 14, 2010

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:

0

3.196408441

4.491972091

4.491972091

4.491972091

Equation:

=INDIRECT(ADDRESS(SMALL(IF((NumRange)=SMALL(NumRange,1),ROW(NumRange),""),1),1))

View 12 Replies
View Related
Jun 2, 2009

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 3 Replies
View Related
Dec 21, 2006

How can i easy return the smallest number higer than 0 from a set of numbers?

View 11 Replies
View Related
Jan 25, 2013

I need a formula that will find the second smallest number in a row. The row can contain as many as 200 numbers with many of them being zeros. That makes using the "small" function impossible.

View 9 Replies
View Related
Jul 17, 2012

I want to count the smallest numbers in a range of 10 cells after omitting the 4 largest. I have been using the formula

=SUM(IF(ISNUMBER(LARGE(E5:N5,{1,2,3,4})),LARGE(E5:N5,{1,2,3,4})),D5),

This works perfectly in that in the desired cell it enters the 4 largest numbers and a specified cell. I then want to be able to total the remainder.

View 9 Replies
View Related
Dec 29, 2009

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 9 Replies
View Related
Jan 25, 2014

I want to find the largest and smallest value for a number... E.g.

68734 the largest value is 87643 and the smallest value is 34678

39823 the largest value is 98332 and the smallest value is 23389

43089 the largest value is 98430 and the smallest value is 3489

View 4 Replies
View Related
Mar 30, 2007

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 14 Replies
View Related
Apr 14, 2009

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 7 Replies
View Related
Feb 11, 2010

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

0

0

54.2

0

82.6

0

0

55

So i need t get that 54.2 out of there.

View 9 Replies
View Related
Mar 10, 2014

I'm looking to find the smallest number in a column that fits a certain criteria, and I'm not sure where to put the if function, or if I can use some other 'qualifiers'.

I was thinking something along the lines of If(E:E<>2, Small(E:E, ROW()-1),____) but I'm not sure what the "false" argument for the if function would be in the blanks. I don't want there to be one.

Where E:E is the range I'm looking in, and 'not equal to 2' is the criteria I'm looking to have filled.

So, "Find the smallest number in E:E that does not equal 2" would be the wording.

View 5 Replies
View Related
Mar 10, 2009

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 4 Replies
View Related
Mar 26, 2009

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 6 Replies
View Related
Sep 2, 2008

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 9 Replies
View Related
Oct 4, 2006

I am wanting to allow the user to select a cell in a worksheet and return the cell address and information/value from the cell. For example, if "Dog" is in cell A1 and "Cat" is cell A2 and the user initiates the Macro I would like the macro to to pop up a message box and say something like "Select a cell in Column A". When the user clicks on cell A1, I would like the message box to pop up and say, you selected "Dog" in cell A1.

View 4 Replies
View Related
Jun 21, 2008

does excel have a function or some kind of method where you can have it display the range of cells that you have selected? For example, if I highlight cells A5:G7 what would be the code for excel to display "A5:G7" in a message box? I need this to be dynamic, so the next time I select cells B3:T32, it needs to display "B3:T32."

View 5 Replies
View Related
Oct 10, 2007

I have an embedded chart on my worksheet.I can select a cell behind the chart using the keyboard arrow keys.Is there a way of doing this using a mouse click,so that I know which cell i am pointing to/choosing?

View 4 Replies
View Related
Oct 15, 2008

What formula can i use to obtain the address of the cell which contains the highest number? My range is A:A

View 2 Replies
View Related
Oct 23, 2008

I have a list of street addresses. I want to alphabetize them. I just want to remove all the text (usually just numbers but not always) up to and including the first space only.

View 7 Replies
View Related
Jan 20, 2009

i'm looking to return the cell address of the max number in a given range?

View 3 Replies
View Related
Mar 18, 2013

I have the following text, in which I need separated into three columns.

Column A) Company Name

Column B) Address

Column C) Phone Number

I was thinking perhaps I could replace "-------" to just one "-" and use that delimiter to separate the phone number.

Then Use *** Company Name Here *** --> *Number Variable* and filter the rest..

But I don't see it being able to work.Here is the text I'm looking to format (Mind you there is about 8,000 Records.

And, it's all formatted like below. (Company Name, phone number being on different lines and phone number being separated by dash's

[Code].....

View 8 Replies
View Related
Apr 11, 2013

I found out a way to get rid of the street number from an address, but I would like the street and apartment number removed. Right now I have various formats:

Dilger Ave #61

Dilger Ave Apt 61

Dilger Ave Lot 61

I would like simply "Dilger".

View 5 Replies
View Related
Aug 26, 2009

I am trying to do season stats for my roller derby league. I have a great spreadsheet going but the one thing I want to do, I can't seem to do. I am attaching a "test" file. very simple and small but shows what I need to do.

I have different sections and I am trying to pull the highest number out of that section. For this example, it is under the green Jams Skated: Jams at Jammer. The number on the right is the highest number of jams skated while the number on the left should be the name of the skater who has this number. Okay, no problems there. My problem occurs when I try to move these same formulas to another sheet. in this case, sheet 2. It pulls all kinds of strange things.

View 4 Replies
View Related
May 18, 2007

I have something like the following:

If Worksheets("RawData"). Cells(1, nColumn).Value = Worksheets("RawData").Cells(12, 16).Value Then

..do code (I know the cell location of interest at this point)

End If

Then I End up needing To Do something Like the following

ActiveChart.SeriesCollection(1).XValues = _

Worksheets("RawData").Range("L1:N1")

“L1:N1” above is a sample. Given the code above it I have the cell location in question but it is in the format such as .Cells(12,16). That doesn’t do me much good when range wants something like “L1”. I don’t know how to convert that (12,16) to a L16 for example.

View 7 Replies
View Related
Aug 18, 2007

I am setting a variable to the value of an absolute cell reference. I want to set the value of another variable to just the row value of that absolute cell reference.

View 4 Replies
View Related
Jun 30, 2008

I have a 2000 row sheet with an address field that contains the apartment number and the address. The text is displayed as unit number, then hyphen and the building/street number for example '101-1234 15th Street'. I'm looking to cut the building number from the cell and paste it into a seperate column. Ideally removing the hyphen completely, so that I have two columns 'unit number' and 'building/street address'.

View 5 Replies
View Related
Oct 30, 2009

To select cells of a certain value I would use something like this:

If Range("E" & n).value = 90 Then

Range("E" & n).Select

End If

But I need to select cells with numbers starting with a certain number although they may not neccesarally be the same number of digets, ie i would equally want to select 789, 78910, and 700000000

How would you do this in VBA?

______________________________________________

Also, is it possible to use the above script to select multiple values ie

If Range("E" & n).value = 90 or 165 Then

Range("E" & n).Select

End If

View 9 Replies
View Related
Apr 19, 2006

random number selection...

i have a list of 200 items (rows) and i would like to "randomly" select 30 of them...(next month i will get another 200...)

View 3 Replies
View Related
Jun 12, 2014

I am using the macro below to count the number of specific character in a cell. The only problem it only counts one cell. How do I modify to display multiple cell counts if more than one cell is selected?

[Code].....

View 4 Replies
View Related