Defining Order Of Operations In Formula?
Mar 5, 2014
I have a formula that looks like this:
=12*NETWORKDAYS(C2,D2)-12+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)
This formula calculated the time between two dates (Start date in C2, End Date in C3) between the hours of 06:00 and 18:00 on weekdays. This is so I can work out how long an incident has spend within the group at the time when our service should be operating.
I was given this formula by someone on this forum a while back and i was new to excel and formulas so I just took it at face value since it worked. Now I want to disect it so I can use it in other situations where similar calculations are needed. I want to try to break it up into easier to understand chunks to try to understand what it is I need to change in order to do these related calculations.
NETWORKDAYS(D2,D2)
Is this trying to distinguish if D2 is a weekday?
MOD(D2,1)*24 Is this not the same as (1-D2)*24
NETWORKDAYS(C2,C2)*MOD(C2,1) Is this not the same as: IF(weekday(C2)>5,(C2-1)*7,0)
IF(NETWORKDAYS(D2,D2),....,....) What is the comparison here? if NETWORKDAYS(D2,D2) = 0 or 1?
Is this equivalent?
=12*(
NETWORKDAYS(C2,D2)-12
+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)
-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)
)
View 1 Replies
ADVERTISEMENT
Aug 16, 2008
I have "bashed into shape" a formula that involves 2 sets of parenthesis. After progressing beyond "#REF! or #NAME errors, Excel's automated Help suggested to add a comma, and the formula functions as wanted, I thought I was "Smart".
Later, when saving the formula to my `Formula Workbook', "Order of Operations" crossed my mind. - I think this formula is backwards, but `Excel Help' only suggested the addition of a comma? Maybe the all inclusive parenthes allowed the order to be understood?
The Formula: =AVERAGE((E56:AH56),COUNTIF(E56:AH56,">0"))
Q: Shouldn't the COUNTIF occur before the AVERAGE? Did the extra Parenthis automate the order of Operation within the Parenthis?
View 9 Replies
View Related
Jan 16, 2009
I am trying to write a formula in 2003. The basic premise is below and I'm having problems getting all the senarios to work out.
Cells:
E20 = Calc'ed amount
E22 = Min amount (Sometimes equals zero - No Min)
E23 = Max amount (Sometimes equals zero - No Max)
E24 = Additional amount to add
=IF(AND(E22=0,E23=0),SUM(E20,E24),IF(AND(E220,E230),IF(AND(E20>E22,E20E23,SUM(E23,E24),IF(AND(E20E22),SUM(E20,E24),IF(AND(E20
View 9 Replies
View Related
Jan 22, 2010
I would appreciate knowing how to fix this formula to do more than less than/more than answers. How do I have the following answers return for these birth year ranges? I have attached a sample file, with the formula in place. Thank you so much for any help!
1974-1991 = amateur 18-35
1900-1973 = amateur 36 and up
1992-1994 = junior 15-17
1995-2005 = junior 14 and under
blanks or zeros = unknown
View 8 Replies
View Related
Jul 31, 2007
I have 2 colums that represent the height and width of a paper edges. And now I would like to find a formula (somekind of an IF?) which would calculate automatically (taking account the values in these colums) which of these 2 measures make together ISO paper sizes A0 (84,1x118,9), A1 (59,4x84,1), A2 (42,0x59,4), A3 (29,7x42).
View 2 Replies
View Related
Nov 13, 2012
I'm trying to take a vba function and add a second if clause. Here's the function:
VB:
Function getdes(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRn Then
holder = holder & ce.Offset(0, 2).Value & vbLf & ""
End If
Next ce
getdes = Left(holder, Len(holder) - 2)
End Function
I want to do something like:
VB:
Function getdes(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRn If ce.Offset (0,-02).Value = "y" Then
holder = holder & ce.Offset(0, 2).Value & vbLf & ""
End If
Next ce
getdes = Left(holder, Len(holder) - 2)
End Function
View 3 Replies
View Related
Jun 17, 2006
Does excel contain a format of : "number of months"?
Example : I have 3 cells
A1 : should contain the number of months (3 months)
A2 : should contain a date with this format (dd/mm/aaaa)
A3 : should contain A1+A2
View 4 Replies
View Related
Jun 2, 2006
I'm attempting to conditionally alter specific column range values based on the variable 'tagrow'. From the example you can see that if 'tagrow' is 7 or 8, I want to divide the values in column C by 1000 and put them in column D. If tagrow was different, I will use another conditional statement with a different denominator.
In the current code, I get a type mismatch error. I have also tried dividing by an equal size column range full of 1000's, with no luck (matrix math anyone?). All I want to do is manipulate ranges!
'Initializing tagrow so we can test what train we are trending'
tagrow = Cells(12, 2).Value
'Only Propylene flows need adjustment in A and B trains, calculate everything'
If tagrow = 7 Or tagrow = 8 Then
Sheet2.Calculate
Range("D15:D115").Value = Range("C15:C115").Value / 1000
End If
View 3 Replies
View Related
Mar 7, 2007
I need to have the contents of cell A1 (a 3 component list) control a formula in cell E1. If A1 is "add", then E1 should be B1 x D1. If A1 is "delete", then E1 should be B1 x D1 x .6, and if A1 is "delete ilo", then E1 should be B1 x D1 x (-1).
A1 B1 C1 D1 E1
list qty. list price of C1 item formula needed
( lookup)
View 5 Replies
View Related
Jun 22, 2009
I'm try to put together a set of macros that perform certain operations within a file. However I've got a bit stuck on the following, and I can't see where I've gone wrong:
View 3 Replies
View Related
Jul 28, 2007
I want to copy several separate sections on a worksheet and place these sequentially in an array, then paste these in order into another worksheet.
I have written a macro to copy and paste between the two spreadsheets, however, this is rather basic as it involves straddling between the two and performing multiple alternate copy and paste operations, as only one section can be copied at any one time.
View 9 Replies
View Related
Oct 17, 2008
I am trying to make things easier for the next user by having one command button run the macros for the other worksheets (so they don't have to switch between worksheet)
However,
I get "Runtime error '1004' Application-defined or object-defined error"
View 8 Replies
View Related
Mar 30, 2007
I need a macro that will perform a set of oprations on all worksheets in a workbook. The names and number of worksheets will not always be the same. I have found several macros that look like they will do this, but when I try to add my code they don't seem to work.
View 9 Replies
View Related
Jul 18, 2007
I need to create a spread sheet that in Col A has 3 variables, each of which I need to triger 1)fill of that row, 2)different formula's in different columns within that row. Is this possible in excel?
View 9 Replies
View Related
Aug 8, 2007
To speed up copy and paste operations in a macro, I've heard that one can replace the following:
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").pasteSpecial
with
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
The second code should run faster as the clipboard is not used to store the copied info.
When I tried this, though, I didn't notice any improvement in macro running speed.
View 9 Replies
View Related
Jul 16, 2014
Let's say I have a column A with a list of dates like this:
02/01/14
02/08/14
01/01/14
06/15/14
04/13/14
05/12/14
01/13/14
04/01/14
06/01/14
I'd like to then write a formula in column B that tells me what number order the corresponding date in column A would be if column A were sorted in chronological order, broken out by month.
So the results would look like this:
02/01/14 0201
02/08/14 0202
01/01/14 0101
06/15/14 0602
04/13/14 0402
05/12/14 0501
01/13/14 0102
04/01/14 0401
06/01/14 0601
So the first two numbers of column B are simply the month, and the last two are telling you whether it's the first, second, third etc chronological date of that month, that is found in the entire list.
If column B needs to be a concatenation of a couple columns of formulas to get to that result, that's fine.
The whole idea behind this is to assume that the data in column A can not/will not be sorted by Excel.
View 7 Replies
View Related
Feb 15, 2012
I would like to create a formula that will select a 7 digit order number that begins with an 8 or a 9. For example in cell "AI3" I have the following:112972/8517223.
I would like cell "AH3" to capture only the number 8517223. Also, instead if that cell had 9000954 if would like "AH3" to have that number as well. In addition, if cell "AI3" was blank, I would like the formula to also search in columns "AJ3", "AK3" and "AL3" for a seven digit number that begins with a 8 or 9.
View 3 Replies
View Related
Nov 29, 2008
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:
I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?
Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...
Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).
Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.
View 9 Replies
View Related
Oct 3, 2013
In the above pic I need a formula that goes down column B, looks at column A, picks out all the different numbers from column A, orders them lowest to highest and separates them with a comma. The words in column A of course are ignored.
View 9 Replies
View Related
Feb 2, 2007
I have a series of operations to carry out and, while I can do the code for each individual one, how to declare the variables correctly and have the operations done in a series of Do/ Loops or For/Nexts. Especially the declaring of named ranges as variables. Also a bit uncertain of the best way to find and coy the match. I have attached a simplified version of the workbook, with explanations on it.
Basically what I need to do is loop through a series of named ranges and then loop through the names in each, match each name with a name in a master list (with a flag as an image), add an e-mail hyperlink to that flagged name and copy both to a new cell.
View 2 Replies
View Related
Mar 1, 2008
I need a way to re-order an excel worksheets columns based on another worksheet.
Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.
How can i reorder the columns without physically having to cut and paste the columns to match?
I have attached a sample spreadsheet.
You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.
View 3 Replies
View Related
Jan 10, 2007
When building complex and long formulas in excel which can not be auto
filled due to non progressive variables I tend to combine several cells
containing parts of the formula using the ampersand (&) operator.
E.g. B2=[A1&A2&A3&A4]
where:
A1=[=]
A2=[INDIRECT($A$1&"!"&"S]
A3=[8]
A4=[")]
The result will then look like this: =INDIRECT($A$1&"!"&"S8"), then I
copy all the values created by this method (it could be several
thousand)
and past them into the appropriate worksheet using: past special > past
values.
The problem is that in order for the text string to turn into an active
formula I have to go into each individual cell (F2) and hit Enter. When
I am working with thousand of cells this is not very feasible.
View 11 Replies
View Related
Aug 23, 2012
creating a formula to rank certain cells in reverse order.
Example
Cell Total Rank
B1 = 2456 3
B15 = 2369 2
B30 = 2547 5
B45 = 2141 1
B60 = 2471 4
Rank every 15 cells basically in reverse order.
View 9 Replies
View Related
Oct 18, 2012
I would like to find a formula that produces Names and $values in ascending order.
Example :-
Cell a1 =John Cell b1 = $70
a2 = Bill b2 = $100
a3 = Jane b3 = $25
a4 = Frank b4 = $10
a5 = Mary b5 = $60
a6 = George b6 = $30.
ANSWER to be
Frank $10......Lowest price
Jane $25
George $30
Mary $60
John $70
Bill $100.
View 6 Replies
View Related
Jan 15, 2014
I have a big file here : [URL] .....
As you can see, the second column contains abstracts which I want to use to determine categories for each document (each line).
Categories are as followed :
Diabetes prevention -> keywords : sugar, diabet*, insulin*
Obesity prevention -> keywords : weight-loss, fat*, LDL
etc. (5-8 categories)
To target one keyword, I found this formula :
=IF(IFERROR(SEARCH("*diabet*"; $B3); 0); "Diabetes prevention"; " ")
What I would like to know is how to add more than one keywords in this formula.
I would also like to know, if it is even possible, how to put every categories in this column, so that i don't have a category per column ... It's hard to explain.
I want to have in front of each line the corresponding column. It means that the formula has to search for every keywords and select one or more appropriate categories.
View 2 Replies
View Related
Feb 27, 2014
I have a sheet that lets the user enter a category and then a sub heading, I need the data to be displayed under each category heading in alpha/numeric order as each new entry is made into the list.
Please see the attached sheet for details. Book3.xlsx
View 5 Replies
View Related
Feb 17, 2014
I'm trying to make a chart viewer on the first sheet of my spreadsheet and am using this website as a guideline, [URL] However instead of the 3 charts in the example, I have 12 which makes my formula for defining a name substantially longer.
By moving my charts to the same sheet and getting rid of the INDIRECT formulas, I shortened my formula to 291 characters which is too long as the limit is 255.
=IF($G$9="Sodium",$N$34,IF($G$9="Manganese",$N$35,IF($G$9="Iron",$N$36,IF($G$9="Nitrate Nitrite",$N$37,IF($G$9="Nitrite",$N$38,IF($G$9="Nitrate",$N$39,IF($G$9="PH",$N$40,
IF($G$9="Conductivity",$N$41,IF($G$9="Turbidity",$N$42,IF($G$9="Fluoride",$N$43,IF($G$9="Selenium",$N$44,$N$45)))))))))))
Is there any way to make my formula shorter? Or to reference a cell where I place the formula?
View 5 Replies
View Related
Oct 7, 2008
If Average GMV (Cell A1) is less than 75% of Average BP (Cell B1), use Average GMV, however the Average GMV or Average BP can't be less than $600 million. Multiply the result by .01*1/3. Below are two equations I've created, however each of them only solves one part of the problem.
=IF(A1<0.75*B1,A1*0.01*1/3,B1*0.01*1/3)
=IF(B1<600000000,(600000000)*0.01*1/3,B1*0.01*1/3)
Essentially Average BP would be used, unless Average BP was something like $900,000,000 and Average GMV was around $650,000,000. At this point Average GMV is 72% of Average BP and above the $600,000,000 minimum.
View 2 Replies
View Related
Jun 16, 2009
Knowing Excel adapts its formulas upon inserting a row (i.e. a sum formula will automatically include a new row inserted in the middle of its range). Is it possible to do this with a named range. I have a pivot table that serves as a table for a vlookup, but the table sometimes changes month to month with extra accounts. I need to be able to keep the formula even though the table changes, so I figured a named range would help me out but it still doesn't accomplish what I need it to do. So basically, is there a way to make the Named Range, say AGroup (A1:C5) extend to (A1:C7) if 2 new rows are added in?
View 12 Replies
View Related
Apr 23, 2009
I have a sheet containing several charts. I have a script which I found on this site that helps me create dynamic min and max values for the axis.
Since I have several charts it seems that I need to define which chart that I want to change the axis.
This causes an error.
View 9 Replies
View Related