Tag Info

New answers tagged

0

You might want to shorten the formula somewhat with EDATE and EOMONTH.              The formula in B2 is, =YEAR(EOMONTH(EDATE(A2,6),0)+(DAY(A2)>19)) Fill down as necessary. I've applied a custom number format using F\Y 0000. This leaves the year as a true number that can be used in future calculation while visibly displaying the fact that it is an ...


0

I think you are on the right track but it may be better to push the current date ahead one month rather than subtract 12.             The formula in B1 is, ="Q"&INT((MONTH(EOMONTH(A1,0)+1)-1)/3)+1&CHAR(32)&YEAR(EOMONTH(A1,0)+1)


0

Awww, figured it out. I had to put in a nested if to check if the month = 6 and date is >= 20. =IF(AND(MONTH(P2)=6,DAY(P2) >= 20),YEAR(P2)+1,IF(MONTH(P2)>=7,YEAR(P2)+1,YEAR(P2))) I don't like to use nested IFs, but it seems like a necessary evil this time.


1

In column B, sheet 2, use a VLOOKUP with an IFERROR test that leaves a blank if there is no matching student number. Cell B2 would be: =IFERROR(VLOOKUP(A2,Sheet1!A$2:B$1000,2,FALSE),"") (Use the actual range.) Copy that down column B. Any student numbers that are not found on sheet 1 will show as blank.


0

You can use an AVERAGE array formula, e.g. =AVERAGE(IF((Race=1)+(Race=2),Pupil_Score)) confirmed with CTRL+SHIFT+ENTER The "+" effectively gives you an "OR" You can easily add extra conditions by adding IF functions, e.g. assuming "range1" needs to be "x" =AVERAGE(IF((Race=1)+(Race=2),IF(range1="x",Pupil_Score)))


1

I don't have Excel to test these formulas right now, but I thought you could try one of the following: =AVERAGEIFS(Pupil_Score,Race,">=2",Race,"<=3") This works only if race numbers are adjacent. The next one needs to be confirmed with ctrl+shift+enter instead of enter: =AVERAGEIFS(Pupil_Score,Race,{2,3}) Not sure if it will work though. Give it ...


1

The AVERAGEIFS criteria come through as AND conditions. You require an OR condition since any one row could not be both 1 and 2.          In the above, the sum of the blue/greens is 23, their count is 5 making the average 4.6. The formula in E2 is either, =SUMPRODUCT((C2:C8)*(B2:B8=F1:G1))/SUMPRODUCT(--(B2:B8=F1:G1)) ... or with the colors hardcoded as, ...


0

Based on what you said in the comments you can do this one of two ways. You can either make a conditional statement based on their input or you can make the assumption that the users won't fill out both sections of data. If you are using a conditional statement use this: =IF(O9="",IF(S9="",HOURS(K9-G9),HOURS(O9-S9)),HOURS(O9-S9) You are basically saying ...


1

In the find box, hit options to search the whole workbook.


0

I solved it like this (not with pie chart - not a fan of pie charts) Copy one of the two charts under the two charts, this will become the "legend" Delete legend of both the main charts Delete axes and lines from the "legend" chart Make the drawing area of the graph teeny tiny Make the legend area as large as the chart area Change the legend area's fill to ...


0

I had this exact same problem a few weeks back. Excel doesn't seem to remember dynamic names for charts/graphs data ranges. The only workaround I found was to create a pivot chart. Create a pivot table based on data in your table range 'UW ATO'!$B$10:$B$15 (or however long you want it to be) and be sure to include the adjacent columns that contain axis ...


0

I created the following VB code to resize the header row when a cell within a range (B2:B1500) because date values above 12/28/2014 would cause the header to show a warning that these dates, in a timesheet, would go into Week1 of 2015: Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the ...


1

Reference How to convert a numeric value into English words in Excel This article contains sample Microsoft Visual Basic for Applications functions that you can use to convert a numeric value in a Microsoft Excel worksheet cell into its equivalent in English words. How to create the sample function Called SpellNumber Start Microsoft Excel. Press ...


0

try opening the file in other apps that can open spreadsheets.... Open Office, Star Office or whatever the hell they call it, Google Docs even do you have a unix OS available? Try porting the file there and getting someone to open it for you, you might recover more than what you bargained for, or partially recover following the above, I'm not knowledgable ...


0

I would use the Power Query Add-In for this - it has a Group By command that can produce a list of the unique values. https://support.office.com/en-au/article/Group-rows-in-a-table-e1b9e916-6fcc-40bf-a6e8-ef928240adf1 Your exact requirements are a bit unclear, but Power Query has lots of data manipulation functionality to automate any further tweaks ...


0

The title and description requests are slightly different. You can use the method in http://www.extendoffice.com/documents/excel/770-excel-find-unique-values-between-two-columns.html to solve either one.


0

Run Repair The first thing to try is to open it with the Repair option in Excel. You can do this by clicking on File and then Open. In the Open dialog box, single click on the Excel file and then click on the small arrow next to the Open button. Go ahead and click on Open and Repair and you’ll get a message asking if you want to repair, which will ...


0

Lookup functions can return either a location or a value at a location, depending on which one you use. VLOOKUP returns a value. You want to use another function that performs the same lookup as VLOOKUP but returns the location. MATCH does that. Both VLOOKUP and MATCH work with relative locations--you define a range where the action will take place and ...


0

this works for me: =if(sum(calender[Date])>0;if(isblank(sum(Sales_SalesOrderDetail[LineTotal]));0;sum(Sales_SalesOrderDetail[LineTotal])))


0

If you only want this to affect the way the data is displayed, then instead of using a formula you can use conditional formatting. I can't think of a general solution that would bracket every other column, but you could bracket the second and then use the Format Painter to copy the first two columns' formatting across your data set: Let's say you have data ...


0

Opening excel with a command line with option solve it!! excel.exe /s "ABSOLTE PATH OF EXCEL FILE.XLS"


0

Power Query is a great tool for getting raw data into shape. For modelling data and adding advanced calculations, I would also use the Power Pivot Add-In from Microsoft. Power Query can feed directly into a Power Pivot Model. This site is probably the best resource: http://www.powerpivotpro.com/what-is-powerpivot/ I think the strength of this approach is ...


1

Adding upon Excel: check PowerQuery from Microsoft (it's free). Any modern BI tool can do what you asked: SAS, Informatica, Cognos, MS SQL reporting server, Oracle BI, etc. See: this list for example.


0

You've receive a comment on how to put the upper-case equivalent of the typed input into another cell with a formula. To replace the value in the cell itself you would require some form of VBA event macro. AFAIK, there has never been an automated upper case conversion but I can recall a couple of database products that did and form based input could do the ...


-3

Yes it is. Please try the following: =sum(C4:C) It will sum all rows in C column starting from 4th row


0

To keep the random value formulas from updating, you need to overwrite them with their values. To do this, copy the range and Paste Special > Values back into the same range. This will remove the formulas and leave only the values. If you want to disable manual editing (e.g., accidental deletion or keyboard input), you'll need to lock the range with the ...


-1

You did not specify the exact format, not attached a screenshot, so its a bit hard to guess. Lets assume the following Sheet1 A B C D E name date unit_1 unit_2 unit_3 John 1-2-3 3 9 7 John 2-2-3 9 13 16 Maria 9-10-4 10 8 7 Maria 13-5-4 6 3 ...


1

Create a duplicate row under the first one and add the formula =CONCATENATE("(",B1,")") It will add the parenthesis on the value of B1 or second method to go pro, with the condition of one column with parenthesis one not go with: =IF((MOD(COLUMN(),2)),CONCATENATE("(",B1,")"),B1)


2

Tom, the most robust solution would be using the Fuzzy add-in. A number of other VBA solutions have been generated using the Levenshtein distance, but I suggest you try the add-in first. http://www.microsoft.com/en-us/download/details.aspx?id=15011


0

Well I think you have a issu with your Windows username, for example too long or have any don't liked character. Try this: Create a user account in control panel, with administrater privileges, and give this name for the account: apple (if this the problem, must be work the save, becouse not have any unwanted character). I also suggest for you, save as in ...


0

If the cells are truly blank or if they contain formulas that may return empty strings (e.g. "") then the COUNTBLANK function has been available since at least Excel 2003. COUNTBLANK will count an empty string as a blank while COUNTA will count the empty string as a value. In W2 use the following formula. =COUNTBLANK(B2:R2) When all of the cells in the ...


0

From your content I believe that you are having issues in opening certain excel files and not the whole files. This might be due to file corruption issue. To fix this corruption you can use file--> Open button, and then click Open and Repair. For various recovery option check the Microsoft article


0

You might want =17-COUNTA(B2:R2) where COUNTA() counts the non-empty (non-blank) cells in a range.  But part of your question makes it sound like you are putting a value of 17 into cell W2 and then modifying (decrementing) that cell.  That's not possible with formulas; to actually change a cell you need to use VBA.


0

Using the scroll bar from among the Forms controls? It's the more robust. However, the scroll bar from the ActiveX controls is a bit fancier, and the chart updates while you drag the little handle without waiting for you to release it. ActiveX controls don't work on the Mac, and they tend to make your workbook unstable.


3

=IF( D17<=200%, 'THEN C17*130%, 'ELSE IF(D17<=150%, 'THEN C17*120%, 'ELSE C17*140%) ) You need to rethink the logic. As written you will ALWAYS have C17*130% executed when D17<=200% is true. =C17*IF(D17<=150%,120%,IF(D17<=200%,130%,140%)) should work - untested. The double IF will return ...


0

=IF(D17<=200%,C17*130%,IF(D17<=150%,C17*120%,C17*140%)) Your first if checks D17 <= 200%, and its false checks if D17 <= 150% If it's not below 200, why would it be below 150? You need to start with the most restrictive if - IF(D17<=150%,C17*120%,IF(D17<=200%,C17*130%,C17*140%))


2

I will explain how to put this macro into Excel as simply as I can. To add a macro, press Alt + F8. This will open the Macro window. Then type a name for a new macro and click Create. VBE will open and you can paste the below code in between Sub and End Sub. Dim WordApp As Word.Application Dim myDoc As Word.Document Application.ScreenUpdating = ...


0

A step-by-step guide for the perplexed: Press the Windows key Type "intl.cpl" and hit Enter. Click "Additional Settings". Go to the "Numbers" tab: Change the Decimal Symbol to a dot. Change the "Digit Grouping Symbol" to a space. Change the "List Separator" to a comma. Go to the "Currency" tab: Change the Decimal Symbol to a dot. Change the "Digit Grouping ...


1

This would seem to reorganize the sample data you provided correctly. Sub mcr_fix() Dim rw As Long, c As Long With ActiveSheet For c = 1 To 2 With .Cells(1, 1).CurrentRegion.Columns(c) .Cells.Sort key1:=.Columns(1), order1:=xlAscending, _ Orientation:=xlTopToBottom, Header:=xlYes ...


0

Try in cell C2 and copy down =IF(COUNTIF($A$2:$A$7,A2)>1,A2&" "&SUMPRODUCT(--($A$2:$A$7=A2),--(B2>$B$2:$B$7))+1,A2) Adjust formula to your range. Don't use whole column ranges like A:A or B:B inside the Sumproduct function. Use the real start and end row instead, like $A$2:$A$500 -- otherwise the calculation may take a long time.


2

The number of hours will be simply the next business day at 9:00 AM minus the starting time (formatted as [hh]:mm to allow the display of >24 hour periods). The trick will be getting the number representing the next business day at 9:00 AM correct.         The formula in B2 is, =WORKDAY(A2,1*(HOUR(A2)>9))+TIME(9,0,0) This makes the formula in C2, ...


0

Type the name and year in the cell using one of the two font sizes you want. In the bar at the top that displays what you typed highlight the portion you want to re-size. Then either right click and select the new font size or go to the Home, Font section and select it.


0

Here is my take on your sample data and requirements. Note that I have expanded your sample data to demonstrate how multiple scenarios are dealt with.           The standard formulas in D2:G2 are, D2 is =IF(COUNTIF(A:A,"Widget")>COUNTIF(D$1:D1,"Widget"),"Widget","") ` E2 is ...


0

Shift+downarrow gets you to the bottom of your selection set AND adds the next cell also. So if you are in Column A you highlight row 1 thru 1000, apply the method stated to jump to cell A1001. So go back up 1 cell for your actual end point of the original selection set.


0

Click on the chart you've created. Under Chart Tools, click the Design tab. Click Select Data. In the Select Data Source window that pops up, you should see two main boxes: Legend Entries (Series) and Horizontal (Category) Axis Labels. Under Legend Entries (Series), click the Add button. In "Series name," you can type in whatever you want to name it as ...


0

Change the file extension from ".csv" to ".txt" on the file you are having problems with. Open excel by itself. Don't click on the file to open it. Click "Open", change the file types to look for from "All Excel Files" to "All Files" Find your file and click open. Since Excel doesn't auto format txt, it will then take you thru the wizard. Makes sure the ...


1

Updated formula. Finds the cell with the first type and second type and looks for the word widget between it. If it exists return the value beside type if not return blank. =IFERROR(IF(MATCH("widget",INDIRECT("A" & SMALL(IF(A:A="type",ROW(A:A),1000000),E1) & ":A" & ...


1

If your formula returns "1" or "0" then the quotes make those text values and SUM will ignore text. Remove the quotes and use just 1 and 0 and the sum function will sum accordingly... .....alternatively, as counting 1s is the same as summing them you could use COUNTIF to count "1"s without changing your original IF formula, e.g. =COUNTIF(J2:J2013,"1")


0

You can do this with an IF AND statement like: =IF(AND(A1="Widget",A2="**Type**"),B2,"") That basically tells Excel IF the text "Widget" is in a cell above a cell with the text "**Type**", enter the data from the cell in column B next to "**Type**", otherwise, leave it blank. You end up with this: If you want the data (072 in this example) to show up ...


0

I don't believe there is an automated way to do this, but you can "build" it manually. First you need to create single labels, which you can do by concatenating Set and Type. Use column E for that. So E2 would be: =A2&B2 for S1A or =A2&"-"&B2 if you want a hyphen in the middle: S1-A Copy that down the column. Now create a ...



Top 50 recent answers are included