I have started to develop my own userdefined functions for complex analysis. I have some problems, but I believe they will be solved soon.
The arrays are defined type complex with the
Type Complex
re As Double
im As Double
End Type
sub refleks
dim sa as complex
dim akd(150) as complex
.
.
..........................
I'm trying to understand how complex numbers are handled/processed in Excel.
As related to my application, an interesting exercise would be to use Goal Seek w/s command to find the roots of the equation:
X^2 + 4 = 0
setting the (rounded) value in cell A2 to 0 by changing A1
A1:: 1+i
A2:: =COMPLEX(ROUND(IMREAL(IMSUM(IMPOWER(A1,2),4)),6),
ROUND(IMAGINARY(IMSUM(IMPOWER(A1,2),4)),6))
Obviously a conventional or direct use of Goal Seek wouldn't work since Excel treats complex numbers as text.
Perhaps, one should use Goal Seek twice in this case:
first: find the coefficient "a" for IMREAL(A2) = 0
second: find the coefficient "b" for IMAGINARY(A2) = 0
and the root would be "a+bi".
Put serial numbers against row like above either by the help of formula or either by VBA code.
SrDePNo.Truck No.Vehicle Type
16384LZR7793SM
46385TLC964C
56386LES7540T
66387LES4982T
76388LES 6509T
86389K4470T
96390LWC3070SM
-6391LWC3070SM
106392AJKA 7595T
116393RIC8879T
126394W1649T............
I have created an index of documents in Excel 2003. I have one "master" worksheet containing a list of documents and their corresponding version numbers. On another worksheet, I record details of changes to the documents and hence changes to version numbers. This list will constantly grow over time, and in any order depending on the documents to be changed. However I want certain fields to automatically update themselves, namely the version number.
So for example, I have a column to evaluate if document changes are required, yes or no.
If "Yes" is selected, I want the version number in another cell to automatically update to the next in sequence (add one). So if changes are required, V1 becomes V2 etc. Now the next version number will depend on the previous version number, so before it decides if it adds one, or not, it needs to lookup the value of the current version number in a range. Again, as its a list of different documents this can only be done by first looking up the unique document number.
So to summarise, I need it to lookup the unique doc number in a range and find the one that has the highest version number against it, and return its version number. And then encompass all of this with the If statement to evaluate whether or not the version number needs to add one.
I have the following scenario
A1 - 0.50
A2 -
I am trying to extract values from a text. I used macro to solve the problem. I was able to extract the numbers however i am trying not to extract all of the numbers in text. For example as you can see below, i am trying to get only 22.99 The only unique thing here can be $ sign i believe. I need to put a criteria that selects the number right after $ sign and extracts 5-6 decimals after that.
**work lamp/desk light led;orion8879 final price: $22.99 (store)**
this is the text in a cell and i only need 22.99 not 8879)
i have this formula that is complex ( atleast it is to me ) and i need to add more to it. Here is the formula:
=VLOOKUP(" "&LEFT($A5,FIND("[",$A5)-2),'7E'!$C$2:$D$682,2,FALSE)*0.99
What i am needing is, when it gets the price from another worksheet, if that price is below 0.04 that it makes it 0.05
I wish to make a transposition using dictionary. I wanted to take a script jindon, but I have difficulties to adapt.
VB:
Option Explicit
Sub test()
Dim a, i As Long, ii As Long, w, rng As Range, cpt As Byte
a = Cells(1).CurrentRegion.Value
[Code] .....
The formula below works apart from the first bit which i want to be if F23 says 'fail' then i want the cell to say 'no dissertation' but if f23 says pass then i want the formula past that bit to be put into action.
=IF(F23="Fail",0)*IF(COUNTIF(B22:B45,"Dissertation")=1,IF(VLOOKUP("Dissertation",B22:C45,2,0)>39,LOO KUP(F26,{0,40,50,60,70},{"No Dissertation","3rd","2:2","2:1","1st"}),"No Dissertation"),"No Dissertation")
isolation123
I am an experienced Excel user, but it does not seem possible for this formula to be written all within one single cell. The SUM combines rounding a known value, followed by calculations, and subtracting a rounded number with modified calculations.
This is the formula which does work, although I would prefer for the sake of accuracy (and succeeding in the task of making the formula do exactly what I wanted it to do!!):
=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%
The value of T9 is 2283.3 recurring.
There are two main parts to the SUM:
=ROUND(SUM(T9/1.5)/5,0)*(5)*(6.5%)+(26.25+15.81+1)
...and:
+SUM((T9/1.5)*(6.5%)+26.25+15.81+1)*19%
The challenge is to insert another:
=ROUND(SUM(T9/1.5)/5,0)*(5)
in replacement of:
=SUM((T9/1.5)
I'm aware that the use of brackets differs between the two sections of the sum, but it is neccessary to allow the correct answer.
Is this too much for one poor cell to handle?
I am creating a complex formula with UDF, this Hlookup is part of my complete formula. Meanwhile, I am working on this piece, not Sure what I am doing Wrong
I try to conver "MonthCurrent" Variable in a Year because the MonthCurrent is 06/01/2014, so will be Year(6/1/2014) = 2014
My Range called "DailyRates" is based per Year = 2014, 2015 ......
[Code]......
The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report.
Then, I need the following logic as VB macro.
for row = 6
F6 = (G6*G4) + (H6*H4) + (I6*I4)+...+ ("max_col:6" * "max_col:4") / (G4+H4+I4... "max_col:4")
The function LOGEST fits to the equation y=b*m^x.
Does anyone know if more complex functions can be found in Excel?
Ideally I would like to fit to y=b*m^(x-a) + c. If anyone has come across a method, or some code, which allows this.
I am trying to summarise training data in a spreadsheet as per the following layout:
A B C D E F 1 Trainer name # of trainees # of assessments Total hours Avg hrs per trainee Pass rate 2 Peter
3 Paul
4 Mary
The intention is to have this summary fixed on one tab, with the raw data being dumped into a second tab alongside the summary (named “the data”).
The data for the summary comes directly out of an Access database and is in the following format (I’ve put an X against the headings that aren’t relevant to the summary):
A B C D E F G H I J K L M N 1 X X PersID Name X X X X X Trainer Assessment X Result Hours 2
12345 Jimbo
Peter Excel
Competent 2 3
54321 Nelson
Paul Access
Not yet Competent 3
Notes:
-The data can be up to 2000 rows long
-There will be multiple entries for each trainer of course, and multiple entries for some trainees (i.e. they will complete several training types)
-Result can only be Competent or Not Yet Competent
So, of the 5 fields I need to populate for each trainer, I’ve currently only got the following nailed:
# of assessments: =COUNTIF('The data'!$A$2:$A$114,A2)
Avh hrs per trainee = D2/B2
These were the easy ones.
What I’m having trouble with is the following:
For the #of trainees field, the calculation must determine how many people were trained by each particular trainer – so only unique entries should be counted (PersID could be the unique identifier).
Total hours will also need to be linked to the individual trainer – in other words, how many hours in total has the trainer spent training?
Pass rate is another tricky one – for each trainer, I’d like to see the percentage of trainees deemed competent out of the total number of trainees they trained.
I have searched for awhile, but I am stuck on an equation that I could use help on. A little background, I am developing a staffing profile, which I previously was using pivot tables and then using a "=getpivotdata " forumla to pull the data. I then found the conditional sum formula and like it's capabilities as I wouldn't have to worry about human error if I forgot to update a table or if the data in the table wasn't updating correctionly. I am doing a 2 entity conditional sum but now I need to incorporate a simple if statement as well. What I am trying to do is add another formula stating that if the formula comes back with zero data, i would like to make the answer blank For example ... =if(D3="","") . The reason I need this, is I have a tab for each month. As it is Aug now, I have actuals spend until July but don't have anything yet for the remainder of the year. When I am calculating my averages for the remainder of the year, I don't want the data to be scewed by all the zero's. My question is, is it possible? I tried to add it and did the CSE trick and got an error message. I thought you guys could help. I hope I was clear. Please let me know if I was not and I can expand. Below is the formula I would like to add another if statement.
=SUM(IF('Jan'!$D$3:$D$1500="TD",IF('Jan'!$H$3:$H$1500="Supply Support",'Jan'!$AB$3:$AB$1500,0),0))/H$2
I have the following template: -
Rows C5:N5 = Months Jan - Dec.
B6 = Finance
B7 = Marketing
B8 = HR
B9 = IT
B10 = Operations
B11 = Legal
B12 = Service
B13 = Procurement
Rows C6:N13 = Randomly Generated monetary values (#.##). Note that some cells could be blank, indicating zero.
B20 = Finance
B21 = Marketing.........................
I've got the following complex issue:
...................Trader1 Trader 2 Trader 3 Trader 4 Trader5... Trader20 (Horizontal)
Commodity1 .....0.....2.....0.....1.....1
Commodity2 .....0.....4.....0.....-3.....0
Commodity3 .....1.....0.....0.....5.....0
Commodity4 .....0.....0.....2.....0.....0
.
.
.
Commodity 36 .....2.....1.....1.....0.....0
I have 5 cells that are basically a drop down list of all the traders, so the user can select up to 5 traders.
Once the user selects up to 5 traders (lets say Trader 1, 3 and 5 for our example) I have the following requirements to fulfill:
Displaying all commodities that have non-zero, non-negative values associated with those traders in a list somewhere.
For our example, this would then display:
Commodity 3
Commodity 4
Commodity 1
to convert a complex Excel file to a Web App (usable in the browser of a iPad) which has 15 sheets and some input.I tried every javascript/jquery library, tried to hire a freelance, asked for a quote everywhere, tried add-ins, etc. Nothing seems to work. Manually, well, it is pretty impossible within 2 days.
View 3 Replies View RelatedUsing Excel 2007 the below code causes Run Time Error '16', Expression too complex.
It works fine in Excel 97, 2000 and 2003.
why this would now cause an error?
My search on google only refers to this error in relation to charts.
I am working between two worksheets. In one worksheet I have a list of
account numbers. In the second worksheet I have the same account numbers in
a different order. What I am trying to do is, If the account number from
worksheet one is found in the column with the account numbers in worksheet
two, I want to display the matching title for the account number that is in
the same row but different column in worksheet two.
So essentially (if acct # from column x in wks 1= an acct in number in
column m from wks 2, display the corresponding title in the same row of the
matching acct # found in column m). Is this possible?
I have a complex request which I think can be done either via vb or a macro, not sure which is best?, will try to explain:
The table shows a list of items that make up various BOM. there are quantities of each item required at the different BOM level
0 = Top level
1 = Level Below
2 = Level below that etc, etc
If the top level (0) has a quantity greater than 1 then all the items at the lower levels should be multiplied by that quantity
THEN move to the level 1's and whatever the quntity is in the level 1 field, multiple all lower levels by that number
if the quantity fields are blank, then always multipy below by 1
This needs to continue on down the chain of levels
So basically you end up with the correct quantites of each item at each BOM level.
I am attempting to match data entered into two cells. The data input goes into Worksheet 1. In Column A Row 1 the user inputs data... Example: 5. Then in Column B Row 1 the user inputs separate data... Example: B. I would like the result returned to Column C Row 1. The lookup would be on a table in Worksheet 2.
Worksheet 2
A B C D E F G H I J K L M N O P
1 2 3 4 5 6 7 8
A Pig A Cat A Dog A Fish A Ox A Bat A Six A Pie
B Pie B Six B Bat B Ox B Fish B Dog B Cat B Pig
C Fish C Ox C Cat C Six C Dog C Pie C Pig C Bat
Therefore the Entry 5, then the entry B would Return: Fish
So it's a lookup and match Column A-P. Then using that match lookup Row 2-4 for the second match. Then Return the data from the cell next to it.
I am trying to generate a formula for formatting text that will remove all spaces, comma's etc. Examples as follows:
The Bank Job -> the _bank_job_small.gif
Picture This! -> picture_this_small.gif
War Games: The Dead Code -> war_games_the_dead_code_small.gif
Chacun son combat (Never Back Down) -> chacun_son_combat_small.gif
I've figured out a formula that works but it seems overly complex - it uses multiple SUBSTITUTE, SEARCH and REPLACE functions. Is there an easy way to remove a range of symbols i.e. ! : ( ' ?
I've tried some of the basic formulas, but this is beyond me.
I have table A of 500 rows and need to compare it to table B of 2000 rows.
A
B
C
D
E
1
D6 - 1.01
Table with PC
3
Yes
2
D6 - 1.01
Bookshelf
4
No
[Code] .......
For example with these two, from the second table to the first. IF column B equal to column and the corresponding column C equal to the corresponding column C, return for example "yes". Then I need to check the same thing for the corresponding other columns, but basically everything would be tied to B and C. The problem is the second table, larger one, is basically all over the place, I could manage to do this if we would be talking equal length tables, but I do not know what formulas I could use to check further down a row once we find an equal value.
I am having a difficult time with a look up. It would be very hard to explain so I'll attach a copy of the section of the worksheet that the problem lies on with comments so you can see whats going on. The problem there is a numbered list with a reference number i can't seem to figure out a lookup that will look in the chart above and find the row associated to the reference number and according to how many before it have that reference number find a secondary reference number listed in the column above. The attachment should clear it up.
View 4 Replies View RelatedIn the attached sample file, one can see the setup of my data. I'm trying to reference the row number of a certain cell in a matrix with many cells of the same value. I have different Headers of each row, which should make it possible to use INDEX, MATCH or VLOOKUP. but i don't seem to be able to get it right.
Description.I need to return the "class" och each of the capability elements, i.e. If the capability in "Soups" is marked ("X"), at class 3, i need the integer "3" to be shown here.
create a unique scoring system on a set of given criteria that enables an even split in work for team members. so i.e. work comes in, and depending on the criteria of work its then passed to the relevant team member based on points. At the moment work is allocated A-Z which is working out unfair on team members as some are getting more/less work than others...a points system will enable a even split. so if a piece of work scores 30 points it goes to a junior member, if a piece of work scores 80 points it goes to a senior member and so on...my problem is how to link the points and criteria...Im guessing joint vlookups will be needed.
the only other way i can think a system is similar is like a fantasy football system...points based on criteria.
upon further consideration I thought that it would be best to probably do verything in two passes or maybe not see below macro to get an idea of how the end process should function
View 4 Replies View RelatedI really need a validation code for Cell B15. I realize that a macro could do this, but a validation code is what I really would need:
Cell B15 can only allow at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):
I
I I
I I I
IV
IA
I IA
I I IA
IVA
or (some combination examples):
IA & I I I
I I & I I IA
I VA & I IA
If the user fails to meet these requirements, then he should get an error message telling him to try again.
I'm basically creating a scoring worksheet, where there are a list of questions. Each question has a weighting as some are more impotant than others. Each question also has a range of answeres as some may not be clear yes/no answers. Also not all questions apply to all assessments so some may be left blank.
I basically need a formula which does the following to produce a total score:
[Total awarded score] / [Total available score of questions scored]
which equals
[(score <> "" / range)*weighting] / [if score <> "" sum weightings]
I've attached an example document where I think I'm getting close, just not sure if its possible.