excel,vba,excel-vba,cell,formula

I'd look to handle this in another if statement and then nest the next if statement within like so: Sub Example() Dim Pattern As String Pattern = W.Range("C7").Value If Pattern = "AND" Then If CritEUs = "NO" And CritSKUs = "NO" Then 'Do Something' End If ElseIf Pattern = "OR"...

This is the final formula I came up with: =IF( LEFT( MID( Sheet2!V19, FIND( "|", Sheet2!V19, FIND( "|", Sheet2!V19 )+1 )+1, 999), LEN( INDIRECT( "Sheet3!"&ADDRESS(T16,1) ) ) )=INDIRECT( "Sheet3!"&ADDRESS(T16,1) ), 1, 0 ) where Sheet2 is the sheet containing the pipe delimited strings, Sheet3 column A contains the drop-down lookup...

Have you tried Goal Seek, the disadvantage is it will only change 1 cell. Using the Solver will change all three cells, but you then need to set constraints on the cells in column B.

Instead of X = (X - 1) % 5 use X = (X + 4) % 5 which is the short form of X = (X - 1 + 5) % 5 or generally X = (X - 1 + n) % n This ensures that the argument in ()...

r,string,text,syntax-error,formula

The <text>:2:10080 is giving you the location of the error. 2nd line, 10080th character. Consider: parse(text="1 + 1 + 2\n a - 3 b") # Error in parse(text = "1 + 1 + 2\n a - 3 b") : # <text>:2:8: unexpected symbol Here, the error is with b, which...

algorithm,math,slider,formula,uislider

This should work: ((x - minX) / (double)(maxX - minX)) * (maxY - minY) + minY ((x - minX) / (double)(maxX - minX)) computes the "percentage" of x in x's range. Then you simply multiply by the magnitude ((maxY - minY)) of the y range and add the lower bound...

Can't tell for sure from your question if you cannot use a package, because the Formula package has a function just for this. It classes its objects as both formula and Formula: > library(Formula) > frml <- Formula(y ~ a + b | x + z ) > update(frml, ....

F(n) = ceiling((sqrt(8*n+1)-1)/2) Say F(n) = a. Then n ~= a * (a+1) / 2. Rearranging: a^2 + a - 2n ~= 0. Solving: a = F(n) = (-1 + sqrt(1+8n)) / 2. Ignore the negative answer....

To handle the bigger number problem, we need to use number 10234573 in more efficient way. We know that for mod, we have: (m*n) mod x = ((m mod x)*(n mod x)) mod x To use the above formula in our calculation: n*(n+1)*((2*n+1))/6 % 10234573 we need to get rid...

google-spreadsheet,formula,spreadsheet

Maybe you can also consider the use of AVERAGEIF() as it seems the function is built for situations like this: =averageif(A2:A; "moderate"; B2:B) ...

In Java, integer division will truncate the results. For example, 5/2 will truncate 2.5 to 2. To ensure you're using float with numeric constants, add a .0 on the end, as in: int test = (65.0 / 65.0 / (1.0 + 25.0) * 10000.0); ...

Thank you for your tip about using ranges, I will use it in the future. You suggested the following formula: =IMPORTRANGE("https://docs.google.com/...; A1) It didn't work. I got it to work with the following formula: =IMPORTRANGE("https://docs.google.com/..."; (A1&"!C2:C5")) ...

excel,indexing,row,formula,names

This is an approach that uses a Helper column that you can adapt to your needs. We place your data in columns A and B and put the validation list cell in E1. In C1 enter: =IF(A1<>$E$1,"",ROW()) and in C2 enter: =IF(A2<>$E$1,"",IF(SUMPRODUCT(($A$1:A1=$E$1)*($B$1:B1=B2))>0,"",1+MAX($C$1:C1))) and copy down. Column C marks the items...

You can use the VLookup function for that: Make a table with your city name in one column and the state in the next column. Then the following formula next to the city that you want populated: =VLOOKUP(A1,A$20:B$23,2,FALSE) In this example, the city you want to identify is in A1,...

sql-server,formula,date-of-birth

It looks like the original code is not taking today into account, it always starts tomorrow, which may be one reason why your new code is finding more people. However, consider your logic: DATEPART(DY,dateOfBirth) - DATEPART(DY, CURRENT_TIMESTAMP) > 0 According to this, if my date of birth was January 1st...

excel,vba,excel-vba,if-statement,formula

Try this: Cells(Counter, 36).Formula = "=IF(" & Cells(Counter, 35).Address & " >= 0,""Recovery " & _ "Equals or Exceeds Amount calculated"",""Recovery less than Amount " & _ "calculated"")" ...

Following this example you could do this: lapply(1:3, function(i){ lm(as.formula(sprintf("y ~ x%i + x4 + x5", i)), a) }) ...

Recursive solutions are best in those situations that move quickly towards the base case. For example, a binary search gets rid of half the solution space for every recurrence so that you could search four billion items in about thirty-two levels of recursion. Your algorithm to process 25,000 items requires...

excel,excel-vba,excel-formula,formula

There is no need for VBA. As I suggested in my comments above, add a helper column (Let's say in B) and put the formula mentioned below in cell B2 and pull it down. =IF(COUNTA(C2:F2)=4,"Complete","") Output ...

python,math,automation,formula

Here i would say the problem looks more like polynomial interpolation In python you have many libs dealing with that, for example high level scipy.interpolate or you can do it "by hand" like these examples In any case, you will need some maths !...

Does this do the required behavior? func balance(P:Double, r:Double, c:Double, Y:Double) -> Double { var bal:Double bal = pow(P*(1+r),Y) + c * (pow(1+r, Y+1) - (1 + r))/r return bal } called like var balance = balance(1.0, 2.0, 4.0, 7.0) oops forgot my return type....

java,mysql,hibernate,function,formula

Solved, I just use native SQL, addScalar method, and List of Object[], and big big query: Session session = null; List<Object[]> catalogStores = new ArrayList(); try { session = this.sessionFactory.openSession(); catalogStores = session.createSQLQuery("select \n" + " t1.name as goodsName,\n" + " t3.name as companyName,\n" + " sum(t2.amount) as amountSum,\n" +...

string,excel,search,sum,formula

Do you want a running total, or just the total? Either way you can use SUMIF with "wildcards" For a simple total of all column B values where column A contains the substring "apple" try this formula =SUMIF(A:A,"*apple*",B:B) If you actually want a running total you can amend that to...

php,security,formula,brute-force

You are still going to execute PHP code which needs CPU/RAM/... so a better way to do this is through limiting requests handled by the server. If you are afraid of a user DDoSing your server try using a service like Cloudflare. If you are using Apache, read up onto...

excel,osx,math,numbers,formula

Excel stores time as a fraction of a day, so 1 hour = 1/24. Your formula (in C2) should be: =A2/B2/24 Alternatively you can use `TIMEVALUE' function: =A2/B2*TIMEVALUE("01:00") Make sure to change C column format to currency/number/general EDIT: I think the above functions calculate hourly cost, but after reading your...

excel,count,sum,excel-formula,formula

If you have to compensate for blank cells, take the formula and adjust the numerator of your count unique to check for non-blanks then add a zero-length string to the COUNTIF function's criteria argument. =SUMPRODUCT((Usage!J2:J6000<>"")/COUNTIF(Usage!J2:J6000,Usage!J2:J6000&"")) Checking for non-blank cells in the numerator means that any blank cell will return a...

javascript,date,formula,momentjs

Just divide it into 3 parts.. first week, last week and the in between, something like this: function workday_count(start,end) { var first = start.clone().endOf('week'); // end of first week var last = end.clone().startOf('week'); // start of last week var days = last.diff(first,'days') * 5 / 7; // this will always...

Putting your formula into the Online Excel Formula beautifier I notice several problems with your formula: =IF ( ( P5 ) < ’UK Tax Figures’!C3 , 0 , IF( P5 >= ’UK Tax Figures’!C3 < UKTaxFigures!G5, ( ( P5 - UKTaxFigures!C3 ) / 100 ) * 20, IF( P5 >=...

You can use SUMIF for this, e.g. =SUMIF(A:A,1,B:B) that sums all values in column B where column A = 1...

hyperlink,google-spreadsheet,formula,google-apps,auto-populate

give this a try: =ArrayFormula(if(len(E2:E), HYPERLINK("https://domain.service-now.com/incident.do?sysparm_query=GOTOnumber%3D"&E2:E, "Check for Updates"),)) and see if it works for you ?...

algorithm,math,formula,equation

One way to approach this would be to adjust C so that it absorbs the factor A+B. Then the ratio of A to B would be exact, and X, Y, and C would all be integers. Let D = 5*(A+B), C2 = ceiling(C/((double)D)) * D (round up so you get...

try if totext((Date(Year(YourDateField), Month(YourDateField), Day(YourDateField))),'dd/MM/yyyy') ="01/01/1900" then "-" else totext((Date(Year(YourDateField), Month(YourDateField), Day(YourDateField))),'dd/MM/yyyy') ...

Let's intersection point is P, and unit direction vectors (norm in the code from above link) of A and B lines are dA and dB respectively. Then tangential points and arc center are (four solutions): T1 = P +- dA * R T2 = P +- dB * R C...

java,math,formula,square-bracket

You can remove those square brackets and replace them with normal parentheses (). double x = (5 / 2) * (3 + 5 * 2); Based on the priority, the first statement to be evaluated will be the square-bracket statement because of highest-precedence and left-to-right associativity in solving this particular...

Assuming you have your data set up as follows A1: Name B1: Grade C1: Goal use the following formula in D1 =VLOOKUP(B1,{"A",0;"B",1;"C",2;"D",3;"F",4},2,0)-VLOOKUP(C1,{"A",0;"B",1;"C",2;"D",3;"F",4},2,0) From there you can drag the formula down...

Try: Seems like this solves it: =index(B:B, match("black shirt",B:B,0)+1)...

excel,if-statement,nested,formula

Don't reinvent the wheel: there is already a built-in function do to this. =TEXT(G2,"0000") Now, to answer your question specifically, the reason you got your error was that your IF functions weren't properly nested due to misplaced right brackets ). Was: =IF(LEN(G2)=3,"0" & G2),IF(LEN(G2)=2,"00" & G2,G2) ^ Should be: =IF(LEN(G2)=3,"0"...

javascript,latex,formula,mathjax

For LaTex inline math you have to use \( and \) instead of backticks: el2.html('\\(\\frac{\\displaystyle \\sum_{k=1}^N k^2}{a}\\)'); gives the desired result. The backtick is per default the asciimath delimiter (see http://docs.mathjax.org/en/latest/asciimath.html) and \( the TeX delimiter (see http://docs.mathjax.org/en/latest/tex.html). Alternatively you could reconfigure the delimiters with asciimath2jax: {delimiters: []}, tex2jax: {inlineMath:...

If you aren't looking at thousands of rows, you could try the following. Instead of using this line to print the result of your vlookup formula: Rng.Cells(i, 37) = Application.VLookup(.Cells(i, 28), Sheets("VAT_apportionment_table_201310").Range("D:G"), 4, False) You could instead put the actual formula in the cell with this change Rng.Cells(i, 37).Formula =...

google-spreadsheet,formula,spreadsheet

Please try in C2 and copied down to suit: =if(B2>=percentile(B$2:B$51;0,75);"YES";"NO") I hope your classes are no bigger than 50....

OK, now that I know what you're after, what you could perhaps do is use that taylor command and expand about a point that is quite far off from where you want to compute the limit. If we set the expansion point to be where you want to evaluate the...

excel,excel-formula,excel-2007,formula

We can do this with a helper column: Helper column, keep the name if the count of 5 equals to 5. Enter below to K2 and fill down. =IF(COUNTIF(E2:J2,5)=5,D2,"-") Then we are counting how many times name appears in the helper column. Enter below to B2 and fill down. =COUNTIF($K$2:$K$11,A2)...

r,formula,random-forest,caret,predict

First, almost never use the $finalModel object for prediction. Use predict.train. This is one good example of why. There is some inconsistency between how some functions (including randomForest and train) handle dummy variables. Most functions in R that use the formula method will convert factor predictors to dummy variables because...

c,algorithm,math,formula,mathematical-lattices

Writing plane as an equation Instead of “slopes” I'd rather speak about normals of these planes. So the way I understand your question, you have xy plane, slope 0 ⇒ normal (0, 1, 0) xy plane, slope ∞ ⇒ normal (1, 0, 0) xy plane, slope 1 ⇒ normal (1,...

excel,vba,date,excel-vba,formula

a formula to add 1 to your week number format looks like this: =YEAR(DATE(LEFT(A2, 4), 1, 1) + MID(A2, 5, 2) * 7) * 100 + WEEKNUM(DATE(LEFT(A2, 4), 1, 1) + MID(A2, 5, 2) * 7) it will create a sequence of week numbers that includes: 201452 => 201453 =>...

If you are wanting to do this programmatically instead of using a built-in, check out the split function for chopping up your comma separated string. It will split up your input string into an array. Then you can do whatever you like with the array. Dim Names() As String Names()...

algorithm,math,language-agnostic,functional-programming,formula

Note that the most circles with radius R contain less than πR² integer points within the boundary, so usually that map doesn't exist. Reference: Gauss circle problem And I suspect that even for good R values there is no simple formula, only iterative aprroach (look at (1) formula in the...

crystal-reports,formula,crystal-reports-xi,suppress

Its a bit confusing but try below way... if you are trying to meet all 3 conditions then you need to write that first because if any of the one condition is met first then control will never reach to statisying all 3 conditions and after that your regular conditions...

Like this? =IF((IF(ISERROR(SEARCH("TEST",A1)),0,SEARCH("TEST",A1)))>0,(A1 & " Dave"), "") You would just change TEST and Dave to what you would like and you can fill it down in Column B...

Assuming your data starts in A2 (row 1 being used for headings), then try the following formula in C2 and copy it down: =COUNTIFS(A:A,A2,B:B,">"&B2,B:B,"<="&B2+2) That says "Count all cases where the value is col A is the current student and where the date is between the date on the current...

Are you looking for excel formulas to do this for you? How about: =VLOOKUP((LEFT(A1, 5)), Sheet2!A:C, 2, FALSE) In column B of Sheet1. A1 references the first column in sheet one, and you would change the 2 to a 3 in column C....

excel-formula,excel-2007,formula

Aha! This is actually quite simple and straightforward after reading Excel SUMPRODUCT Function. Thanks to the author Dave Bruns! The formula is =SUMPRODUCT(--(C1:C4<B1:B4),--(ISNUMBER(C1:C4)),C1:C4) ...

tan, sin, and cos are actually measuring the ratios between two edges of a 3-edged object aka a triangle. Hence in your case, to form that triangle, you will need the lengths of two edges. They are the lengths between y1 and y2, and x1 and x2. That is why...

I needed to change the comma"," in the formula to a point".". Although in my local settings, excel uses the "," apparantley in the object it only uses the standard value ".". Thanks again for your help!

There are 3 ways to insert variables in the formula part of a function: First by using . which will include all of the variables in the data data.frame apart from the response variable (variable Output in this case): net <- neuralnet(Output ~ ., data, hidden=0) #apart from Output all...

networking,formula,concept,network-efficiency

Yes. But it has already been included. Token Holding time is THT = Transmission Time + Ring Latency time (for single round of packet transmission) As (THT = Tt + Tp) ....

excel,if-statement,wildcard,formula

Wildcards don't work here. Possible solution is instead of: $C7243=ANP$7&"*" Use: LEFT($C7243,LEN(ANP$7))=ANP$7 ...

I upvoted Nplay comment, easychem seems really fun. Filthy, dirty way to achieve it with HTML (don't try this at home please) : <p> <span style="visibility: hidden;">CH<sub>3</sub> - </span>CH<sub>3</sub><br/> <span style="visibility: hidden;">CH<sub>3</sub> - </span>|<br/> CH<sub>3</sub> - C - CH<sub>3</sub><br/> <span style="visibility: hidden;">CH<sub>3</sub> - </span>|<br/> <span...

First, note the proper syntax for the IF() function: IF(logical_test, value_if_true, [value_if_false]) The logical_test is B2 <= 2. The value_if_true_ is 0. The value_if_false (i.e., if B2 is > 2) is B2 * 25. So the formula is =IF(B2<=2, 0, B2*25). For example (data starting in B2, formula in C2):...

for 50% (halfway) the formula is simply result = (a + b) / 2 in general for a generic percentage k the formula is result = (a*(100-k) + b*k) / 100 ...

I would use a helper column. Pick an unused column, say column QR and in cell QR4 enter: =IF(AND(PM4=1,PN4=1),1,0) In QR5 enter: =IF(AND(PM5=1,PN5=1),1+MAX($QR$4:QR4),0) and copy downward. This column assigns a simple sequential value for each "collectable" row. For example: Then in the other sheet, pick any cell and enter: =IFERROR(INDEX(Data!M$4:M$4999,MATCH(ROWS($1:1),Data!QR$4:QR$4999,0)),"")...

google-spreadsheet,formula,spreadsheet

Formula to be dragged down: =mod(B2-A2;1)-if(mod(B2-A2;1)>time(4;0;0); time(0;30;0); 0) Because of the mod() function this formula should deal with 'negative' hours in case of overnight time. Or in arrayformula: =ArrayFormula(if(len(B2:B); mod(B2:B-A2:A;1) - if(mod(B2:B-A2:A;1)>time(4;0;0); time(0;30;0); 0);)) Format the output column as 'duration'......

excel,excel-formula,excel-2010,formula

1st SUMPRODUCT could become =COUNTIFS(Sheet1!$N:$N,$A4,Sheet1!$H:$H,"1A*",Sheet1!$M:$M,"<>service catalog",Sheet1!$J:$J,"incident",Sheet1!$I:$I,"<>self-serve",Sheet1!$AK:$AK,AFM$1,Sheet1!$E:$E,">="&$E$1,Sheet1!$E:$E,"<"&$E$2) The LEFT part can be handled by a wildcard, as shown change the second part along the same lines...

algorithm,bit-manipulation,formula,twos-complement,ones-complement

you cannot calculate any formula with numbers in 2 different bases, you have to use their decimal representation (or an other representation you can handle) I'll give it a try in dec: 1042 (base 5) = 1* 5^3 + 4* 5^1 + 2 = 125 + 20 + 2 =...

Use INDIRECT to reference range from its text address: =COUNTIF(INDIRECT("Imports!$B$2:$B$999"),"1") ...

We can avoid the entire problem by using the default method of randomForest (rather than the formula method): randomForest(df[5:8], df[[4]]) or in terms of predictors and response defined in the question: randomForest(df[predictors], df[[response]]) As mentioned in the Note section of the randomForest help file the default method used here has...

I'm not quite sure about your algorithm, but it seems like you try to get every 3rd and 4th post not (starting at 0). The fitting code would be: if(($i % 4 == 0 || $i % 4 == 1) && $i != 0) { /* do stuff */ }...

oracle,performance,formula,oracle-apps

By looking at the function reference for OracleFormula, it seems that you have no other option than calling TO_CHAR with the appropriate format to achieve the desired result. based on the doc's example: mesg = 'Birthday is: ' + TO_CHAR (birthdate, 'DD') ...

Where names include spaces surround the name in single quotes. Easiest may be to start a formula in one sheet (=) and go to the other to select the range you want.

You need another INDIRECT: =INDIRECT("'" & (INDIRECT("B" & ROW())) & "'!G1") ...

You can use SUM(INDIRECT(A89)) where cell A89 would contain the text "A88:F88".

numbers,character,formula,powerquery

There are likely several ways to do this. Here's one way: Create a query Letters: let Source = { "a" .. "z" } & { "A" .. "Z" } in Source Create a query GetFirstLetterIndex: let Source = (text) => let // For each letter find out where it shows...

excel,google-spreadsheet,excel-formula,formula,spreadsheet

There are 21 different combinations of 2 days in a week - to ensure an even distribution you can use a helper column, so assuming your weeks start at A2 and days at B2 (always showing 1 then 2 in days column for each week) then use this formula in...

I take the meaning of your variable as below capacity as the total volume of water it can hold storage as the total volume of water it currently holds inflow as volume of water flowing into the dam per second outflow as volume of water flowing out of the dam...

excel,google-spreadsheet,excel-formula,formula,countif

You would have to use wildcards.. please try: =countif('Responses'!B:B,"*"&A2&"*") and see if that works ?...

function rank_avg($value, $array, $order = 0) { // sort if ($order) sort ($array); else rsort($array); // add item for counting from 1 but 0 array_unshift($array, $value+1); // select all indexes vith the value $keys = array_keys($array, $value); if (count($keys) == 0) return NULL; // calculate the rank return array_sum($keys) /...

excel,vba,excel-vba,excel-2010,formula

Sub Tester() Dim v v = Application.Evaluate("=Sheet999!A1") If IsError(v) Then Select Case v Case CVErr(xlErrRef): Debug.Print "#ref!" Case CVErr(xlErrDiv0): Debug.Print "#div by zero!" 'etc End Select End If End Sub See: http://www.cpearson.com/excel/ReturningErrors.aspx...

excel,reference,match,cell,formula

To make CELL work you need a cell reference, e.g. CELL("address",C1) The trouble is that MATCH just gives you a number, not a cell reference. Probably the easiest way is to use the ADDRESS function, so a first try might be =ADDRESS(1,MATCH(AU14,C1:AG1,0)+2) That would give you the right answer if...

javascript,math,formula,app-inventor

Yes, the library used by App Inventor is available. It is called Blockly.

Your formula is the sum of the reciprocal. You would do: select sum(1.0 / t.number) from t; Unfortunately, most databases do not support a product function. You can do this by taking the log and adding them and then reversing the log. The specific functions vary by database, but something...

How about this? SELECT POWER(10, CONVERT(INT, LOG10(@Input))) It takes the log base 10 of the input value (which returns the value of the exponent to which you would have to raise 10 to in order to get the input value), then it lops off the decimal portion leaving only the...

Taking a guess, assuming: You have a column of dates and a separate column of other data. The dates are in ColumnA. Since your example shows a single result, the other data is irrelevant for current purposes. Dates are date/time indices, not text strings. You don't want to sum dates...

excel,excel-formula,formula,vlookup

Personally, I would convert the letter grade to ASCII character using the function: CODE(A1) use the ASCII Reference Chart for the integer value of each Upper Case character. Note: A=65, B=66, etc... Perform your calculations, then you can use the function: CHAR(A2) to convert the number back into a character....

r,function,variable-assignment,formula,evaluation

You can do this: `*` <- intersect `+` <- c Be aware that if you do that in the global environment (not a function) it will probably make the rest of your script fail unless you intend for * and + to always do sum and intercept. Other options would...

Your mistake is single quotes ('), which means PHP won't interpolate variables inside the string, and you're not breaking out of the string for concatenation. Use double quotes (") without trying to concatenate: "=D{$i}*E{$i}" or concatenate: '=D'.$i.'*E.'$i ...

I figured it out. The formula was incorrect this is the correct formula Converting Between Markup vs Margin In the event that you know either the markup or the margin and need to know the other, then the following formulas will help you calculate. Markup = Margin / (1 –...

I am thinking along these lines. Pseudo-code, which you can easily convert to PHP (not tested): start transaction if (last_reset_date not null) { days_since_reset = last_reset_date } else { // Handle the case where no reset has yet been performed days_since_reset = now - sign_up_date update last_reset_date to sign_up_date }...

You can't have a range as the second argument of a Countif. The range you pass into the formula will resolve to just the first value. Use the Evaluate Formula tool to see what I mean. If you want to determine if ANY name in the range A1:W1 appears more...

Say, you have your monthly targets in Sheet1: Column A:A 2015-01-01, 2015-02-01, 2015-03-01 ... Column B:B =DAY(DATE(YEAR($A2),MONTH($A2)+1,0)) Column C:C monthly target values Column D:D =$C2/$B2 Then in Sheet2 you can calculate your weekly targets (I assume columns A:A, B:B and C:C don't need explanation): Column D:D =DATE(YEAR($B2),MONTH($B2),1) Column E:E =DATE(YEAR($C2),MONTH($C2),1)...

excel,date,formatting,formula,conditional-statements

Place the cell cursor to the first date in your list (here A1 for example) Select "Manage Rules"from the "Conditional Rules" dropdown icon in the dialog box select "New rule", then "Use a formula to determine which cells to format" enter formula as =A1<TODAY()-365 ... and give it a...

parameters,crystal-reports,report,formula,value

First thing to note is to select the value of the parameter before running the report then you can get the value. IF ({parameter1}) = 1 AND ({parameter 2})=14 THEN 'text' & {?parameter3} & 'text' ...