w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Mysql. Deduct SUM(d.Amount) from SUM(c.Amount) and group by (depending on) values in other column
SELECT TransactionPartnerName, SUM(if(CAST(DebitAccount AS UNSIGNED) IN (?), -Amount, Amount)) FROM 2_1_journal DATA WHERE (CAST(DebitAccount AS UNSIGNED) IN (?) OR CAST(CreditAccount AS UNSIGNED) IN (?)) GROUP BY TransactionPartnerName Try this Edit : Removed inner query

Categories : Mysql

What is the excel formula to get an amount from one column if another column indicates a Y?
Are you looking for the IF statement? IF( condition, [value_if_true], [value_if_false] ) Example: =IF(B2="Y",C2,X)

Categories : Excel

Excel: How to parse/cast text as a formula?
I'm not sure if this would answer your question(it's a very detailed question!), but if your user was entering the account numbers in a table (I'm calling it 'RefTable') , that was: Length of account number | business type ---------------------------------------- 6 | Accountant 8 | Advisor Then you could just use a vlookup on the length of the account number, given you've already separated them out. =vlookup(len(accNumber), Reftable, 2, false) Make sure that you either use a dynamic range name, or specify plenty of space below in RefTable, so that when your users add types, they don't get lost. Also, if you have two different accounts with the same length, this could get you into trouble.

Categories : Excel

Excel: get cell collection of cells that contain certain text or formula
Paste this macro in your macro editor in your workbook (ALT+F11) Sub test() Dim w As Worksheet, r As Range For Each w In ThisWorkbook.Worksheets For Each r In w.UsedRange If r.Value = "Certain text" Or r.Formula = "Certain Formula" Then 'Do what you want to do here End If Next Next End Sub Hope this helps

Categories : C#

For an Excel Formula's variable taking a Range, input row number by the result of a formula
Try using the INDIRECT function:) say you have three cell that can dynamically calculate the address of your input range. something like what you suggested and it is in the cell A1 ="$BB$2:$BB$" & COUNTA(BB2:BB1000) ---> in cell A1 You can use the Indirect function like this: =SERIES(INDIRECT(A1),[RANGE],[RANGE]) Hope this helps!

Categories : Excel

Excel formula -> how to change SUMPRODUCT formula to skip null cells
In the sumproduct, to exclude empty cells, suppose you're using it on range A1:A100, you could do the following: = Sumproduct((A1:A100),--(A1:A100<>"")) That second criteria will ensure that you're only looking at cells that have a value in them... As an explanation (A1:A100<>"") will return an array of True False, where, if there is a value in the cell, it returns true, otherwise, false. Then, including the -- before it, it converts True/False to 1/0. So, in effect, you're multiplying empty cells by a zero (excluding from the formula) and non-empty cells by 1 (including them in the formula). The --(logical statement for my array) is a very useful trick to use with SumProduct() in MANY different ways!!

Categories : Excel

Convert Java/C style formula to excel formula
You can use built in Excel functions to substitute desired strings instead of other determined strings, such as: =SUBSTITUTE(D2,"AND"," == ") You can also read the formula into a string in VBA, perform the same operation, and place the formula back in excel. The strFormula = Cells(2, 4).Formula strFormula = Replace(strFormula, "AND", " == ") Cells(2, 4).Formula = strFormula I hope this helps.

Categories : Misc

write a basic formula in VBA code for Excel (string date text to date format)
=DATE(YEAR(A1),MONTH(A1),DAY(A1)) no need! its already there :) if you need to do this in a fx it could look like this function convert_date(text As String) As Date convert_date = EVALUATE("DATE(YEAR(text),MONTH(text),DAY(text))") end function

Categories : Excel

SQL Formula Answer is Different from Excel Formula
Firstly, the question is tagged with MySQL, but the syntax used looks suspiciously like SQL-Server, as such I will do all the demonstrations with SQL-Server syntax. Secondly, based on the magnitude of the error (to the order of 10), I think you have your calculations mixed up, you appear to be saying that in the below 3 columns sum ([Impacts]) as 'column1', sum (nullif([Resp 2],0)) as 'column2', sum (nullif([Resp 2],0)/nullif([Impacts]*1000,0)) as 'column3' the third column is not giving you the same result as if you did the (column2 / (column1 * 1000) in excel, however this is not the same formula. I think what you need is: NULLIF(SUM([Resp 2]), 0) / (NULLIF(SUM(Impacts) * 1000, 0) AS [Resp 2%] Imagine this simple example (I have removed the NULLIF operators to make the difference

Categories : SQL

Generating a new Excel while retaining old formula from template Excel file
If you have access to the Cell, simply call: String formula = cell.getCellFormula(); Note though that this will only work if the cell is formula cell. IE: cell.getCellType() == Cell.CELL_TYPE_FORMULA. If the cell is not a formula cell, you're going to end up with an IllegalStateException as described in the API

Categories : Java

Excel VBA Formula not working in German excel
This wouldn't work under English locale, too. * & is not a valid sequence of operators. My psychic powers tell me you want to embed Calc into the formula as a literal, in which case it should be Range("A2").Formula = "= B2 *C2 * " & Str$(Calc) You have to use Str to have Calc converted into a string according to the English locale, required by Formula. You could leave it as is if using FormulaLocal.

Categories : Excel

If/Then Excel Formula
You need to put your return values inside quotes, otherwise Excel assumes that TP is a valid name in the workbook (i.e., an address, named range, or variable). =IF(A2="160850","TP",IF(A2="202006","BL",IF(A2="203646","MM",IF(A2="203917","KT",IF(A2="200265","MP",IF(A2="201447","JB",IF(A2="170566","VB"))))))) There may be additional errors, but this one is apparent.

Categories : Excel

set sum formula in excel using vba
Try something like this. For cl = 2 To 5 ColName = Left(Cells(1, cl).Address(False, False), 1 - (cl > 26)) Worksheets(5).Cells(4, cl).Formula = "=SUM(" & ColName & "4:" & ColName & "6)" Next cl

Categories : Excel

Excel nested IF formula
Your if statements are backwards you should check for bigger numbers first in this case otherwise the first if is always true. so 7 is falling true on >= 3 therefore you get 1 + 1 = 2 EDIT - a better way to do it would be to take your J4 divide it by 3 and return the value without the remainder QUOTIENT(J4, 3)

Categories : Excel

Converting an Excel formula to C
I am not an Excel function wizard, but am pretty sure the following three functions may help: time(), localtime() and mktime(). Each is ANSI C and well documented. By getting current time, then passing it back and forth between the functions (either by pointer to struct or time_t value) you can manipulate or inspect different values for time. Simple example: #include <windows.h> #include <ansi_c.h> void timeFunc(void); void main(void) { timeFunc(); } void timeFunc(void) { struct tm *today; struct tm *modified_time; time_t secCntGMT1900 = 0; time_t modifiedTime; // Get GMT time in seconds since 1900 time( &secCntGMT1900 ); // Convert GMT time to local time to local time format today = localtime( &secCntGMT1900 ); //modif

Categories : C

MIN INDEX Excel Formula - Where "IF" is a value
If you don't mind an array function (the first one that came to my mind), you can use this: =MIN(IF(S2:S8="Outstanding",N2:N8)) And change the height of the ranges accordingly. Since this is an array function, you have to use Ctrl+Shift+Enter to make it work, instead of enter, after typing the formula.

Categories : Excel

POI Excel formula translated?
Take a look at this question. As stated in the answer, Apache POI doesn't support multiple languages, so you will have to use the English formulas to make it work.

Categories : Java

Excel formula with OR and AND and possible lookup
The formulas you'll need to use are IF, OR, and AND. Assuming you're familiar with IF, AND and OR can be used together to evaluate multiple conditions. In your case, your formula could look something like this: IF(AND(OR(A1="x",B1="x"),OR(C1="x",D1="x",E1="x")),"YES","NO") For your other conditions, you can move the OR conditions as you need, or add/remove conditions within the OR/AND statements.

Categories : Excel

How build formula IF in Excel?
Not tested, but you can just nest IFs, putting another IF inside the true or false clause. Arbitrarily assuming your value of H is in cell A2: =IF(OR(A2 = "V", A2 = "KR"), 0, IF(A2 >= 7, 1, 0.5))

Categories : Excel

MS Excel Formula Correction
If it is acceptable to use Visual Basic for Applications (VBA), create a macro and define the following function in it: public function GetCode(s as string) as string GetCode = "" codes = split( _ "SIPC|HIU|GMC|CNS|LCSM|RoHC|GL1|GL3|GL2|URRC|UPHY|UHAL|UMAC", "|") for each code in codes if InStr(s, code) > 0 then GetCode = code exit for endif next end function You should then be able to access the function in your formula as follows: = GetCode(J2)

Categories : Excel

understanding and excel formula I have been using
1048576 (2 to the 20th power) is the maximum number of rows in a worksheet in Excel 2007 and later. (In previous versions, it was 65536, or 2 to the 16th power.) Basically, the N$1:N$1048576 refers to "all the cells in column N". However, for safety reasons, you should change that part of the formula to the simpler N:N - in fact, if I copy your formula, click on an Excel cell, and press Ctrl+V, Excel does that replacement automatically.

Categories : Excel

Excel formula for just M-F and T-S of each month
Well, sure, you can use this formula to avoid the blank cells in between the dates: =IFERROR(INDEX($A$1:$A$31,SMALL(IF(MOD($A$1:$A$31,7)>1,ROW($A$1:$A$31,9^99),ROW())),"") This formula (for Monday to Friday) needs to be called with Ctrl+Shift+Enter. After doing this for the first date, you can drag down to the bottom. For the one with Tuesday to Saturday, it's a bit longer; =IFERROR(INDEX($A$1:$A$31,SMALL(IF(((MOD($A$1:$A$31,7)>2)+(MOD($A$1:$A$31,7)=0))>0,ROW($A$1:$A$31),9^99),ROW())),"") But at the same time, you have to call this function with Ctrl+Shift+Enter as otherwise, you won't be able to get the other dates (you will get #NUM! instead for the other dates).

Categories : Excel

VBA Excel: not statement & formula
You will need to give us more code, as the following works: Public Sub test() MsgBox Not myfunction() End Sub Function myfunction() As Boolean myfunction = False End Function

Categories : Excel

Why does this Excel formula work?
To answer the first part of the question - the curly brackets indicate an array formula (function). The PERCENTILE function is one of them. To enter the formula you need to use: CTRL+SHIFT+ENTER, pure ENTER is not enough. You can read more about it here:

Categories : Excel

Excel VBA Formula Format Issue
Use this instead: ActiveCell.Formula = "=MODE.SNGL('Data'!D:D)" Or if you need to use .FormulaR1C1 property you could do it this way: ActiveCell.FormulaR1C1 = "=MODE.SNGL('Data'!C4:C4)"

Categories : Excel

Excel formula for merging rows
If your data is in A:C starting in Row2 with labels in Row1, enter =C2 in E2 and: in D2: =IF(A2=A3,"",COUNTIF(A:A,A2)) and copy down in E3: =IF(A2=A3,E2&", "&C3,C3) and copy down then copy A:E to a new sheet and Paste Special Values there. Filter and select Blanks in ColumnD. Delete these and ColumnsC&D.

Categories : Excel

Excel Array formula for sum if combination
Are you getting a formula error for certain inputs? If so, I would suggest breaking the formula into smaller and smaller constituent parts until you are able to successfully isolate the portion causing the error.

Categories : Excel

Excel formula with double quote with C#
I have not succeed to find the reason why there can be no double quotes written into an excel formula. However there is a solution to avoid double quotes: string formula = "=COUNTIFS({0}!E:E, {1}, {0}!A:A , CONCATENATE(CHAR(62), CHAR(48)))";

Categories : C#

Excel Formula Not Updating Cell
You have to explicitly trigger a recalculation. See the Recalculation of Formulas section here about why & how to do it.

Categories : Java

Excel formula to find reference to top row
Though with limited testing, this seems to work if Customer in your PT is in E3 and the formula is placed somewhere convenient in Row4 and copied down to suit: =IF(E4="A",MAX(GETPIVOTDATA("Val",$E$2,"Customer","A","Item","a"),GETPIVOTDATA("Val",$E$2,"Customer","A","Item","b"),GETPIVOTDATA("Val",$E$2,"Customer","A","Item","c"),GETPIVOTDATA("Val",$E$2,"Customer","A","Item","d")),IF(E4="C",MAX(GETPIVOTDATA("Val",$E$2,"Customer","C","Item","a"),GETPIVOTDATA("Val",$E$2,"Customer","C","Item","b"),GETPIVOTDATA("Val",$E$2,"Customer","C","Item","c"),GETPIVOTDATA("Val",$E$2,"Customer","C","Item","d")),"")) Note that it is hard coded for A and C being your top customers and only caters for the four Items you show. Also it returns 5 and 7 rather than ItemD and ItemC, though I think it could

Categories : Excel

Excel VBA setting cell value to a formula
Something like.... Sheets("Standalone Non Clustered").Range("G10").Formula = "=CEILING(Worksheet!$G$9*(1+Worksheet!$G$10+Info!$B$14),5)" Sheets("Standalone Non Clustered").Range("G10:P10").FillRight

Categories : Excel

Excel formula only working on first cell
First, delete everything in the missing groups column (or copy it somewhere else, just in case). then enter this formula in C2 =IF(ISNA(MATCH(TRIM([@[All Groups]]),TRIM([Site Collection Groups]),0)),TRIM([@[All Groups]]),"") Then press ctrl+Shift+Enter (instead of just enter) This will trim both the single entry for column A and all of the entries for column B. It also uses table references for everything (instead of referencing A2, etc).

Categories : Excel

Excel formula to return the value that is not equal to "x"
If I understand you correctly =IF(B1=TRUE,A1,"") Add this formula to cell C1 and drag down.

Categories : Excel

Formula required for recoding in Excel
I would say to put in E7 : ="VAR" & IF(D7=1,4,IF(C7=1,3,IF(B7=1,2,IF(A7=1,1,0)))) where A is the column of VAR1, B of VAR2, C of VAR3 and D of VAR4. Just place the formula inside the column E (check the indices ; the formula above is for the line number 7 (E7))

Categories : Excel

excel "IF" formula returning "0" (it shouldn't)
Possible answer: The "IF" formula will only return the "0" value in these conditions: 1. "Value_if_true" = 0 AND Logical_test = TRUE 2. "Value_if_false" = 0 AND Logical_test = FALSE 3. "Value_if_true" = "0" AND Logical_test = TRUE 4. "Value_if_false" = "0" AND Logical_test = FALSE 5. "Value_if_true" = (NULL) AND Logical_test = TRUE 6. "Value_if_false" = (NULL) AND Logical_test = FALSE According to your screenshots, you have strings in both "Value_if_true" and "Value_if_false". So we must check how Excel are evaluating these strings: In Excel (2010), go into "Options" > "Advanced" > "Lotus Compatibility Settings for: [YourSheetName]" Check if the "Transition formula entry" option is checked. If it is, then you probably found your problem. You just need

Categories : Excel

Turning an excel formula into a VBA function
Here is a simple solution... Building_name = ??? Date = ??? Last_Row = Range("A65536").End(xlUp).Row For i = 1 To Last_Row if cells(i,1).value = Building_Name Then if date >= cells(i,4).value Then if date <= cells(i,5).value Then first instance = cells(i,6).value end if end if end if next you should add a test at the end to avoid the case where there is no first instance in the table

Categories : Excel

Excel formula Comparing two arrays
If I understand right, you want to populate the calendar with the start dates from xTable with TRUE when found and with FALSE otherwise? You can use this formula in cell B2 and fill it right and down: =IF(COUNTIFS($A$13:$A$19,$A2,$B$13:$B$19,B$1*1)=0,FALSE,TRUE) I'm using B$1*1 just in case you have the dates stored as number in row A1. Otherwise, if they are stored as date but formatted as dd (or equivalent), use DAY(B$1) instead. The formula will count the number of occurrences of a row with the corresponding name and start date of table xTable using COUNTIFS. If none are found, it will return 0, which yields to FALSE.

Categories : Excel

How do I convert this Excel formula into VBA code?
You need to escape your quotes. ActiveCell.Formula = "=IF(O6=""Hand"",""Manual Entry"",IF(O6=""JET"",R6,IF(O6=""COKE"",""Red Bull"",IF(O6=""Freight"",""Logistics"",IF(O6=""TAX"",""Tax"",IF(O6=""TRANSFER COST"",""Transfer Cost Transactions"",IFERROR(IF(FIND(""INV#"",R6,1)>=1,MID(R6,FIND(""INV#"",R6,1),10),""""),"""")))))))" And use .Formula since you're using specific cell names that are not relative to the currently selected cell.

Categories : Vba

excel formula non trivial nested OR
I think you are talking about nested IF statement Stick numbers 1, 2, 3 in cells A1, A2, A3, then in Cell B1 stick the below formula and drag it down =IF(A1=1,"A happens",IF(A1=2,"B happens","Neither A or B")) And your results

Categories : Excel

Error with Nested Excel IF formula
2 Issues: Missing parenthesis in the ISBLANK function. (On second thoughts, this looks like a copy/paste issue.) Missing cell number in the LEFT function. Should be: =IF(ISBLANK(CG3),"",IF(LEFT(CG3,2)="/m","mcat||"&CG3,"icat||"&CG3))

Categories : Excel



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.