I have a lot of lists of integers and I want to sum their Xth power automatically, for X = -3, -2, -1, -1/2, 0, 1/2, 1, 2, 3, 4, 5, 6.
For example, take this short list of integers: 1 2 3 4 6 12
The sum of their Xth power must be: 1^X + 2^X + 3^X + 4^X + 6^X + 12^X
How can I do that?
A user would input 4 integers.
How can I generate a list of all possible combinations of the integers?
I maintain a number of models requiring use of multiple OLAP Cubes in the same Excel workbook. I'm now using Excel 2007.
While I don't get this problem in Excel 2003, in Excel 2007 the Cubes often lose their connection to the source file. I'm given the error:
"Initialization of the data source failed. Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database."
When I attempt to recreate the connection, or select a new connection pointing to the same OLAP cube I'll often get the same error, but not always. I've tried using a separate connection, pointing to the same OLAP Cube and this also does not solve the problem.
how I can resolve this issue? Or do I just need to move my models back into Excel 2003?
How would I add the squares of cells A1:ZZ1 or A1:A1000, without having to do
=SUM(A1*A1+B1*B1+C1*C1... etc
This is a basic example of what I actually want to do, but is essentially the same problem.
I need to do a Sum of the Squares of the first 'n' Natural
Numbers. Something like:
function_name(n)
If 'n' is 8 say, then the function would return 204.
Thats 1*1 + 2*2 + 3*3 + 4*4 + 5*5 + 6*6 + 7*7 + 8*8
Likewise if 'n' is 6 say, then the function would return 91!
I'm trying to do a sum of squares calculation, between and within groups, across multiple columns.
I llike this to occur automatically when I input the data. Where I've running into difficulty is calculating the mean of each group(without sorting and manually selecting). See sample of data below:
Typestdby_currentrx_currentNormal0.141116170.171218Rework0.140827160.170686Normal0.140360580.172524Rework0.136112870.167756Normal0.141427740.170232
Note that there are more columns of different parameters I've just inserted two for demonstation
I would like to:
1-Make Rows & columns 1/4" X 1/4"
2-Be able to enter dimensions into an input box that would then draw a square(or rectagle) with the inputed dimensions in INCHES.
Further to here where exactly in options do I delete all of these little yellow squares with script signs in them which are invading my spreadsheet?
View 14 Replies View RelatedIn a userform i have created an textbox. The user types some text in it and after clicking an OK-Button this text must be copied to a cell To allow multiple lines (enter = new line in textbox) i have changed the textbox property EnterKeyBehavior to True. The problem is that after copying this textbox1.text to a cell in see square blocks in the cell.
line1[]
line2
instead of
line1
line2
I use the following code to copy the text into a cell:
Private Sub CommandButtonOK_Click()
Dim TextboxText As String
TextboxText = TextBox1.Text
ActiveCell.Value = TextboxText
Unload Me
End Sub
how to avoid this [] (should be like alt-enter in a cell)
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies View Relatedi'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.
View 5 Replies View RelatedI'm currently working with 3 dropdown lists depending on each other using data validation and =indirect(). I arranged them like in a top-down approach where you have a mother-list and child-lists (categories-->subCategories-->subCategoryCriteria). The user should easily chose first a category then a subCategory etc... This part works perfectly
My question is: when a user chose in the dropdown list e.g. a category, a subCategory and a subCategoryCriteria and he now maually deletes the value in the highest level, which is the Category (mother-list), all sub level values like subCategory and subCategoryCriteria will stay. It shouldn't be that way, because it's not logical. If you turn a tree upside-down (top-down approach) and cut one of the higher positioned trunks, all lower positioned trunks will fall off as well, right? So, my question is: can I use a conditional formatting or a formula to delete or fade out the value in the lower lists when the higher list value is being deleted?
I tried to use If-formula in combination with indirect in data validation, like =IF(A1="";("");INDIRECT(B1))...just and example.
The value doesnt need to be deleted, it could also be automatically face out like white on white or something. It's just confusing for the user if he deletes the highest choice and the lower choices still stay.
I have a variable called "MinVal" which is an integer. Does anyone know the correct code for rounding this integer to the nearest multiple of 5 (or 10)? I need to do this in VBA, not on the Excel worksheet.
View 4 Replies View RelatedI am trying to add some numbers only if they are integers/numbers. My condition goes like this:
IF((rangeA)=Numbers,SUM(rangeA),"One or more values not available")
I came up with this formula, but it gives me an error.
=IF(ISNUMBER(P5;Z5;BL5;BN5;BP5;AS5;AV5;EU5;FI5;GH5;GK5;GN5;GX5;HE5;HR5);SUM(P5;Z5;BL5;BN5;BP5;AS5;AV5;EU5;FI5;GH5;GK5;GN5;GX5;HE5;HR5);"One ore more values not available")
It says I have given many arguments for ISNUMBER function.
The only way I can find under "Conditional Formatting" to highlight round numbers is to set a formula.
View 1 Replies View RelatedAccording to Microsoft in Visual Basic an Integer can contain -2,147,483,648 to 2,147,483,647.
Yet when I store 5,000,000 to an integer I get an overflow error.
I am using to rotate through different values of 2 variables. The macro was created by a friend of mine 2 years ago so I need to modify it. The macro is quite usable so I hope someone will benefit from it. The problem with it is doesn’t rotate through a range of integers but only from zero to some number. In other words it allows to specify only the right hand of the range through which it will rotate (for example, from 0 to 100 or from 0 to 20 for A). The second issue with it is that it adds a difference number (“dif”, e.g. 3) to the start of the second variable’s range and starts only from there (from 3 to 100 for B). The third issue is that it doesn’t allow to rotate through a third variable (“C”) as you will see in my example. I am working on a task which requires precise definition of the ranges through which the macro will rotate and also the ability to rotate through a third variable. Need to modify it so that all the three imperfections are removed from it? Or maybe there are ready made macros freely available which allow to do just that?
View 34 Replies View RelatedI have a set on 300 values and I want to sum the 290 smallest numbers. Is the a function or combination of functions that can give me that result?
View 3 Replies View RelatedI recently had a major system crash. Took me a couple of hot stops and starts to get my XP professional to behave. It seemed to sort itself out and all is now working fine except that is for excel 2010 part of office 2010 personal and business. I first installed Office 2010 in January this year and it has all worked fine. Now Excel spread sheets behave as follows - even those that were created before the system crashes mentioned above. If I enter integer 1 in any cell and press accept the entry becomes '0.01'. If I enter 1.0 the entry becomes 1 when accept pressed. I have repaired the Office suite. I have removed and reload the Office suite. I have tested numbers in Word and on the calculator and both work correctly - so my question is simple - How do I make Excel once again accept that a single digit should be taken at value and not convert it to tenths?
[URL]
Suppose you have 3 variant Arrays, named sArrPos1, sArrPos2, and sArrPos3.
What is the syntax for using an integer variable to specify which of the three to work with?
E.g.
[Code] ......
I want to work with sArrPos1 first, then sArrPos2, then sArrPos3, but can't hard type them.
I needed to convert PartNumber to a string using Cstr(PartNumber).
The following VB code searches an Excel spreadsheet for a Production Order and then looks for a Part Number in the corresponding row. The Production Order can be listed multiple times, but there is only 1 Production Order/Part Number combo. When searching for the Part Number, it will find letters, numbers with a dash and letter/number combos, but won't find integers.
For example, it will find ABC, 3500-01 and T1000, but not 200.
is it possilble to extract integers from a text string within a cell?
Eg.
Cell A1 - "ABC 123"
I want to extract the "123" from cell A1.
I have many text strings from which I need to extract the integers from.
The trouble is that each text string does not follow a consistent order, so I
cannot use text functions such as LEFT, MID or RIGHT to extract what i need.
I have a set of values as below from (r1,c1) to (r6,c1)
-2.1
-1.6
-0.3
0.3
1.5
2.9
I want to find the smallest positive value of the given values.(output should be 0.3) ....
I'm trying to round off my numbers to specific integers. Sorta like a step function (in algebraic terms).
For example, my first few integers are 0-8-13. I want: 0<=X<8, 8<=X<13, etc.
So far, this is what I have: ...
I need to declare a two dimensional array that will return the Row and the cell's string value. My only solution is to return the row as a string like this.
Public GlobalArray(2, 100) As String
How to get more control than this? Maybe I should just declare it as a variant but this might open it up for problems later.
I am experimenting with solver and have attached an example of my problem.
A3:A12 contains a list of numbers from 1 to 10. B3:B12 are my cells I wish to change to be either 1 or 0. If a cell in column B equals 1 then it multiplies the corresponding number in column A by 1 and puts the result in column C. Column C is summed up in cell C14.
My solver settings are below: ...
I have a comma delimited CSV file that holds about 2 millions rows of data (a lot I know but it's the only format I can work with unfortunately). One of the rows has some numbers in it that have lots of decimal places (like 3.908651901). I need to round that entire column - all 2 million rows - to whole integers.
The problem is that when I open it in excel it says something like "only 1 million rows shown". After more investigation I have found out that excel cannot handle more than 1 millions rows at a time.
My question is this: If I select the whole column and round the numbers to integers will that apply to the entire 2 millions rows? Or only the 1 million showing in excel?
Are there any other programs available that can handle 2-2.5 million rows and change the column to whole integers?
I have a linked text box and spin button on a userform so the user can enter text or use the buttons and I'm having trouble barring non-integer inputs! Here's what I have so far:
Text box = NumberBox
Spin button= NumberSpin
I've a big problem getting the Constraints in excel solver to use variables.
It seems that the only accepted variable type in the contraints are STRING variables. THe following simple example works AS LONG AS the variable apa is a STRING value and as long as it doesn't contain decimals....
So the question is. How to use varialbes in the FormulaText argement other than string values as integers?
I am attempting to multiply the value of strings/integers for an output in a function macro. I know it's a simple question, but a simple answer would be suffice. I'm in the middle of a massive brain fart...
View 6 Replies View Related