All Questions
Tagged with microsoft-excel-2016 microsoft-excel-2010
76
questions
0
votes
0
answers
20
views
max if functions in Excel to return text from a different row matching the max if conditions
I have an Excel spreadsheet with a summary table that contains credit card charges information from different sheets. The sheets have columns A to F with 'Date', 'Cardholder Name', 'Amount', 'Result (...
-2
votes
2
answers
11k
views
Conditional Formatting with greater than & less than parameters based on date in cell
Trying to conditionally format a cell based on the date in the cell. My ultimate goal is to try and track warranty expiration dates on some of the product we maintain. Within 30 days of expiration ...
- 1
0
votes
1
answer
56
views
Format of Excel drop down list
When choosing from a drop down list in Excel, is it possible for the cells to change the format when you choose a specific subject?
Each subject has a different format for the cells.
- 1
0
votes
1
answer
829
views
Compass bearings on excel
Would like some help creating an excel formula:
Compass bearings are from 0 to 359. I have attached a picture of how it looks like here:
Basically, I am looking for a solution for when I input 2 ...
1
vote
1
answer
2k
views
Excel Formula to display date when cells are filled in
I have a table and I need a excel formula that says the following:
If Cells C1, E1 and F1 are filled out, than today's date will appear. However if Cells C1, E1 and F1 are not filled out, than the ...
0
votes
4
answers
906
views
How do I get VLOOKUP to return all results not just one (using formula not auto-filter)
I have a table full of several orders and the dates. On another page i want to be able to type in a date and for all orders to show from that date, like a VLOOKUP. But, a VLOOKUP would only display ...
- 1
0
votes
1
answer
198
views
Excel Pivot display multiple columns
I had a Salary data month wise now i want to calculate the difference between last 2 months salary field wise
below is the sample data
Required Sample output of single employee.
I have tried using ...
- 103
0
votes
2
answers
221
views
Index Function with error "You've entered too many arguments for this function"
=index(_70Rain,1+int((row(b4)-1/columns(_70Rain)),mod(row(b4)-1+columns(_70Rain),columns(_70Rain))+1)
I just followed the code given in https://www.extendoffice.com/documents/excel/1172-excel-...
0
votes
2
answers
847
views
Excel Custom Ribbon Separators
I was looking for a way to insert vertical separators into a custom ribbon in Excel 2010, which seems to be problematic for everyone. I've searched the planet and been told it can't be done!
- 1
0
votes
1
answer
364
views
Excel: Take on the value of a formula cell when other cell is equal to x?
I know that title isn’t great but essentially:
Let’s say
A1 = some number
And
A2 = some formula involving A1
I want to store values of A2 in a column for different values of A1 so like a function ...
- 1
0
votes
1
answer
80
views
Excel Hyperlink within the same sheet
I have an excel workbook with 2 sheets. Sheet1 and Sheet2. I want to stay on sheet1 and create a hyperlink to sheet2 but I want the information that is available in sheet2 to be displayed in sheet1 ...
0
votes
1
answer
615
views
Highlight all the cells if the keyword in the cell matches to any value of a list
For one of my excel sheet, it is required to highlight the cells that has specific keyword in it.
To solve this, i have created a list called my_list which contains value1, value2 & value3.
I have ...
0
votes
2
answers
48
views
excel - group sheets by columns
I have a bunch of .csv files with the same structure, but it's not guaranteed that they are in same order row-wise. I would like to group them all in a single spreadsheet. The following image draws ...
- 101
0
votes
1
answer
347
views
Create multicategory timeline chart in Excel
I'd like to create an Excel chart showing on the x axis the dates from 1943 to 2021 and on the y axis the relevant category which follows a specific timeframe.
I.e. from 1/1/1943 to 18/01/1943 ...
- 3
0
votes
0
answers
84
views
Checking Whether Two Columns Match any Numbers from a Different Column
I was wondering if anyone knew the best way to use one column as a sort of "criteria" column by which two other columns are searched for any numbers that match that criteria column. However, ...
- 1
0
votes
1
answer
444
views
Excel - count unique days in a start-end date range
Happy new year!
I have an Excel filled with start-end dates (A2:B7).
My interest is to try to get unique days in an interval.
I've made a research but I can't seem to find any solution to this, most ...
- 3
0
votes
1
answer
49
views
Filter to another sheet with criteria from 2 column based on cell name
I would like to add ranges E:J from Fixture! sheet based on Team name only. F: is home games and J: is away games. I can add this to my other sheet as 2 seperate commands I wanted to know if it is ...
- 1
1
vote
1
answer
105
views
Excel: Match a value from a column and then collect values from a row
I want to enter a value in G2, then it will search from column A. In the picture attached it is A26 cell. Then I need to fill up G4 and G5 cell from column B and D.
How can I do it?
- 11
0
votes
1
answer
14k
views
Excel: How to show a floating text box only when I select the cell?
There's a type of text box in an Excel file that is associated with a cell
and appears only when you select that cell uniquely
(e.g., by clicking on it or moving to it with cursor keys).
Hovering ...
- 13
1
vote
2
answers
192
views
Cell Pattern Value to Columns
Is there any way of converting below pattern into columns?
Image is attached for reference to understand better what is the input pattern and what will be output required.
another picture
EDIT: I've ...
- 159
0
votes
4
answers
7k
views
How to List all Fridays in a given Month
I've a question, How to List dates of all Fridays in a given month? Some months have 5 Fridays and some have 4 Fridays in month.
Month of May-2020
e.g. A1=01/May/2020 the result should be in B1:B5 as ...
- 159
0
votes
1
answer
38
views
Transpose some columns of an Excel table
I am not able to achieve going from this format:
To this format:
I could transpose manually, but there are hundreds of items.
- 103
0
votes
0
answers
63
views
Which Excel formula is more elegant for this data?
I'll try to be succinct and I'll provide an image for clarity at the bottom.
The problem:
I have a large set of data consisting of City resources (publicly available) which includes the information at ...
- 1
-1
votes
2
answers
172
views
Excel: How to Transpose a column of data into a row using a formula and not using copy and paste transpose
Within the screenshot, I would like to Transpose a column of data (Column A) into a row (Row 7) using a formula, cell referencing and not using copy and paste transpose. Would someone be able to help?
0
votes
0
answers
55
views
Dynamically change charts from a drop down with a changeable data range
I will be straight to the point, aassuming you got a set of some measured data, and there in a Column A you got a product name, in Column B number of tests you did (always different) and third Column ...
- 121
0
votes
1
answer
330
views
How to set custom format of cell with trailing text as hr/hrs.?
Is it possible to set the custom format of cell if the number is 1.0 as "1.0 hr " and if the cell is having number greater than 1 the format display as "hrs" instead of "hr&...
- 159
0
votes
1
answer
248
views
Requesting Formula for 4-days work and 4-days off including weekends to schedule tasks
Experts,
I need help in formulating the below example for 4-days work and 4-days off based on hours scheduled. I'm able to do for straight days. Please see the details below.
Formulae Used
in cell G9=...
- 159
0
votes
1
answer
981
views
Excel formula to format URL Regex
I need to create Regex for Black listing that takes all variations of URL - http:// or https:// with or without www. I came up with this:
Url:
https://www.example.com/contactus.aspx
Regex:
(https?:\/\/...
- 3
1
vote
2
answers
438
views
Q: Usage of Excel function called as EFFECT()
I am having a question regarding the usage of the EFFECT() function in Microsoft Excel.
I know the basic concept of the function, and I have no issue with it when there are pre-defined compounding ...
- 13
1
vote
1
answer
1k
views
How do I flip a bar chart?
In excel, I have a set of percentages and a set of text labels. When I create a bar graph going horizontally, I see "label 5" at the top and "label 1" at the bottom. There doesn't seem to be a way ...
- 11
-1
votes
1
answer
4k
views
How to automatically update all the relevant cells in Excel when a new row is added
So I have a spreadsheet used for calculating let's say the taxes on something which I purchased. It has a column with the sales tax calculation, state tax, etc. and all each of these columns reference ...
- 1
-1
votes
1
answer
535
views
I want to scan down column 1 and if, for each cell, it matches any of the cells in column 2 I want it to return the entry in column 2 it matches
I want to scan down column A in sheet alpha (alpha!A) which is a list of emails such that if, for each cell A1,A2,...AX, the said cell matches any of the entries in column D of sheet beta (beta!D), ...
- 1
0
votes
1
answer
824
views
How to generate dense rank in excel
I want to generate Rank similar function works like DENSE_RANK() in database product (such as SQL Server).
Here is sample data :
Col1
1
2
3
4
4
So, i want to be generate following sequence.
...
- 101
0
votes
1
answer
101
views
Count for each month-Year, how many times a number (1/2/3) appear in a column range
Experts,
I need help in counting error types(1|2|3) for each month of a year.
As you can see in the image below, cells may have multiple error types for a single entry separated by comma.
I couldn'...
- 159
4
votes
1
answer
160
views
Force Excel to re-parse a whole column of dates
I have a series of dates in a column, currently recognized as text. Thus sorting doesn't work correctly.
When highlighting the column and changing the format to Date, Excel still recognizes it as ...
- 1,900
0
votes
1
answer
117
views
How to make calculations with specific lines?
I have basic to medium knowledge in Excel, and I am having trouble with a somewhat complicated problem for me.
I have a table where I need to relate three columns: a "Type" column, and another two ...
- 3
1
vote
2
answers
160
views
Excel 2016 - How to bring up a conditional list
I'm trying to create a dependent list in Excel 2016, but don't really know where to start. I tried following a couple of guides to create a dependent list, but it wasn't doing what I wanted it to do.
...
- 13
0
votes
2
answers
793
views
Excel - If any of 6 cells are > 0 divide by that number of cells
What I'm trying to do is the following:
Once 'Cell B19:B20' and 'Row 21' have a value, 'Row 22' divides 'Cell B19:B20' between the amount of columns that have a cost value ('Row 21').
For example:
...
- 3
1
vote
1
answer
688
views
SUM multiple rows when criteria is met and Match a specific column
=IFNA(SUM(VLOOKUP($A4,Consol!$C$8:$BS$222,MATCH(TB!G$1,Consol!$C$2:$BS$2,0),FALSE)),0)
I have a project finance model (PFM) by month and I need the data to feed into a TB (also monthly). So what I ...
- 11
2
votes
1
answer
127
views
Shortening long sentence based on lookup-value
Need help: As stated in image below that I need the result in E3 to be "Washington" because based on lookup value in B6:B9 there is a matched-word partially (which is 'Washington') from D3's long ...
- 85
1
vote
2
answers
1k
views
Bypassing one of the SUMIFS criteria+criterion if the particular cell is blank
Need help:
From the table image below, the result I want in cell M5 using SUMIFS formula is that if cell K5 (validation filter) is blank or "" then the formula of D5:D6,K5 should just bypass.
So the ...
- 85
0
votes
1
answer
42
views
Return formula if the range is not ""
I wanted to have formula in G3 =
From range J3:L3 find the first value which value is NOT ""
so since from range J3:L3 the first non "" value is the one in K3 (the value is "GDNdynamic") so then the ...
- 85
0
votes
1
answer
911
views
Is it possible to create a chart based on a single cell that contains two separate numbers?
I'm currently combining two workbooks and then exporting a comparison of the data there. If I'm exporting a table this leaves me with something like so..
+----+---------+
| 1 | 2 |
+----+------...
- 63
0
votes
1
answer
16k
views
Make only one bar in bar chart stacked?
Is there any way I can create a bar chart based on data but make only one bar stacked? Like this (see the last bar):
I made this image by adding a shape to the last bar. Is it possible to create a ...
1
vote
1
answer
1k
views
Excel function to find and count all duplicates by row, excluding the first instance
I'm trying to create a report in excel that counts the number of duplicate rows, excluding the first instance of that row, in an imported sheet. I know that I could use "remove duplicates" in the Data ...
user1022144
0
votes
0
answers
545
views
MATCH Formula Returning 0
I have the following formula:
{=INDEX(config!H2:H20,MATCH(1,(F5=config!F2:F20)*(VLOOKUP(D5,config!C2:D20,2,FALSE)=config!G2:G20),0)) }
I then wanted to add another condition to make sure that ...
- 157
2
votes
1
answer
374
views
Excel: How to use =SUM() with two strings for range?
I need to pass in two strings indicating the two locations of the range. However, SUM wouldn't read the string, but it reads the value of what's supposed to be the indicator of a location.
What I ...
- 169
0
votes
1
answer
80
views
INDEX, MATCH with IF
I'm trying to use an INDEX with MATCH that contains an IF expression.
This previously worked in Excel 2010 but is no longer working in 2016.
Any thoughts as to what is throwing the N/A error?
=...
- 1
2
votes
3
answers
388
views
Convert time string/text to time (calculable)
I have a column of data which consists of strings such as follows:
Row 1: 46m 06s.
Row 2: 15d 5h 09m 33s
Row 3: 17h 24m 59s
Etc
I want to convert these strings to number or time format so I ...
- 21
0
votes
3
answers
9k
views
Extract certain words from strings of text in Excel cells
I have a column of cells filled with a lot of text. I only need one word from each cell. If you look closely you will see that the latter part of each cell says either High, Medium, or Low.
I want to ...
- 15