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 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 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")
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 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.
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'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.
I am trying to do a dynamic sumifs that allows the user to use data validation lists to select 5 items to query. This is the easy part.
The hard part is that the amounts range to sum goes across 6 weeks and I want the answer to also take account of another validation input which specifies up to what week the range should be.
I have done a simply if statement in row 8 that flags weeks relevant to input cell B6.
How do I do a formula in say cell A16 that does the sumifs and acknowledges that the user has requested a dynamic range to sum?
Sheet1ABCDEFGHIJK1RegimePost 20052TypeBudget<<<<- Dropdowns3Prod AreaLoans4ChannelRetail5Pro/ReReactive6Week18-May-1178Flags ->1110009Regime 2TypeProd AreaChannelPro/Re04-May-1111-May-1118-May-1125-May-1101-Jun-1108-Jun-1110Post 2005Budget Loans Retail Reactive 36942041320736540011Post 2005Budget Cards Retail Reactive 38423444021726636212Pre 2005Actual Mortgages Tele Proactive 35739840536923740113Pre 2005Actual Loans Direct Proactive 3213872554552254621415Answer16Excel 2007
I've got this small (?) problem here. In a range of about 150 rows and 30 colums, I have to edit the cell formula, i.e. add something to the existing formula. For instance, in all the cells in column C I need to add " + D9" to the existing formula, in all the cells of column D I have to add " + E9". Is there a way to do this in VBA ? I would already happy to do it column by column, but I'm really reluctant to edit 4500 cells manually.
View 2 Replies View RelatedI have a cell that uses list type data validation. I need to be able to update the list of allowable values via a macro and would greatly prefer not using a range in excel.
I've been able to create the string that I want as the formula but cannot change the formula. I'm not very familiar with this aspect of excel macros (data validation)
I have an array formula i use to sum data by specific criteria:
=SUM(IF(Input_Sheet!$A$2:$A$1000>
=DATEVALUE("01/04/2011"),IF(Input_Sheet!$A$2:$A$1000
=DATEVALUE("??/??/????")
So that it searches for any date and replaces with the new ones, but obviously the use of quotation marks with the wildcards makes this difficult in VBA.
How to link the cell values in with the "Replace with".
Code:
Sub findrep()
Dim target, cell As Range
Dim i As String
Dim k As String
i = "Input_Sheet!$A$2:$A$1000>=DATEVALUE("??/??/????")"
[Code]....
How to edit a formula in a cell using vba code.
For example, the cell A1 in sheet "sheet1" contains formula "=sum(Sheet2!B1+Sheet3!B1)" and I would like to use VBA to edit/change it to "=sum(Sheet2!B1+Sheet3!B1+Sheet4!B1+Sheet5!B1)" and so on
I have a template that I have hidden the formulas but allowed editing. Is it possible to create a pop up box for the end user to use to edit a cell with the hidden formula? This is a budget template with already set up formulas but the end user needs to be able to make edits but the end users are beginner excel users and if they double click on the cell the formula will automatically delete leaving the cell blank. I need a way around this so they cant view the formula but only change the data if they really mean to change it.
View 3 Replies View RelatedI have this huge formula and I want to run it in one row, across 350 columns:
=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)))
The only thing I need to change from one column to the next is the four places where it says ROW(3:3). I need that to ratchet up as it goes across to the next. The cell to the right should say:
=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)))
Then the next cell to the right should say:
=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)))
etc., etc.
How can I fill across so those go up and I don't have to fill in those four numbers 350 times?!
Have a macro that copies a formula from each of 100 workbooks to a new workbook. I want to display these formulas as text and want a macro or someway to display these cells as text. I have tried to record a macro that presses the F2 key, the home key and the apostrophe. This works for the one cell but provides the following macro that does not work for anyother cell.
ActiveCell.FormulaR1C1 = _
"'=VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)"
Range("B3").Select
I have tried numerous versions of macros I found. Most get the same error of; Range object error or script error.
Here is the formula: VLOOKUP(C$13,VESSELS_DATA,ROW(30:30)-12)
What I would like to do:
1) double click to on the cell with the formula
2) goto the worksheet "DATA_TABLES" that contains the Named Range "VESSELS_DATA"
3) go down the rows until the c13 is matched
4) offset column the same as in the above formula "Row(30:30)-12"
Is there a way that formula links in spreadsheets can use environment variables to reference formulas in other spreadsheets/addins?
For example, I have a spreadsheet that imports templates which call functions in an addin. The spreadsheet, templates and the addin could be installed into either the 'Program Files' or the 'Program Files (x86)' folders.
I am experiencing issues with the formula references when moved between 32 and 64 bit computers since Excel seems to store the full path of the addin in the Excel formula (even though it hides it when the reference resolves its path) in my templates. I would like to update the references in my templates so that they use an environment variable (set by my software installer) to always know the correct path of the addin.
In Excel 2003, spreadsheet links are edited via: 'Edit'->'Links'
In Excel 2007 and higher,'Data'->'Edit Links'
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] .....
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?
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 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 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.