Creating A Function To Sum Numbers
Jan 7, 2010I am trying to create the function below:
View 3 Replies
ADVERTISEMENT
IF Function - Can Combine AND / OR And Creating (at Least) Function
Jun 24, 2013I have the following function:
=IF(AND(I17>=$I$1,O17>=$O$1,Q17>=$Q$1,F17
Creating Random Numbers
Jun 2, 2006Im an using excel to run a simulation of demand over a period of 200 days. i have an average daily demand of 7.75 and a stdev of 2.6. Is there any way to generate random numbers (for daily demand) based on this data?
View 3 Replies View RelatedCreating Ranges Of Unique Numbers
Jun 3, 2014I have to create ranges of unique numbers that follows the below rules.
1) Have to start with 3 standard digits (ie 101 or 102 etc)
2) The total length of the is has to be 10 digits (ie 1010123456)
3) The second part (the last 7 numbers) must be unique!
4) Can create a lot of numbers (more that 5000 rows)
5) thats all with the unique numbers
Continue,
Somewhere in the sheet has to be a search function to find where a specific id is located, make it red and copy/cut in to another sheet.
Creating List Of Remaining Numbers
Jan 10, 2007I have a list of numbers . Several numbers are pulled, based on criteria, and then I need to create a list with just the remaining numbers.
EXAMPLE:
A) Numbers 1 - 500, defined by name (So I can INDEX them later)
B) Pull out numbers 47, 3, 143, 224 (based on certain criteria)
C) By INDEXING the field, create a list of numbers 1 - 500, omitting the above numbers.
I have no problems with steps A & B. I can't do step C.
Creating A Range Of Numbers Through Formula
Jun 29, 2006I cannot find the right catagorty I need to use to word this. I know there is a formula out there. I basically have 1k numbers I need to add into Collumn A of a worksheet - the numbers are from range 34-2501 through 34-3500. How can I get excel or VBA to do this automatically?
34-2501
34-2502
34-2503
repeat til
34-3500
Also, how to make it user friendly to create the same process over and over with a different set of numbers... meaning make it so I onlt have to input the range or numbers over and over. if it is an easy task I do this allot
Creating IF Function For Blank Cells
Dec 11, 2009first post, i'll try to make this short and to the point. i have a file that has single invoice numbers, invoice dates and invoice totals. the line does not have a vendor name. the vendor name is above the invoice lines. (see spreadsheet).
Example:
D2 = Vendor
Lines 3-10 are individual invoices
Basically, I need to setup a formula that says if a cell in Column D is blank, then select the closest cell above the blank cell. I have 65000 lines with thousands of vendors. In the end, each invoice line will now have the vendor name that it is associated with.
Creating Unique ID Using Concatenate Function
Mar 26, 2014I need to create unique IDs for all of my stock. I would like to use the concatenate function so that the first letter of each products type is taken and then joined onto a unique number. Im not sure how I will do this I was thinking that the formula looks at the largest value and adds 1 so that there are no duplicate numbers added to the first letter of the product type. E.g. the first mother board added will be "M1" the next will be "M2" as the formula has found that M1 exists and has added 1.
View 3 Replies View RelatedCreating A UDF That Incorporates The Indirect Function
Apr 26, 2008I would like to create a UDF that incorporates the Indirect Function. So if this is possible, I'd like to put in any cell..... =Tev(A2,D6)...... "Tev" would replace the Indirect function in the UDF script. In cell A2 would be the name of the sheet that I want to pull from. And Cell D6 would be the cell from the other sheet that I'd like returned.
View 9 Replies View RelatedCreating A Numerical Integration Function
Jul 25, 2008I'm a VERY basic user of Excel. I have a mathematics and programming background, but no knowledge or experience in VBA programming.
I'm wishing to create a new function as a VBA custom function procedure to numerically intergate an expression (such as 3*X^3 + 2*X^2), defined by a worksheet formula (not by data points). The five parameters required by the function would be in the form of cell references which point to (1) the expression to be integrated (such as the above example), (2) the integrating variable (in this example, X), (3) the lower limit of X, (4) the upper limit of X, and (5) a tolerance value for termination.
I've been trying to implement the Trapezoidal Method - but not a flicker of light so far!!.
I understand the Formula property of a cell may be used to get the expression to be integrated into the VBA code as text. Then the Substitute function may be used to replace the variable of interest by a value, and the Evaluate method may be used to get the new value of the expression. I have no experience in implementing these steps.
I've made extensive attempts to do the above, but without success so far. ("I started off with nothing, and still have most of it left"!).
I'm hoping someone may be able to assist me, or else point me to a relevant area of the forum (or elsewhere), by demonstrating the methods and techniques needed to achieve the function I'm seeking. Perhaps it has all been done before, but I haven't located it.
Creating Array Of Numbers With Specific Definitions?
Feb 3, 2014Lets say I need an array of 100 numbers from zero to 100. I want a mean of 75, and SD of 12. Or the same data based on a skewness and kurtosis value. Can I do that in Excel?
I'm just generating fake datasets for my stats class to analyze.
Edit: I found that =norminv(rand(),,) will work.
Creating Sum Of Numbers Based On List Of Names
Jan 27, 2013I am a small business owner with little experience in Excel and I have been trying to set up a worksheet that will organize my products and finances. I organize sets of various products and sell them in bulk to customers. I was trying to recreate this in Excel so I can figure out the total price for each of these sets and easily edit them.
What I've been trying to do is to take a list of the items and assign a price to each of them. This is on a sheet titled "Prices." Here's an example:
prices.jpg
On a separate sheet titled "Sets," I created lists of items included in each particular set. To easily add or change the products I used drop down menus.
sets.jpg
I would like for the sum of the price of all the items listed under each set to add up automatically in the "Total" row. For example, in cell B7 it should display the number 10. Here is a list of the following formulas I've tried:
[Code] ........
Creating Two Columns Filled With Numbers And Alphabets
Dec 9, 2013I would like to create 384 by 2 table such as below. One column has series of alphabets (A-P) using 24 cells for each letter and other column has series of number (1-24) for each corresponding letter on left column.
A
1
A
2
A
3
...
...
A
24
B
1
B
2
...
...
P
23
P
24
I tried my best using my limited knowledge of VBA but macro fails by overwriting entire column in each loop (This code is for only one column, but I want to make two columns shown above).
Code:
Sub alphabetNumber()
Dim e As Long, f As Long
For f = 1 To 16
For e = 4 To 387 Step 24
Range(Cells(e, 2), Cells(e + 23, 2)) = Chr(64 + f)
Next e
Next f
End Sub
Search Function - Choosing And Creating Lookup
Jun 13, 2014I'm currently trying to put together a search function of sort onto an excel document to look for entries of "Y" based on the choices in a drop down list to populate a list of names that have a "Y" next to them. I've searched the net and read up on IF, VLOOKUP and HLOOKUP commands but I can't quite string something together. An example of what I am trying to achieve is below;
Raw Data:
type1type2type3type4room1room2room3room4
AndyYNNNnYNY
BillYYNYYYYY
ChrisnYNYYYYY
DaveYNNNYYYY
EddyYNNNYYYY
FrankYYNYYNNN
GilesYYNYnYNY
HarveynYNNYNYN
IannYNYYYYY
JohnYYNYYYYN
KyleYYNYYYYY
Search drop downs:
Typetype1
Roomroom1
End result (based on the example of Type1 and Room1):
Bill
Dave
Eddy
Frank
John
Kyle
Excel 2010 :: Creating VLookUp Function In VBA
Dec 28, 2011Im trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.
My Data Looks like this:
List 1Weight 1List 2Weight 2List 3Weight 3List 4Weight 4a2.00%j20.00%a14.00%p2.00%b4.00%k32.00%d2.00%y3.00%
c7.50%e10.00%g14.00%u6.00%d8.00%d15.00%h20.00%h7.00%e9.00%q13.00%y45.00%f8.00%f20.00%r5.00%q5.00%
d9.00%g35.00%n5.00%t10.00%h10.00%r14.00%i4.50%w15.00%b0.18Z0.04S0.04Total100.00%Total100.00%Total100.00%100.00%
Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.
What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.
So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.
The code I came up with so far looks like this:
Function AK_Overlap_Go(x, y)
Dim Temp(1 To 2, 2 To 1)
Dim x As Integer
[Code]...
One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.
Creating Auto Costing Sheet For Pre-Sales Function?
Oct 9, 2013I am trying to create a spreadsheet that uses a selection criteria based on different sheets within a workbook to output a costing quote, anything i can use as a template? I will post the sheet that i am working on that I need to combine into a working book.
View 2 Replies View RelatedCreating A Function Or Macro To Replace Data Between Cells.
Jun 2, 2009I am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.
What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.
Is this possible?
Excel 2010 :: Creating Multiple / Repeatable Lists Of Random Numbers
May 19, 2014I am trying to create challenge/response cards. Each card will have a 5-digit number in the upper-left and lower-right corner along with a series of rows containing a line number and 3 pairs of challenge/response words.
Rather than have have copies of these cards, I want the central office to have a macro-enabled workbook wherein they can enter the two numbers from the card and have the challenge/response matrix regenerate on a worksheet.
So, essentially, I want the challenges random number generator seeded from one of the 5-digit numbers and the responses random number generator seeded from the other 5-digit number.
I am trying to use
Code:
Randomize(Seed)
where Seed is one of the 5-digit numbers.
The problem appears to be that, no matter what I use for Seed, the RND(-1) function is not resulting in different numbers.
I am using Excel 2007 and Excel 2010.
Function To Test For Whole Numbers
Jan 22, 2009I have a resolution calculator that I am working on.
You put your original resolution in say 1440x1080
then below you put in any one of the new target resolutions.
Place 720 in the height and you get a message that says
"960 is your new matching Width resolution"
Id like to follow up on that with an if statment that test to see if the cell is blank if it is then null, if not then test 960 in this case to see if 960/16 = a non decimal number.
So in the cell to the right of that sentence it would return the result
"and 960 is 16 pixel safe" something like that.
I can probably figure out how I will handled the cell arrangement, numbers, and text I just for now need to find a way to do a test on whole numbers and return a text value (my guess is an if statement)
Function For A Range Of Numbers?
Jan 22, 2014I am looking for a function that gives a range of numbers a score, here is what i need:
if A1 is between 6&15 B1=25 if A1 is between 16&35 B1=15 if A1 is between 36&65 B1=5 if A1 is 66 or more B1=0
Can You Use MAX Function On Numbers/text?
Dec 21, 2009I am trying to use the "MAX" function to find the max temp on a particular day in a week from 7 separate sheets. The problem I have is one particular company autopopulates their temperature block with the temp and degree symbol: 46°F instead of just 46. The max function is thrown off it appears by the symbol and letter. Is there a way to get the max function to only look at the numbers? I'm also open to the option of autoconverting each temp to a number on the master sheet and letting the "MAX" function search through those if that's possible.
View 10 Replies View RelatedSubstituting Numbers With IF Function?
Oct 18, 2012telephone codes in SA begin with the international code of 27 which when followed by a mobile number drops the zero from the Mobile numbers. eg 27722732415 without the SA code should be 0722732415. i want to remove the 27 and substitute it with a 0 ONLY if it occurs at the beginning of the number and not when it occurs in elsewhere within the telephone number
View 8 Replies View RelatedSpell Numbers Function
Jan 13, 2008I had entered a spellnumber function in Excel 2007 and it was working fine but then all the sudden it disappeared. I would prefer a function to convert numbers to text without doing visual basic code.
View 7 Replies View RelatedIF Function True Not Recognizing Numbers
Sep 22, 2009I am working in two different files. If number "40556" on worksheet B is showing on worksheet A, the function is to state "TRUE", but it's stating "FALSE". Both files' numbers are listed as GENERAL under FORMAT CELLS.
View 12 Replies View RelatedLimiting Numbers Of Arguments In A Function?
Jul 26, 2009there is limit of argument in a function that can i put. for example i am not able to figure out why this function does not work
View 2 Replies View RelatedUse The Countif Function To Look At That Range Of Numbers For
Jul 18, 2006I have a range of numbers between 1 and 25 in 50 cells.
How could I use the countif function to look at that range of numbers for
values between 5 and 10 and count those values that meet the criteria of
between 5 and 10?
Or if there is another function to use, let me know.
How To Use VLook Up Function To Look At A Range Of Numbers
Oct 26, 2012is it possible to use the VLook up function to look at a range of numbers - I.e.
30-40, answer = 15%, I have tried 30-40 also 30,40 but none seem to work?cualte?
Sum Numbers For Each Month By Sumif Function
May 4, 2007Column A have random dates and column B have numbers. How can sum numbers for each month By sumif function?
View 6 Replies View RelatedExcel 2010 :: Random Numbers In One Function
Jul 22, 2012I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
Incrementing Serial Numbers With Send Key Function?
Oct 3, 2013I am trying to get Excel to write a series of incremented serial numbers to an external program.
I need 10 per line, then for the code to send ENTER and start writing on the next line. Following is my current code:
For i = 0 To qty - 1
SendKeys prefix_sn & next_sn + i & ","
Next i
