FAQ Database Discussion Community


display parameter value within formula crystal reports

parameters,crystal-reports,report,formula,value
I'm using Crystal Reports 14. I have a formula which based on two parameters (from month, to month) display different text AND different parameters My code is as follows IF ({parameter1}) = 1 AND ({parameter 2})=14 THEN 'text {?parameter3} text' My problem is that instead of the value of paramter3,...

How to calculate Number of 'Working days' between two dates in Javascript using moment.js?

javascript,date,formula,momentjs
How to calculate the number of working days between two dates in JavaScript using moment.js. I have a working formula that calculate these days, but the formula does not satisfies all conditions: here is my code: var start= moment(data[x].start_date); var end= moment(data[x].est_end_date); var difference= end.diff(start, 'days'); var workingDays= Math.round((difference/7)*5); //data[x]...

Algorithm to find closest integer values that meet certain criteria

algorithm,math,formula,equation
Edited to clarify the application by adding units (ml) and explaining the difficulty to measure wet reagents by units of 1/26. The word 'solution' was ambiguous because it was used to mean both a chemical solution as well as the solution to the problem. Added results based on Edward's reply...

Difference between birthday formulas in SQL server programming

sql-server,formula,date-of-birth
I am an intern at a company that is teaching me how to program queries for the first time in SQL server and I had a question about a formula I ran across while doing one of their tickets. It's a stored procedure that runs every week to send out...

Token ring protocol Efficiency and Cycle time?

networking,formula,concept,network-efficiency
I read Token ring protocol from a book Forouzon. According to book, Let N be the number of stations in the ring, THT the token holding time, Tt be the transmission time of packet, Tp be the propagation time of packet on Channel/ Link. Then Cycle Time = n *...

Interpret formulae/operators as functions

r,function,variable-assignment,formula,evaluation
Is it possible in R to assign custom functions to mathematical operators (eg. *, +) or interpret the formulae supplied with as.formula() as a directive to evaluate? Specifically, I would like * to be interpretted as intersect(), and + as c(), so R would evaluate the expression (a * (b...

Optimizing Excel formulas - SUMPRODUCT vs SUMIFS/COUNTIFS

excel,excel-formula,excel-2010,formula
According to a couple of web sites, SUMIFS and COUNTIFS are faster than SUMPRODUCT (for example: http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html). I have a worksheet with an unknown number of rows (around 200 000) and I'm calculating performance reports with the numbers. I have over 6000 times almost identical SUMPRODUCT formulas with a couple...

Reference other workbook in an Excel formula

excel,reference,formula,sheet
I have a workbook named 022315_FNUniverse (2).xlsx and a sheet named Regina Import that I would like to reference in an INDEX formula in Excel. I am unsure how to do so because of the spaces in the names. Both sheets are in the same folder. Any help would be...

Counting values embedded in strings inside a column (Google Spreadsheets)

excel,google-spreadsheet,excel-formula,formula,countif
I have a Google Survey where I created some multiple choice questions. Now, I am trying to count the responses. [A] [B] [Response#] [Selections] [1] [Apple,Orange,Banana] [2] [Orange,Banana] [3] [Apple,Orange,Banana] [4] [Banana] [5] [Apple,Banana] [6] [Apple,Orange] . So on my summary spreadsheet, I would like to have the totals: [Favorite...

Chemical formula in css

php,css,formula,equations
I need to write several formulas in CSS (or is there some other way?) and integrate it on my quiz website for students. I already found some interesting examples in here: http://www.periodni.com/mathematical_and_chemical_equations_on_web.html But I need one more, as can be seen in this picture: ...

EXCEL SUM cells which contain string

string,excel,search,sum,formula
I'm trying to write a formula in Excel which will run down a column, and search each cell in that column for the presence of a string (which may be in the middle of another string); if the string is found, move over to another column and add the value...

Math - Formula V[i] = ([ V[i-1] * V[i-1] / (i + 2)] + V[ i-1] * i + i + 1) % 666013

c++,math,formula
I need some help to resolv this formula: V[i] = ([ V[i-1] * V[i-1] / (i + 2)] + V[ i-1] * i + i + 1) % 666013 where v[0] = 3 and example: v[10000000] = 22230 My solution is: #include <iostream> #include <algorithm> #include <cmath> #include <fstream> using...

Round to nearest 10 multiplication

sql,tsql,math,formula
I have a bit of math question for my sql code. I want to be able to work out the following in my code using a mathematical formula, if anyone knows how I would love to know. Any number 1->99 : 10 Any number 100->999 : 100 Any number 1000->9999...

How to use square brackets for math in Java?

java,math,formula,square-bracket
I'm trying to make Java calculate an formula, but it contains square brackets ([]). How can I use those in a Java calculation? If I write something like this: double x = [5 / 2] * (3 + 5 * 2); Eclipse puts a red underline under the equal sign...

Formula for output value given a restricted range of input - Slider UI

algorithm,math,slider,formula,uislider
Given these parameters: minX = 10.0 maxX = 200.0 minY = 300 maxY = 700 The user can move x between minX and maxX. Find y so that it is the same proportionally between minY and maxY as x is between minX and maxX. So if x = 10, y...

How to separate specific list items with “+” and add to formula?

r,formula
I am trying to generate a formula using dataframe column names of the following format: d ~ x1 + x2 + x3 + x4 From the following sample dataset: a = c(1,2,3) b = c(2,4,6) c = c(1,3,5) d = c(9,8,7) x1 = c(1,2,3) x2 = c(2,4,6) x3 = c(1,3,5)...

Compound Interest Formula help in Swift xCode [closed]

swift,xcode6,formula
Balance(Y)   =   P(1 + r)^Y   +   c[ ((1 + r)^Y + 1 - (1 + r)) / r ] Can some of you formula experts help me code this in Apple's Swift please. I tried but some math is off and I got in endless loop also. Thank...

centrepoint, starting and ending point of tangential fillet/arc between 2 lines

math,formula
What is the formula to calculate the centrepoint, starting and ending point of a tangential arc at a given radius between 2 lines Ax1,Ay1,Az1 : Ax2,Ay2,Az2 and Bx1,By1,Bz1 : Bx2,By2,Bz2. The lines intersect....

Errors when nesting IF() formulas

excel,formula,nesting
I'm just putting together a simple spreadsheet that calculates tax owed based on a few different bands. I have an 'Invoiced Amount' cell that takes up the yearly invoicing and then applies a tax rate to it based on the following conditions: If 'Invoiced Amount' is below 10, 600 then...

Google Spreadsheet Query formula- show row below matching result

google-spreadsheet,formula
I'm wondering if it's possible to adapt this formula and use query function to display the row below the one that matches the query? =QUERY(Sheet1!B:B;"select * where B contains 'black shirt'";1) Google Docs formula auto copy and display cell if contain a specific word I'm importing data and the row...

Show residual grade in Excel

excel,excel-formula,formula
I'm trying to do something pretty basic but can't seem to get my head around it. - I'm no Excel expert. Basically I'm trying to work out how far away from a target grade a student is. For example: Student X, Target Grade B, Actual Grade C = Student X's...

Excel return value or run calculation if criteria is not met. Formula needed

excel,excel-formula,formula
This is a fairly simply formula that I need syntax for. I do not know the syntax that Excel will accept. Formula: I want to return a value of "0" if the target cell has a value of "2" or less. If the value is greater than "2" I want...

use object for adding formula to Excel c#

c#,excel,formula,comexception
I want to add a formula as shown in the third component. When i remove the "=" everything works. But for adding a formula to excel, you need the "="... When I add the "=", the program doesn't work anymore... Then I receive the error: System.Runtime.InteropServices.COMException was unhandled HResult=-2146827284 Message=Exception...

Randomly generate 2 dates per week over 2 years

excel,google-spreadsheet,excel-formula,formula,spreadsheet
How do I write an excel formula, which randomly picks 2 dates in each week over a 2 year period, where: The 2 dates per week are not the same as each other So this should not happen: 05/02/2015 05/02/2015 The first date in any given week is always before...

Is there any javascript construction block script/library alike Appinventor or Scratch? [closed]

javascript,math,formula,app-inventor
I was wondering if there is any javascript script or library that i could install on my site that would work much alike Appinventor or Scratch design blocks. I am trying to create a small formula creator from blocks, so i don't need something too complicated. Need just basic math...

Attempting to create a SUM with a CONCAT in the range, google sheets says no

excel,sum,formula,concat
I was wondering if you could help me with a request. I used a custom script that dynamically creates one half of a cell reference, and I use a ROW formula to calculate the other half of the reference. The problem is, I'm trying to use that generated reference in...

Match #VALUE Error?

excel,match,formula
I have two worksheets. I want to search through sheet 1 to look for the id in sheet 2, find where it matches ID and the flag, and then return the price. I have the following formula that gives me a #VALUE error: Column B, sheet 2 =MATCH(Sheet2!A2&"B",Sheet1!A:A&Sheet1!B:B,0) Sheet1: ID...

Unexpected Symbol in As Formula, Can't Find

r,string,text,syntax-error,formula
I've been using as.formula for setting up a glm, and I can't figure out where the unexpected symbol is. Part of the problem is that the character vector I'm converting is so long. It's about 700 words with + inserted in between in order to turn it into a formula....

Fast Modulo for N^2 formula

c++,formula
I have a problem, when I am trying to resolv this ecuation, ( 1*1 + 2*2 + ... + n*n ) % 10234573 My solution in c++, #include <iostream> #include <cmath> using namespace std; int main() { unsigned long long int n, s= 0; cin >> n; if (n%10234573 ==...

Rank Average with PHP

php,math,formula,ranking
In Excel there's a function Rank Average (see documentation). I wish to do the same in PHP. Looking online, I find a lot of ranking solutions, but not a lot of those take duplicates into account and when they do, the result I get is not the same as Excel...

Find angle from two points formula

formula,angle,points
I have question about the formula to calculate the Angle between two points p1(x1,y1), p2(x2,y2). I use this formula : angle = arctan(y2-y1/x2-x1) Can you explain, why y2,y1 and x2,x1 must be subtracted ? Thanks...

Excel formula to find amount of columns filled

excel,excel-vba,excel-formula,formula
I hope everyone is having a great day. I have an excel file where the employees ID number is in column A. Then I have 4 other columns that tell me if an employee filled out data for Degree, Work Experience, Experience Level and Languages known. The employee may appear...

Excel formula to prevent duplicate name entries

excel,formula
I am creating a work chart for a project with excel table. However with so many people to manage I have ran into an issue of often putting same person twice on different columns of the same row (he/she can't work on two places at same time!) So, I am...

Converting letters to text and back again (Excel 2013)

excel,excel-formula,formula,vlookup
For a piece of coursework I have to complete a register of student grades. I am trying to calculate their overall grades by converting their grades from each of the four units into numbers (which I have done using the VLOOKUP function), but I need to then convert the result...

Java/Pawn Maths formula returns 0

java,math,formula
could someone explain me what am I doing wrong here, and how can I make this formula to be working? right now it returns 0 in Java and Pawn. But it works in PHP, which isnt making much sense for me. int test = (65 / 65 / (1 +...

Deconstruct Excel Formula To Get Result Without Trying Values

excel,formula
I need to deconstruct Excel formulas so that I don't have to put values in to see what the result is. I want to put in a result and get a values. I know this is difficult given with multiple variables the answer could be different. I'm looking for more...

get amount of Days until a dam “Event” in java

java,math,formula
I'm running into an issue making a formula for calculating the amount of DAYS it will take for a dam to either empty, or fill. This is all in a public class called Dam. Basically I have: int capacity = 3538000; // acreFT int storage = 1796250; // acreFT int...

Excel - Conditional Formatting using time arguments

excel,date,formatting,formula,conditional-statements
Looking to change the fill of a cell based upon the amount of time between the current date (TODAY), an entered value detailing when something was done historically and a set period from that historical date of either 6months or 1year. E.g. I have a date of 12/04/2015 entered as...

If a cell value equals this, another cell equals that

excel,formula
I have a spreadsheet with a column for cities, of which their are only 4 different values. What is the formula for equating a new column to show the corresponding state and apply it to the entire list? Example: Atlanta equals GA, Phoenix equals AZ, Chicago equals IL, Nashville equals...

Excel year-week combination calculation

excel,vba,date,excel-vba,formula
I am currently making an excel model where the date is an important aspect of it. However as the information comes from a different source it is sometimes structured in such a way that it is not realistic. I have a problem with the calculation of the year and week...

PHP formula to prevent exceeding http requests from an IP

php,security,formula,brute-force
I am going to prevent hackers from scanning my website pages , I invented a formula to control average hits in a time span , is it the best solution? function exceeded(){ $limit=5; $span=15; $now=time(); if($now-$_SESSION['lastDate']>=$span){ $_SESSION['lastDate']=$now; $_SESSION['totalHits']=1; return false; } $_SESSION['totalHits']=($_SESSION['totalHits']*($span-($now-$_SESSION['lastDate'])))/$span; $_SESSION['totalHits']+=1; // Adds current Hit if($_SESSION['totalHits']<=$limit){ $_SESSION['lastDate']=$now; return...

How to update a conditional formula?

r,formula
let me jump right into the example. Consider the following equation: frml <- formula(y ~ a + b | x + z ) Such a formula specification is used e.g. with AER::ivreg. I would like to update this formula so that it reads frml2 <- y ~ a + b...

Search columns and return number of cells containing “5” against colleague name

excel,excel-formula,excel-2007,formula
What I'm looking for is a formula that will do the following: (I'll be using the first row for this example): Firstly check cells E:J for cells containing a 5. If all cells contain a 5 it will check the person's name and then add this to the box in...

How to write query with agregate functions in hibernate?

java,mysql,hibernate,function,formula
I have a query in MySQL: select t1.name, t3.name, sum(t2.amount), t3.price, t1.price * sum(t2.amount) as 'summ' from store_catalog t1 inner join commission t2 ON (t1.id = t2.idGoodsStore) inner join company_catalog t3 ON (t3.id = t2.idGoodsCompany) group by t1.id I read about @Formula annotation and when I use this annotation, project...

Displaying repeated names from month list just once in excel (2010)

excel,indexing,row,formula,names
I have a Leaves Table on sheet 1 that has the following headers Column A = Months ; Column B = Names ; Column C = ... Now on Sheet 2, I have a months data validation list in Cell H59. QUESTION: When I select a particular month from the...

php/mysql: 30 day interval for pre-paid membership

php,mysql,formula
I have a membership where each user gets 100 credits per 30 days interval. All memberships are prepaid for one to unlimited months upfront. I want to reset the credits balance each 30 days back to 100 no matter if the user used his credits or not. I do not...

Excel - using wildcard in IF formula

excel,if-statement,wildcard,formula
I need to use wildcard in IF formula (see the bold chars) =IF(AND($J7243>ANR$23;AND(OR($C7243=ANP$7&"*";$C7243=ANP$8;$C7243=ANP$9;$C7243= What is the right syntax (this syntax does not run) Thank you very much....

Power Query - remove characters from number values

numbers,character,formula,powerquery
I have a table field where the data contains our memberID numbers followed by character or character + number strings For example: My Data 1234567Z1 2345T10 222222T10Z1 111 111A Should Become 123456 12345 222222 111 111 I want to get just the member number (as shown in Should Become above)....

Complex cross-worksheet Excel formula with string manipulation

excel,excel-formula,formula
I am generating an Excel workbook from C# code, but the code will end there as the workbook must be distributable to people who don’t have access to the VSTO stuff. The workbook contains: A) “Worksheet 1” that contains a dropdown list which gives “1” or “2” depending on the...

Numbers Mac Price Per Hour Formula

excel,osx,math,numbers,formula
I want to essentially calculate the price per hour. I have Column B, with time in it formatted like 01:10 00:45 00:10 etc. and i have a list of prices in column A. I want column c to list the cost per hour. Being below an hour would make it...

Crystal Report formula if-else statement Date condition

crystal-reports,formula
I would like to ask about how to use if-else statement in the crystal report formula. I want something like If Date = "01/01/1900" Then //Display "-" Else //Display Date But i always faced that the field is require number, currency and date error. I am still new with crystal...

Formula Sequence

c++,sequence,formula
I need help finding the formula of the sequence for the next problem. What I think and have for now is Sn=n(10^n-1)/9 but it just works in some cases... Here is the description of the problem: Description Sn is based upon the sequence positive integers numbers. The value n can...

Is there any formula that maps an int on the range [0..πR²] to the (x,y) coordinates inside the circle of radius R?

algorithm,math,language-agnostic,functional-programming,formula
The formula: index(i,w,h) = (i%w, (i/w)%h) uniquely maps each integer i on the range [0..w*h] to a coordinate inside the rectangle of width w and height h. Is there any similar formula: index(i,r) = ? that uniquely maps each integer on the range [0..πR²] to a coordinate inside the circle...

How invert this formula with % operator

c#,sequence,formula
I have a semaphore variable with 5 states. I can increase the state using this cicle X = (X + 1) % 5 For X = {0, 1, 2, 3, 4} generate {1, 2, 3, 4, 0}. But if I try go in the other direction decreasing the state, doesn't...

Nested IFs to pad numbers with zeroes [duplicate]

excel,if-statement,nested,formula
This question already has an answer here: Add leading zeroes/0's to existing Excel values to certain length 5 answers I am looking to pad a columns with zero's so that I can later concatenate it's value. It needs to 4 characters, so I am trying to use a Nested...

Catching iterations using php modulus in an irregular sequence

php,formula,modulus
How can I write a modulus that will select the following iterations in sequence? 1, 4, 5, 8, 9, 12, 13 etc (+3+1r) I'm working within a loop and counting posts (iterations). So for example, I could catch every third post (1, 4, 7, 10) by:- if ($i % 3...

Reflect a point on a lattice across a plane

c,algorithm,math,formula,mathematical-lattices
I have a lattice three dimensional lattice of size lx * ly * lz with periodic boundary conditions on all three sides. My planar symmetry cuts are two horizontal and vertical planes (slope 0 and inf) plus two diagonal cuts (slope -1 and +1) perpendicular to the xy plane, the...

SUMIF YYYY-MM-DD = TODAY?

excel,date,formula
I have fields that look like, and has to look like this: YYYY-MM-DD G/G YYYY-MM-DD U/G YYYY-MM-DD G/U YYYY-MM-DD U/U I want to show in a cell, the SUM of the dates that correspond to the date today is. Is this possible, and how? Like: Done today: XX...

calculating the function limit interatively

matlab,limit,formula
How to calculate the limit of a function interatively in Matlab, by closer to the given limit value? The accuracy of closing is 10^(-7) I suppose that that the taylor formula should be used, but don't know how to apply it there. The function itself is : The limit is...

Error when using predict() on a randomForest object trained with caret's train() using formula

r,formula,random-forest,caret,predict
Using R 3.2.0 with caret 6.0-41 and randomForest 4.6-10 on a 64-bit Linux machine. When trying to use the predict() method on a randomForest object trained with the train() function from the caret package using a formula, the function returns an error. When training via randomForest() and/or using x= and...

Linear regression of same outcome, similar numbe of covariates and one unique covariate in each model

r,formula,linear-regression
I want to run linear regression for the same outcome and a number of covariates minus one covariate in each model. I have looked at the example on this page but could that did not provide what I wanted. Sample data a <- data.frame(y = c(30,12,18), x1 = c(7,6,9), x2...

Static Formulas in Excel

excel,static,formula,countif
I have a formula in excel that looks like such: =COUNTIF(Imports!$B$2:$B$999,"1") It works like I want it to but part of my manipulation of the spreadsheet requires me to insert new columns in the sheet that formula is referencing. When I do that it changes the formula from column B...

Formula to return the sum of a range in Excel for certain rows in a different column

excel,sum,formula
I would like to use formula to return the sum of cells from column B where cell value in column A is equal to a certain week number. The table is below. For example if Week = 1 the SUM would be 40; Week = 2 SUM = 110 ,etc....

Excel formula for convertiny monthly targets to weekly ones

excel,excel-formula,formula
I have the monthly sales targets for a company for 2015 (not linear) and would like to convert them into weekly targets. I am unsure how to come up with a formula which deals with overlapping days (where some days of a week are in one month and the others...

Use text from cell with Importrange Google Spreadsheets

google-spreadsheet,formula
I've got two Google Spreadsheets documents. The first document contains data per week and the second document gives an overview of that data. The sheets in the first document are named by week. For example: Week 1, Week 2, Week 3, Week 4... The sheets in the second document contain...

phpExcel - Excel found unreadable content when use formula?

formula,phpexcel
I wrote this code but when I open excel file I had error "Excel found unreadable content". Here is my code $i=8 $objPHPExcel->getActiveSheet()->setCellValue('F'.$i,'=D.$i*E.$i'); $i++; Where is my mistake? Thank you!...

Crystal reports suppression formula with multiple OR operators not working

crystal-reports,formula,crystal-reports-xi,suppress
I am trying to add a very simple suppression formula to my Crystal Report (XI) field but it is not working as expected. I would like a text box to be visible if certain conditions are met, otherwise suppress. With the suppress box ticked my current formula is below: {table1.field1}...

Excel: Sum if the value is numeric and meet a relative criteria

excel-formula,excel-2007,formula
A B C 1 N1 4 3 2 N2 7 Blocked 3 N3 5 5 4 N4 12 0 I want to SUM the total of column "C" if it is numeric if it is less than column B I want it to be something like this, but obviously...

Count unique values excel formula with Blanks

excel,count,sum,excel-formula,formula
I am trying to count unique values in excel by using a formula. I have tried using the formula below but it returns a #DIV!/0 error because I have blanks in my range. Is there anyway to count unique values in a range with blanks by using a formula? Any...

Formula to get number between percent

math,formula,percentile
I need to calculate a % between two numbers. 255 = 100% 153 = 0% What is the formula to get the number for 50%, using the scenery above?...

Return a name matching two values

excel,formula
I have a table of data in one worksheet and some analysis in a second. In the 'Data' worksheet each row contains a name and then a range of values (numerical). What I need to do is look across each row and where, for example, columns PN and PM both...

using the 'Match' function in Exel to return a cell address

excel,reference,match,cell,formula
I have a list of numbers in a table that I would like to search for and bring back the cell reference of where that number resides. For example the data looks like: A B C D 1 1 2 3 4 ok it doesn't come out very well as...

Googlesheet formula for =HYPERLINK with autopopulation?

hyperlink,google-spreadsheet,formula,google-apps,auto-populate
I really need a bit of help with this. I've a sheet that autopopulates (from a submitted form). There are currently 5 columns. | Stamp | Username | Field | Field2 | Field3 | |---------------|---------------|-------|--------|----------| |06/03/2015 | [email protected]|Text12 |Blah |INC1126363| |06/03/2015 | [email protected]|TextABC|Blah2 |INC1128453| |06/03/2015 | [email protected]|Text123|Blach3 |INC1126270| However, I...