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?
Searching through the forums, I've found a post of daddylonglegs, witch shows how to find the smallest factor of a number:
View 7 Replies View RelatedI have a column of random number in Column A and need a formula to identify if any of the numbers in Col A is a prime number. If it is, then say "Prime" in the cell adjacent to the prime number in Col B. Eg if A12 has a value 17, then cell B12 should say "Prime".
i'm looking to determine if a number is prime or not...
i thought of testing with the mod function in an adjacent cell...but i'm not sure how to do...?
Is there a way of returning the nth factor of a number via a formula?
That is to say, if I have 10, its divisors are 1, 2, 5 and 10, and if I wanted to return the second factor, it would be 2.
How can i easy return the smallest number higer than 0 from a set of numbers?
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 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.
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))
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
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 Relatedvery 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.
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.
I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!
View 4 Replies View RelatedI'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.
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.
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
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?
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))
I have one report that cannot include some special characters so I replace them with empty space. I need also to exclude symbol double prime ( " ). Below is my code, but third line is wrong of course three double primes in a row (two to define text and one is symbol I want to remove), VBA doesn't recognize it. So how could I define that this program also replaces " with space.
Code:
Sheets("Stavke").Range("C2:C" & LR).Replace What:="+", Replacement:=" ", LookAt:=xlPart
Sheets("Stavke").Range("C2:C" & LR).Replace What:="#", Replacement:=" ", LookAt:=xlPart
Sheets("Stavke").Range("C2:C" & LR).Replace What:=""", Replacement:=" ", LookAt:=xlPart
How can I find prime numbers in a range and highlight it if a number in a cell is a prime.
View 9 Replies View RelatedI have the task of creating a spreadsheet that will track and give point value to our sales reps' daily accessory sales. The following is the point value criteria for dollars:
Sell over $30 - 1 point daily
Sell over $100 - 2 points daily
Sell over $150 - 3 points daily
Sell over $300 - 5 points daily
Sell over $500 - 6 points daily
Sell over $1,000 - 7 points daily
Each cell will have a different dollar value in it. I've tried so many different ways to do this IF factor that I have confused myself and spun in circles. Can anyone help me with this? I am about ready to pull out my hair, and really don't want to have to go in and manually do this daily for 30 reps.
I am having a problem getting a number to round correctly in Excel. I know I can use =ROUNDUP(x,x) to get it to the nearest whole number but I also need the rounded number to be a factor of another number like you would use in =MROUND(x,x). The problem is the MROUND function also rounds down. As an example if I have the number 12.5 and I want this to be rounded up by a factor or 4 I would get 16.
View 8 Replies View RelatedSee attached file. I am looking for a macro which can copy/paste down a value (column A) by a given factor (column B). The result should be a list of all values (column C). The amount of values and factors is unknown (the attached example ends at row 5).
copy.xlsb
Which formula should I use to look for the present value factor if I have the interest rate and the # of periods. attached file.
I have two factors that both change, resulting in two unique values. I'm trying to determine which portion of the resulting delta is attributable to each factor. Example:
Hourly Cost Run Rate Piece Cost (Hourly Cost / Run Rate)
$105.00 4,500 $0.0233
$126.10 3,000 $0.0420
Two unique results titled Piece Cost = Delta
$0.0420 - $0.0233 = $0.0187
How much of the $0.0187 is attributable to the change in Hourly Cost and how much of the $0.0187 is attributable to the change in Run Rate?
I got a list of products and a price list. How to obtain a total for a given subset of products using a formula? Kind of sumif multiplied by a vlookup...
View 4 Replies View RelatedI want to group N elements which is a factor of 7 into groups of 3 randomly. For example, I have 21 elements:
ZSW
PMH
NYV
JTX
DEF
CRK
AOI
I want to group them into 6 more sets. Now, all in all 7 sets. Group them into other sets without the element repeating in the row it has occupied before. For example, for letter P. "P" has occupied row 2 in set 1, it cannot occupy row 2 anymore in other sets. Therefore, each element must have occupied row 1-7 in all the sets.
See attached excel file for the example output : grouping7-final.xlsx
Here is a table with values from Factor1 to Factor 5. Underneath that, for a specific index, I can mark Y or N for factor values. For instance, for index 1, Factor1 and 2 is marked as Y. The goal is to calculate total factor based on variable and factor values. In this case, variable value is 6, factor 1 =1 and factor 2=1.25). So Total factor = 6*1.25*1.5 = 11.25. If all factor values are marked as N, then total factor = variable value (Example is for Index 3)
How this can be implemented. I tried using COUNT function to count the total number of Y but that works only if all factors have same values.
Table_factors.jpg
I am currently working on a spreadsheet that records data from 11 different locations, with each location having it's own page within the workbook.
All of the pages are identical in terms of how the data is stored, the only difference is literally the name of each page (i.e. the location).
What I would like is to have a master page that then pulls through the relevant data, dependent on which location I choose from a drop down list on this page. As each individual page is identical in terms of lay-out, one formula will work for each page, with the only obvious exception is the page reference embedded in the formula. I know how to do a standard lookup for various figures, but I can't figure out a formula that would work with 11 different options... Is there any way to accomplish this?