You can use something like: =IF(C2-(C2*(G2/24))>0,C2-(C2*(G2/24)),0) to return a 0 result if the result would have otherwise been negative....
excel,google-spreadsheet,excel-formula,array-formulas
In a google spreadsheet you may want to try: =ArrayFormula(iferror(SORT(ROW(A1:A),SORT(ROW(A1:A),A1:A,1),1)-MATCH(A1:A,SORT(A1:A),0)-ROW()+2)) Example sheet...
Apply the golden rule of spreadsheet design: Data in one sheet, reports in other sheets. You are not doing yourself a favour by spreading the data entry over several sheets, one per month. This will make reporting very difficult. Put all the data in one sheet and add a column...
If your table starts in cell A1, with the first start date in cell B2, try =SUMPRODUCT(--(C2:C6-B2:B6<=30)) Edit after clarification: =SUMPRODUCT(--(A2:A6>124),--(A2:A6<127),--(TODAY()-B2:B6<=30)) ...
For your example, assuming start times/dates in B2:B6 and end times/dates in C2:C6 you can use this formula to get the total (non-overlapping) hours =SUMPRODUCT((COUNTIFS(B2:B6,"<"&MIN(B2:B6)+ROW(INDIRECT("1:"&ROUND((MAX(C2:C6)-MIN(B2:B6))*1440,0)))/1440-1/2880,C2:C6,">"&MIN(B2:B6)+ROW(INDIRECT("1:"&ROUND((MAX(C2:C6)-MIN(B2:B6))*1440,0)))/1440-1/2880)>0)+0)/60 That shows the result as a decimal number of hours. For that example I assumed you want to use the whole range - if you...
arrays,excel,if-statement,excel-formula,match
This should be as simple as multiplying in another condition that returns 1/0. You will get a 0 unless both conditions are met. {=LARGE( IF(Table_owssvr_1[HQ Name]=B1,1,0)* IF(Table_owssvr_1[Status]="Live -3",1,0)* IF(ISNUMBER(Table_owssvr_1[Live Date]),Table_owssvr_1[Live Date],0), 1)} ...
excel,excel-vba,excel-formula,excel-2007
In an unused cell to the right use this formula, =OFFSET($A$1, INT((ROW(1:1)-1)/2), COLUMN(A:A)-1+MOD((ROW(1:1)-1), 2)*2) Fill right one column and fill down as far as necessary. Your results should resemble the following. ...
sql-server-2008-r2,excel-formula
Interestingly enough, Excel is unable to recognize SQL's data type dateas such. If you want Excel to recognize it as a date then you will have to convert it to datetime. So, if you convert the date field into datetime with a cast in the query then Excel will recognize...
Assuming the date is actually formatted like that and not stored as text, this should work: =IF( [Description] = "Diesel Oil", INDEX( TaxStatus!$B:$B, MATCH( DATE( YEAR( [Date] ), MONTH( [Date] ), 1 ), TaxStatus!$A:$A, 0 ) ) * [ValueEx.SalesTax], IF( OR( [Description] = "Furnace Oil", [Description] = "Lubricants" ), [ValueEx.SalesTax]...
This can be accomplished in 2 parts, one formula for the Product, and one for finding the QTY. Product Formula So starting with the Product formula I was able to accomplish this by using: =IF(SUMPRODUCT(($A$5:$A$12=$E2)*($B$5:$B$12=INDEX($B$14:$B$18,MATCH(F$1,$A$14:$A$18,0))))>0,INDEX($B$14:$B$18,MATCH(F$1,$A$14:$A$18,0)),"") Explanation Assuming Product a falls under column F I will explain this using Saul This...
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)...
You have chosen unfortunate sheet names. "=SUMIF('1'!C2,C7,'1'!C[2])" The '1' is the sheet name. Sheet names can be wrapped in single quotes, but you only need to do that if the sheet name contains a space. The ! is a separator between sheet name and cell reference. C2 means column 2,...
I think this does the job (assuming you have your data in B1:F1):- =if(b1="",0, SUM(1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6})),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{2;3;4;5;6;7}))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6}))+1),"/"&$A$1:A$1&"/"))))...
excel,vba,excel-vba,excel-formula
There is a fairly standard method for retrieving unique values from a column but not multiple columns. To achieve the retrieval from multiple columns you need to stack multiple formulas together with the processing being passed to successive columns one the earlier formula errors out. The array formula in...
excel,excel-formula,excel-2013
You need to provide an extra layer of processing so that the formula does not halt at the first result of the ISNUMBER function. A SUM function wrapper should be sufficient. =SUM(--ISNUMBER(SEARCH(G2:G7, A1))) Array formulas need to be finalized with Ctrl+Shift+Enter↵. Any non-zero result will mean that at least one...
excel-formula,vlookup,excel-2013
Try using the Index Match method. It's an alternative to Vlookup which doesn't require data to be sorted and can therefore be of more use. The typical structure of this method is (the text inside the asterisk will give the ranges specific to your sheet: =INDEX (**Column from which you...
It looks as if your data has merged cells. If you want to keep life simple, avoid merged cells. Your data has the same 1,2,3 sequence in each section x, y and z. I assume that these values will always be the same. With the data laid out like in...
excel,indexing,excel-formula,vlookup
With 125 typed into D2 the first will return 101 from Range A; the second will return 500 from Range B. =INDEX(A:A, MATCH(D2, A:A)) =INDEX(B:B, MATCH(D2, A:A)) This method is known as a INDEX/MATCH function pair. When seeking approximate matches, the values to be examined must be in ascending...
There are a few related ways to do this, most involving SUMPRODUCT. If you don't like the solution below and would like to research other ways/explanations, try searching for "rankif". The function looks up the Class and Value columns and, for every value in those columns, returns a TRUE or...
With data in column A, in B2 enter the array formula: =IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$11),0)),"") In C2 enter: =COUNTIF($A$2:$A$11,B2) and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. ...
If you need to get the amount of customers for that month and product, you can just modify that sumproduct function to get this: =SUMPRODUCT($B$3:$I$5*($B$1:$I$1=TheMonth)*(($B$2:$I$2="Actual Customers")*($A$3:$A$5=TheProduct))) That (for me) returned the value 56 for p C and 1951 for p b, so I'm pretty sure you would just have to...
You need a cell where you specify the month you want to look up. Give that cell the name "TheMonth". You need a cell where you specify the product you want to look up. Give that cell the name "TheProduct". Then, assuming your screenshot starts in cell A1, you can...
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 ?...
You could use a table in the worksheet and use Vlookup, or do it all in one formula like =LOOKUP(A1,{"California","Kansas","Texas","Washington"},{3,3,1,2}) The first array must be sorted ascending. Edit: You can catch errors by wrapping the statement into an IFNA() like this: =IFNA(LOOKUP(A1,{"California","Kansas","Texas","Washington"},{3,3,1,2}),"not found") "Shorter" or "more efficient" really depends on...
excel,excel-formula,conditional-formatting
I don't think you can do that with just one conditional formatting formula. Here's a solution though: Use this array formula to find the lowest unique value: =INDEX(A1:A4,MATCH(MIN(IF(COUNTIF(A1:A4,A1:A4)=1,A1:A4,"")),A1:A4,0)) Be sure to confirm the formula with Ctrl+Shift+Enter. Then use conditional formatting to format a cell with that value. So if you...
excel,if-statement,excel-formula,excel-2007
With a changed lookup array in Sheet2: I suggest: =ROUND(C2*IF(B2="PKG",INDEX(Sheet2!C$2:C$7,MATCH(C2,Sheet2!B$2:B$7,1)),IF(B2="POPULAR",INDEX(Sheet2!C$8:C$11,MATCH(C2,Sheet2!B$8:B$11,1)),INDEX(Sheet2!C$12:C$14,MATCH(C2,Sheet2!B$12:B$14,1)))),2) in D2 copied down to suit....
If 06/08/05 should be 06/08/15 then you can return 3 with, =SUMPRODUCT((LEFT(B$2:B$999,4)="test")*(MONTH(AE$2:AE$999)=6)*(YEAR(AE$2:AE$999)=2015)) For versatility it would be better to put test in a cell (e.g. D2) and call it with one of these, =SUMPRODUCT((LEFT(B$2:B$999, LEN(D2))=D2)*(MONTH(AE$2:AE$999)=6)*(YEAR(AE$2:AE$999)=2015)) =SUMPRODUCT(ISNUMBER(SEARCH(D2, B$2:B$999))*(MONTH(AE$2:AE$999)=6)*(YEAR(AE$2:AE$999)=2015)) The FIND function is case sensitive; the SEARCH function is not. A more...
excel,excel-formula,excel-2007
Assuming you start with filtered data already, you need to have the header cell selected. From there: ALT+DOWN, F, A. If the filter is not already created then throw in a CTRL+SHIFT+L at at the start. If you are not in the header row, some combination of CTRL+ARROW keys can...
Let's assume the data below: Step 1: select from cell A2 to A31 (all the data rows under Unit column) Step 2: Click on home tab and click Find & Select. Step 3: Select Go to Special. Step 4: Select Blanks and hit ok. Step 5: On the first cell,...
How about this: =NETWORKDAYS(MAX(A1,C1),MIN(B1,EOMONTH(C1,0))) It might be wise to expand it with some error checking like so: =IF(NETWORKDAYS(MAX(A1,C1),MIN(B1,EOMONTH(C1,0)))<0,0,NETWORKDAYS(MAX(A1,C1),MIN(B1,EOMONTH(C1,0)))) ...
You can use an array formula (Ctrl+Shift+Enter) for this. Set up your list of names somwhere (For example, F1, F2, F3 ...) then next to it (in G1): {=COUNT(IFERROR(FIND(F1,A:A,1),FALSE))} Assuming A is you names column. Than simply drag G1 down to copy for all names....
sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
Excel Formulas support as ended since SSRS 2008 (see Breaking Changes in SQL Server Reporting Services). No Formula Support in Excel In earlier versions of Reporting Services, there was limited support for translating expressions in RDL to Microsoft Excel formulas. In this release, when you export a report to Excel,...
You need to wrap the different OR parameters into curly brackets and enclose the whole Sumif in a SUM function, because Sumif will return an array of values that need to be summed up. Also, the two ranges need to be the same size. =SUM(SUMIF(A1:A10,{"cat","dog"}, C1:C10)) ...
You can use an array formula like this, assuming you have up to 1000 rows of data =INDEX($A1:$A1000,MATCH(TRUE,ISNUMBER(B1:B1000),0)) confirm with CTRL+SHIFT+ENTER...
The Excel Hyperlink() function takes string arguments. You can build the string arguments by concatenating different elements. A concrete formula is a bit hard without knowing your workbook structure. Assuming that you want to link to the cell on the Domain_master sheet, and assuming that the column "Domain" is in...
The issue you are having is if the formula FIND does not find your search term "(" it will output the error value #VALUE!. To correct this please consider the following formula: =IF((IFERROR(FIND("(",A24)-1,FALSE)),LEFT(A24,FIND("(",A24)-1),A24) IFERROR function makes sure if the term is not found it will output FALSE. Hope this helps....
excel,excel-formula,conditional-formatting
Remove any previous attempts at a Conditional Formatting Rule. Select A2:C5 and use Home ► Conditional Formatting ► New Rule. Choose Use a formula to determine which cells to format and supply the following for Format values where this formula is true: =not(or(A2=0, A2=50, A2=100)) Click Format and select...
Gary's Student's answer is correct. I added a couple things. Also, a link to the method: http://blog.contextures.com/archives/2011/07/27/finding-min-if-or-max-if-in-excel/ A distinct list of values can be generated in a couple ways, here's one: And here's links to a method or two for distinct lists. All with array formulas: Ignore Duplicates and Create...
In fact, you are accessing the cell one above the top of the tables's data body. It is the header string value (e.g. RunningTotal) and you receive the #VALUE! error when you try to use the string mathematically with the @Change number. However, the SUM of a string is zero...
excel,excel-formula,worksheet-function,excel-2013,countif
Ugly, but might suit: =COUNTIFS(E:E;">120";E:E;"<139")+COUNTIFS(F:F;">80";F:F;"<89")-COUNTIFS(E:E;">120";E:E;"<139";F:F;">80";F:F;"<89") Counts all the applicable instances in ColumnE, adds the count of all the applicable instances in ColumnF then subtracts the instances where both ColumnE and ColumnF are within the respective bounds - this last part might not be required. An array formula might be more...
If your row variable was called "Year", your column variable called "Month", E1 was one of the cells in your pivot table and your values variable was called "Score", you could do something like =GETPIVOTDATA("Score",E1,"Month","Jan")-GETPIVOTDATA("Score",E1,"Month","Jan","Year",YEAR(NOW())) and repeat for each month. However you would need to leave room under the table...
excel,excel-formula,excel-2010,excel-2013
You could simply turn around your first example and use the correct string: =TEXT(J3, "yyyyMM") Note, I used upper case M. Lower case m means minutes, while upper case M means month. The CONCATENATE is unnecessary. Please also note, that this is prone to localization issues, i.e. it depends on...
excel,excel-formula,comparison
An alternative approach: Assume Date1-Date4 are in A2:A5, and Date5 is in B2: {=OR(B2=A2:A5)} Note that you need to enter the formula as an array formula using Ctrl+Shift+Enter....
I like Joseph's answer, however since I had worked this up, and in the event that a PivotTable (which could also be put on a separate sheet), is acceptable: 1) In the field list drag the ID numbers to the Row Labels, 2) Drag the Course Codes to the Column...
in b3, put =B2+(B$40-B$2)/38 with 38 being 40-2. Then, drag that formula down to B39. See: https://docs.google.com/spreadsheets/d/1SITsLCfSbsP1po6Og6gR3aquQuxUb2iyQrde9sQ2N2M/edit?usp=sharing...
Assuming you go all the way from A to ZZ, this means you are using 26*26 = 676 arguments. I am surprised this function has ever worked for you, since the maximum number of arguments is 255. Since you are using single cells, you should try just using simple addition...
excel,excel-formula,conditional-formatting
Use INDIRECT =ISERROR(MATCH(INDIRECT("Table2[Website]");INDIRECT("Table13");0)) Similar Question Here INDIRECT():Returns the reference specified by a text string. References are immediately evaluated ...
excel,vba,excel-vba,excel-formula
As pointed out in the comments by @siddarth you are looking for: ActiveCell.Formula = "=B4/B5" If you are in a hurry and just need to make the macro work, a compromise on code readability may be acceptable. In that case you could do the following: Start recording a macro Do...
you were close, but not quite, with the array formula, entered with Ctrl+Shift+Enter: =IF(B2=MAX(IF(A2=A:A,B:B)),"Yes","No") ...
excel,vba,excel-vba,excel-formula
I will suggest to loop every lines, and test every columns to your criteria (not NA and colored). If true, copy the colored cell. And add the header line. Here a start code working, need to be adapt to exactly reach your goal. Sub test() Dim aLine As Long Dim...
You can accomplish this with =COUNTIFS(): =COUNTIFS(A1:A2000,"Pasta", D1:D2000,"Sauce") COUNTIFS will take multiple criteria and count the rows where those criteria are true. You can also use =SUMPRODUCTS if you have an older version of excel (2003 or earlier) =SUMPRODUCTS((A1:A2000="Pasta")*(D1:D2000="Sauce") * 1) This will do the exact same multiple criteria count....
If you are using Excel 2013 you can use FORMULATEXT function to get the formula as text, then extract the number, e.g. with formulas in A2 down use this formula in B2 copied down =IFERROR(LOOKUP(10^9,RIGHT(FORMULATEXT(A2),{1,2,3,4,5,6,7,8,9})+0),0) That will extract numbers from the right of the text (up to 8 digits, excluding...
If you want the name in column E and the worked hours in column F then set E1 to =IF(B1>0,A1,"") and set F1 to =IF(B1>0,B1,"") If you want the name and worked hours both in column E then set E1 to =IF(B1>0,CONCATENATE(A1," ",B1),"") and copy down the column. IF(condition, expression...
Are you only trying to get the SLOPE from the linear regression? If so, you can just use the SLOPE function after converting the #N/A to blanks (using IFERROR in a formula). SLOPE will then just toss out the blanks. If you want the intercept as well, use the same...
One solution would be to insert a column in both sheets with a "running count" of instances of the same name. For example, insert col C and in C2 enter =IF(A2&B2 = A1&B1, C1+1, 1). This starts the count at 1 if the concatenated first and last name is new,...
I'm assuming you are using this formula in X13 copied down (or possibly Y12 copied across). Try using this revised formula: =IFERROR(INDEX($F$13:$F$1353, MATCH(1,(COUNTIF($X$12:X12, $F$13:$F$1353)=0)*SUBTOTAL(3,OFFSET($F$13,ROW($F$13:$F$1353)-ROW($F$13),0)), 0)),"") confirmed with CTRL+SHIFT+ENTER The additional SUBTOTAL(OFFSET part works to exclude values that are not visible due to filtering...
excel,vba,excel-vba,excel-formula,breakpoints
Without further information, it looks like you are changing the worksheet, which triggers the recalc. You can suspend recalculation and restore later. let savedCalcMode = Application.Calculation Application.Calculation = xlCalculationManual On Error Goto restore ... restore: Application.Calculation = savedCalcMode If you do this, try to restore even on errors otherwise you'll...
If you are sure that you always have at least one admin in your column, then you can count all distinct values in the column and then subtract one. Assuming your data fall in the range A1:A100, then this formula will give the desired answer =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))-1 If you are not...
excel,vba,excel-vba,excel-formula,average
As for formula solution, if the cell containing "OUTPUT" was C3, the following formula should be put in D4 and pasted downwards. =IF(C4="","",IF(C5<>"","",IF(C3="",C4,AVERAGE(C2:C4)))) This will only work if your top 3 values are at the bottom of every group (model)....
You can nest another IF: This should do what you are looking for: =IF(ISBLANK(M3),"",IF(AND(TODAY()<M3+357),"NO","YES") ...
excel,excel-formula,excel-2013
Commenting in an answer so as to add a picture. But your second formula also seems to work fine: Note that with a 2 in B3, the formula returns B from Sheet_2!B2...
You'd have to setup a second column for Field2 and for Field3. If you assume your dropdowns are located in cell A10 to C10 and your fields are in column A-C then you could go over to column D and make D1 =if($a$10=a1,b1,"") and D2 would be if(countif(d$1:d1,if($a$10=a2,b2,""))=1,"",if($a$10=a2,b2,"")). You could...
You'd better lock the references to use absolute column references like this: put this formula in J and copy down: =SUMIF($C$2:$C$4,$H2,$B$2:$B$4) by add the $ sign to the column refs. ...
excel,excel-formula,worksheet-function
Please try selecting ColumnA, HOME > Styles - Conditional Formatting - New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:: =MATCH(LEFT(RIGHT(A1,8),7),B:B,0) Format..., Fill, select your choice of highlighting, OK, OK....
You can simply wrap your division inside IFERROR() and return 0. {=SUM(IFERROR(F:F/D:D,0)*E:E)} ...
excel,python-2.7,excel-formula,win32com
Adding this to the beginning of the _colorizeTop10Rejets method did the trick self.xl.Calculate() self.xl is the object returned by win32.Dispatch('Excel.Application')...
mysql,excel,excel-vba,excel-formula
If you want to repeat some text a number of times you can use a somewhat complicated formula to do it. It relies on there not being duplicate entries in the Fruits table and no entries with 0 count. Picture of ranges and results Formulas involved include a starter cell...
This should do it: =MAX(600*K6,I6*K6) Work out: 600*K6 (Will either be 75 or 150 depending on if K is 12.5% or 25%) K6*I6 Take the maximum value...
excel,vba,if-statement,excel-formula,excel-match
If I understand this correctly, all we need to do is add an extra array criteria in your LARGE function. You already are getting the 2nd largest based on the customer ID. But we want the 2nd largest based on the customer Id and based on the scheduled date: =LARGE(('All...
By my reading, it seems as if you may be trying to do both tasks in a single formula. This may be possible with a more complex formula, but a straightforward solution is to simply add a column with activity counts to the raw data, and then count the instances...
excel,vba,excel-vba,excel-formula
If you want a formula that works regardless of what surrounds your data, you can get the same result using ROWS, COLUMNS, and MOD along with some absolute/relative ranges. Formula in cell C2 copied down and over. Probably looks a little better with a named range. If you want to...
You can use the following formula: =SUMPRODUCT(IF((A1:A5-B1:B5)>5,C1:C5)) this is an array formula, so press ctrl+shift+enter to calculate the formula. Hope this works for you....
It would seem that the three columns are unique; e.g. there would never be a Beef in the Color column. In that case you can simply query each column, passing back a 1, 2 or 3 as the case may be. =IFERROR(INDEX(Table1[#Headers], ISNUMBER(MATCH([@Name], Table1[Fruit], 0))*1+ ISNUMBER(MATCH([@Name], Table1[Color], 0))*2+ ISNUMBER(MATCH([@Name], Table1[Meat],...
excel,date,excel-formula,worksheet-function,array-formulas
Following 4 named ranges for simplicity in code: "Dividends": DividendDates (column A); DividendsPaid (column B) "AdjClose": StockDate (column A); StockPrice (column B) try (in column C in "Dividends": {=INDEX(StockPrice;MATCH(MAX(IF((StockDates<=A1);StockDates));StockDates;0))} Assuming that the dividend date for which you want to find the adjusted stock price is in cell A1. And copy...
What you could do is add an IF statement to your formula in column Z: IF(AND(W5=0,X5=0),0,SUMPRODUCT(--(ABS($Y$5:$Y$165)>ABS(Y5)))+1) This will make the value in column Z equal to 0 if both W and X are 0. Then you can easily filter these 0s out....
You're going to need to create a separate table that has all unique classifications in the rows and all dealers in the columns (same as yours, but with duplicate rows removed). Then, in each cell, take the average price for that classification*vendor combination. This can be done by using a...
You were close: =IF(AND(OR(D14="CerroVial",D14="Parinas"),E14="Low"),"8%","") All the elements of the AND need to be in the AND(...), just like for the OR, which you had done correctly....
excel,excel-formula,match,worksheet-function,input-filtering
With data like: In D1 enter: =IF(ISERROR(MATCH(A1,B:B,0)),1,"") and copy down and then in C1 enter: =IFERROR(INDEX(A:A,MATCH(ROW(),D:D,0)),"") and copy down. This results in: ...
Two column matches typically come in one of two typical configurations; one being an array formula and the other being a standard formula. Both use variations of an INDEX/MATCH function pair. The standard formula for Quickbooks!A2 would be, =IFERROR(INDEX(Donations!A$1:A$999, MIN(INDEX(ROW($1:$999)+((Donations!B$1:B$999<>B2)+(Donations!C$1:C$999<>C2))*1E+99, , ))), "No match") The array formula version for...
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):...
Using your provided example, this should work for you: =MAX($D2:$D3)-MIN($C2:$C3) ...
excel,if-statement,excel-formula,max,excel-2010
Well, I learned today that the And() function doesn't work inside of an array formula! In any case, try this: =MAX( IF(A3:A29>=$G$1, IF(A3:A29<=$G$2,$B$3:$B$29) ) ) (and don't forget to ctrl+shift+enter)...
I would define the ranges more surgically when using array formulas. Given the small data set, this array formula gives the expected result: =MAX(IF(INDEX($A$2:$A$6&" "&$B$2:$B$6, 0) = A2&" "&B2, $D$2:$D$6)) - MIN(IF(INDEX($A$2:$A$6&" "&$B$2:$B$6, 0) = A2&" "&B2, $C$2:$C$6)) ...confirmed with Control+Shift+Enter to activate the array....
excel,excel-formula,week-number
Using SUMIF is one way. But you need to get your references straight in order to make it easy to enter. Note in the diagram below, the formula: =SUMIF(Weeknums,M$1,$B2:$K2) where weeknums is the row of calculated Week Numbers. Also note that the column headers showing the Week number to be...
This sound an awful lot like you failed to import the data correctly. If you still have the original data, I suggest you import it again and use 65001: Unicode (UTF-8). If you do not have the original data, you can still trick Excel into importing your data again. I...
excel,vba,charts,excel-formula,series
If you use .FormulaR1C1 = Replace(.FormulaR1C1 , "A", L) instead of .Formula = Replace(.Formula, "A", L) the '1004' goes away. Beats me why but it's alway worth trying when '.formula' results in an error. You'll still have to put some thought into the positioning of the charts though. Currently they...
excel,excel-formula,excel-2010
Are you sure that you should be using a ; as opposed to a ,? I get an error for that, and it directs me to $A$30 as you stated.
When you perform math operations in Excel you will sometimes get floating point errors. They come about because Excel is limited to 15 digit precision so significant digits after 15 are ignored. Times in Excel are all percentages of days. Computers calculate in binary so all those decimals have to...
excel,excel-formula,pivot-table
If you're okay with using helper columns, use text to columns to split up your third column into individual columns by using commas as the delimiter. Get everything in one column and then create your pivot table on the resulting data. EDIT: Select C2:C5 and go to the data tab...
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....
excel,excel-formula,array-formulas
Your problem that it is "summing twice" in this formula ={SUMPRODUCT(C,((A<=20)+(C<=20)))} is due to addition turning first TRUE plus the second TRUE into 2. It is not actually summing twice, because for any row, if only one condition is met, it would count that row only once. The solution is...
First you need a table with just one header-row (create a new column for the Arbor .. Values). The value of this column is the name for the diagram line (instead of serie1 etc.)...
You cannot use the MONTH function as a form of sub-function in a COUNTIFS function. You need to add an extra level of calculation with something like a SUMPRODUCT function. =SUMPRODUCT((MONTH(original!A2:A58)=1)*(original!D2:D58="East")) That should count the instances where the month of the date in the Original worksheet's column A is January...
This formula needs to be entered as an Array Formula (Press CTRL+ALT+SHIFT). The data you displayed would be found in the range A1:F2. =SUM(IF(A1:F1>=LARGE(IF(ISNUMBER(A2:F2),A1:F1),3),A2:F2)) I get the 260 this way....
And here is one last approach to this that does not use VBA or formulas. It's just a quick and dirty use of AutoFilter and deleting rows. Main idea Add a reference to a cell =Sheet1!A1 and copy it down to match as many rows as there are in the...
The easiest way is probably to AND the results of a search in each column. This translates to "if not found in D and not found in E then output No". The logic is as follows: In column D | ISERROR(lookup in D) | In Column E | ISERROR(lookup in...
excel,excel-formula,excel-2010
The formula itself is ok, but you need to enter it as an array formula (press CTRL+SHIFT+ENTER when you entered it instead of simple ENTER)