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...

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...

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....

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...

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' ...

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 */ }...

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...

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...

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...

There are two ways to address this problem. I will outline both solutions: Option 1: In Worksheet Formulas I'd have to spend more time on this in order to find a really elegant solution for this route, but here's a functional approach that should work well enough. Here are some...

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 ?...

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...

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.

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):...

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" +...

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,...

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, ....

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') ...

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 ...

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"...

First, you should convert you latitudes in radians too (you already do this for your longitudes): u1 = math.atan( (1 - f) * math.tan(math.radians(la1)) ) u2 = math.atan( (1 - f) * math.tan(math.radians(la2)) ) L = math.radians((lo2 - lo1)) # better than * 0.0174532925 Once you do this and get...

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); ...

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...

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...

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 ?...

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...

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....

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...

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 ...

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...

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...

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) ...

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 ...

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) /...

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...

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:...

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

This formula will count the number of rows that have a number >= 5 at least twice, change the 2 and 5 accordingly =SUMPRODUCT((MMULT((B2:E10>=5)+0,{1;1;1;1})>=2)+0) The {1;1;1;1} denotes 4 columns - change as required if you have a different number of columns or you can automate that part using this "array...

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 >=...

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")) ...

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

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...

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

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...

javascript,math,formula,app-inventor

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

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!

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...

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 =...

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...

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...

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....

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...

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...

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)),"")...

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 –...

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 !...

php,algorithm,math,formula,sudoku

This comes from the way the cells are counted, which we could call row-major. You can see block and cell numbers with their respective row and column numbers on this image : rows and columns The first row (0) contains cells 0 to 8, the second row cells 9 to...

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)...

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...

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()...

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 ...

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)...

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 =>...

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...

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...

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...

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...

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...

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"")" ...

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.

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...

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 }...

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 ()...

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') ...

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

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

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...

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...

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....

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,...

I can't exactly understand what you try to acheive. can you post an example of code, or give more explanations? CREATE TABLE history ( id INT NOT NULL AUTO_INCREMENT, calculation TEXT, PRIMARY KEY (id) ); INSERT INTO history (calculation) VALUES ('2+2=4'), ('1+1=2'), ('4+5=9'); ......

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,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...

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...

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...

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...

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 =...

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...

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...

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...