Questions tagged [microsoft-excel]
A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function. Also add the the specific version tag (e.g. [microsoft-excel-2019] or [microsoft-excel-365]) as features and functions may vary between them.
25,937
questions
0
votes
1
answer
239
views
How to I use a selectable field to change a sheet reference in a formula?
I have a formula that looks something like this
=B2 - IF((B2 - Single!$D$1) < Single!$A$3, (B2 - Single!$A$2 - Single!$D$1) * Single!$B$2 + Single!$C$2, ...
I am using something of a lookup table ...
- 188
0
votes
1
answer
390
views
Add value based on Category
Fairly new to Excel but I want to make a cost sheet.
The layout is like this:
A1: Category B1: Price
A2: Food B2: € 5,00
A3: Utilities: B3: € 50,00
A4: Food B4: € 10,00
Now I want to combine ...
- 187
0
votes
3
answers
2k
views
Excel - Comparing the value of 2 fields (version number)
I am trying to compare and check version numbers and I am not able to use any of the comparison operators because possible examples are
2.0
3.1a
2.1c
3.11d
Any suggestions?
- 1
0
votes
1
answer
4k
views
How to create two-dimensional column chart in Microsoft Excel for Office 365
I would like to create a two-dimensional column chart in Excel. On the x-axis I would like to plot % of Net Sales, and on the y-axis I would like to plot Gross Margin %. Here's an example:
You can ...
- 103
0
votes
3
answers
5k
views
Use LEFT in sum_range parameter of SUMIF function (Excel)
I have a lot of columns on top of each other, like so:
I would like to find all of the cells in this range that begin with 1a, and then sum up whatever follows. So in this example, we find 1a10 and ...
- 212
0
votes
2
answers
3k
views
Excel: Multiply a number by a sequence of numbers
In Excel I want to create a sequence of numbers that goes like
435
870
1305
...
I tried typing the first few instances and then dragging the select thing that's supposed to apply a formula to a ...
- 165
0
votes
2
answers
60
views
Change the specific text become a number in excel
Can I actually Make a specific text become a number in Excel? For example, in Column B:B there are a lot of A and B, and I want to change A=1 and B=2. If this is not clear, take a look the picture :
...
- 3
0
votes
1
answer
65
views
Combining Excel data - 1 cell for conditioning and 1 cell for the actual data
I'm using Excel 2010
First, I have 2 cells. The first cell indicates the color in text - Red Orange Green Grey. The second cell also contains plain text. The data is exported from a SharePoint list.
...
- 65
0
votes
1
answer
58
views
Greater Than / Equal To: using specific words
I am trying to write a formula with an outcome of Pass or Fail based upon two criteria:
Column A relates to a document's required approval level, ranging between Blue, Green, Yellow, Red, Purple, and ...
- 51
0
votes
1
answer
3k
views
Excel VBA - Recover user form deleted by mistake
By mistake I have deleted my userform in Excel VBA. How to restore the user form? Could anyone help me in resolving my issue? It's almost a week's work.
- 1
0
votes
1
answer
9k
views
Excel populate cell based on match of two other cells
I'm sure this has been answered already but, frankly, I don't understand what I've seen.
I have a workbook with two sheets in.
Sheet A looks like
Promocode Campaign
AAABBBCCC
CCCFFFRRR ...
- 1,538
0
votes
3
answers
1k
views
Creating a macro to segmentally average the data in a given column [closed]
I would like to create a macro that averages the values of every 12 items in one column in an Excel worksheet, for example, A1-A12 then A13-A24 etc., and places the results in cells C12, C24 and so on....
- 21
0
votes
1
answer
2k
views
RANDBETWEEN but smaller than another cell value
I would like to generate a number that is random but smaller than the cell above (I'm doing 4th grade subtraction worksheet).
What is the formula for this?
I tried =randbetween(C3, 99) but it didn'...
- 1
0
votes
1
answer
20k
views
Turn off Excel Compatibility Mode
I experimented with compatibility mode in Excel 365, now I can't turn it off, every new workbook opens in Compatibility Mode.
Crawled the web for an answer, but only found how to save a particular ...
- 113
0
votes
1
answer
57
views
About Linear Equation in Excel
I already sketched the slope of lab result to find a value. But I need to do it in Excel which I will include in the lab report. But the slope value is different.
The one I got from sketching in the ...
0
votes
2
answers
12k
views
Keyboard shortcut in Excel for Mac 2016 to paste and match destination formatting
Is there a keyboard shortcut shortcut to paste and match destination formatting in Excel for Mac 2016? If there isn't, can I create one?
- 359
0
votes
3
answers
12k
views
Ranking by multiple criteria columns
I 'm trying to rank my students by 2 criteria. First from RESULT and then POINT.
In the example, the resulting order should be, NUNU, NENE, NONO, NANA and NINI.
It cannot be done by adding up RESULT ...
- 57
0
votes
1
answer
92
views
Excel formula for ratios to reach a total [closed]
I have a spreadsheet which allows the user to enter a budget e.g. "£100,000".
Whatever budget figure is entered by user, I want my spreadsheet to calculate a number of days of 3 different labour ...
0
votes
2
answers
846
views
Change the formatting of a cell depending on the value of that cell and another cell
EDIT:
Okay. Thanks to BruceWayne, I have made progress.
However, the formula I ended up with:
=AND($C3="Y",OR($G3<=0.5,$H3<=0.5,$I3<=0.5,$J3<=0.5,$K3<=0.5,$L3<=0.5,$M3<=0.5,$...
- 23
0
votes
2
answers
15k
views
Excel - Filter table from one sheet to another using formula
How can I filter my table from one sheet, into another sheet using formula? without a formula, I can just copy a reference of the cells like so:
=INDEX(MainSheet!A:A,ROW())
And then mark the range ...
- 139
0
votes
1
answer
449
views
Extract specific string from a string with specific ending character
I have a text file and using Notepad++. I have input lines as below (with new line and no set pattern) and want to extract the number before T and output the numbers as pipe delimited.
Input
(...
0
votes
1
answer
427
views
Concatenate Merged Header Rows
I have a spreadsheet with two header rows. I want to generate the "full column name" as shown on row 3.
A | B | C | D | E | F |
------------------------------...
- 27.9k
0
votes
1
answer
46
views
Comparing Digits For At Least 4 Differences
In A1 I have 19732003, in B1 I have 19811975. I want to compare A1 and B1 to see if at least 4 of the digits have changed. This needs to be all in one function, not 8 separate functions. I have tried ...
0
votes
1
answer
5k
views
Keyboard shortcut Ctrl+S does not work in Microsoft Excel 2016
I try to save an Excel file with Ctrl+S, but that does not seem to work.
In particular, when I close the file, Excel asks: "Want to save your changes to ... ?" If I press "Don't Save", the changes ...
- 113
0
votes
1
answer
12k
views
Excel - using a dynamic address in a formula
How to insert a dynamic cell address in a formula. For example I have data in intermittent cells in a row. I need to use "countblank" formula to count the number of blank cells after the ...
- 1
0
votes
3
answers
1k
views
Formula to increase every 12th row by a percentage
I am trying to create a formula to estimate future costs for owning rental property.
One example would be taxes, on average the property taxes are increasing 4% every year where I live.
So I want ...
0
votes
1
answer
1k
views
How to get one part of a directory path
I have a path which I get through =INFO("Directory") (for example "C:\Users\username\Documents\") and I want to extract username that is between 2nd and 3rd slash, it can be of any word length so I ...
- 1
0
votes
1
answer
2k
views
Why do carriage returns in Excel not visually show up in Notepad when you save the Excel as a Text Tab Delimited?
If you put a carriage return in a cell of Excel (by pressing Alt + Enter), and save your file as a Text (Tab delimited).txt file, the carriage return does not appear when you open the file in Notepad.
...
- 338
0
votes
2
answers
67
views
Excel: get number of cell programatically
I’m doing this, but it doesn’t work:
=&ROW(D(RIGHT(A21, LEN(A21)-SEARCH(" ", A21, 1))))
I want to calculate the find number from a different cell, and append it to Drow to get for example: D8
...
- 103
0
votes
2
answers
2k
views
DATEDIF() not returning expected value, how can I calculate age in months?
I'm trying to calculate the age of my credit card accounts in Excel. I've read online to use the DATEDIF() function, however, I'm not getting the expected result and I'm not sure where I screwed up.
...
- 370
0
votes
3
answers
663
views
How to copy only the rows of text from a column in Excel?
I routinely need to extract the text from files that look like this:
Ideally, in two ways:
Just the individual rows of text
Ex:
done we actually have a publication out
of this data and we are ...
0
votes
1
answer
13k
views
Excel formula changes itself - bug or intended behavior?
I have a weird error which caught me out recently. I want to understand if it is expected Excel behavior and if so, is there a way to prevent it or turn it off?
You can recreate the issue by creating ...
- 23
0
votes
1
answer
709
views
How to divide a number into multiple columns or rows in excel?
I need help to divide a number into multiple columns or rows.
For example A1 has 32000. I need to divide it by 10000. So the data will divide into multiple columns or rows, but the residual value is ...
0
votes
2
answers
5k
views
How to delete rows where two columns are both blank
I have an Excel worksheet with Columns A-G.
And hundreds of rows.
There are sometimes numbers in column F and sometimes in column G.
How can I only delete the rows when columns F and G are both ...
- 1
0
votes
1
answer
23k
views
How do you generate a list on excel based on a given criteria?
I am trying to build something like a dashboard with couple of drop-downs, that would search a table and provide me a list, based on what I have selected. Please see below table:
So what I want is, ...
- 1
0
votes
2
answers
828
views
Excel decimal precision secretly exceeding 15 digits and breaking VLOOKUP
I am doing some midpoint math using this formula
A1-MOD(A1+(Constants!$A$1/2),Constants!$A$1)
(Where values in column A are decimals and the value in Constants!$A$1 is an exact integer)
Then ...
- 484
0
votes
2
answers
180
views
Excel 2013, AND function
I'm supposed to modify the formulas in cells B8 to B11 to display a blank if no boat ID value has been entered (cell B7). I also have to modify the formula in cell B13 to display zero if no boat ID ...
- 1
0
votes
1
answer
2k
views
Can you create a chart without having a table? [closed]
I am trying to create a chart that updates automatically when somebody clicks a drop down tab.
When you create a table it makes the spreadsheet look ugly and clunky.
I want to be able to have ...
0
votes
1
answer
3k
views
How To Transpose Every 3 values From One row To Multiple columns?
This can be potentially embarrassing but hey, its never too late to learn.
I am using Microsoft 2010.
I have the above mentioned row values in triplicates (scrambled control %) and I'll need to ...
- 111
0
votes
3
answers
12k
views
Can an Excel file that was edited and saved in a WinZip file be recovered?
I have a Zip file that contains four Excel files. I have edited those Excel files without extracting them, and closed them properly after being saved.
But when I reopened those files without ...
0
votes
2
answers
2k
views
If cell A1 is greater/lesser than XX, then calculate
I'm attempting to write a formula to calculate the number of positions gained/lost on a cell.
For example:
If A1 has a positive value (example 52.13), then subtract C1 from B1.
If A1 has a negative ...
- 1
0
votes
2
answers
80
views
Look at data between the ranges of. (Excel)
My aim is to add up the amount of data in cells between a certain date range. I have 2 columns which are dates and ajacent to those are amounts of beans I have in the bean bank. I entered this formula,...
- 133
0
votes
1
answer
42
views
Excel Weekday Generation
I have a list of dates in a row. I just want the row above them to tell me which day of the week the corresponding date (below) fell. For example, today (30 Jan. 2018) would output "Tuesday", because ...
- 137
0
votes
2
answers
3k
views
Excel paste special values does not show value as displayed in the source cell
I'm trying to copy and paste row values from a sheet to another using the paste special values option. My source cell is a date custom formatted to YYYYMMDD
Source Cell
Orig ...
- 109
0
votes
2
answers
901
views
Excel - How to subtract cells and add columns
So lets say I have 2 columns, A e B, like for example:
A B C
-----------------------------
1 | | A | B |
-----------------------------
2 | ...
- 107
0
votes
1
answer
68
views
Excel use one column as two datasets
I am working with some data in Excel and I would like to create a combined chart, but I am facing some issues as they are slightly different than usual. I have the
x-Axis || ID || value
0.1 || 1 ...
- 103
0
votes
1
answer
1k
views
How to use RANDBETWEEN() x times in a single formula?
I would like to make a formula that can essentially calculate an average output of a dice roll, think Dungeons and Dragons.
I want to use a single formula to calculate any possible roll, using each ...
- 103
0
votes
1
answer
526
views
How to find the first empty cell on excel
I am trying to get the value in red below which corresponds to the first not empty cell in the line and to have the date corresponding for each :
I have tried this formula but it did not work for ...
- 101
0
votes
3
answers
5k
views
Reset a Hyperlink In Excel
So when you click a hyperlink in Excel, the font goes from Blue to purple,
How does Excel know when a link has been clicked ?
And secondly, how can i reset it so that it returns back to blue
(...
- 7,435
0
votes
1
answer
535
views
Why am I getting a negative balance after subtracting values? [duplicate]
I have a formula to subtract several cells from 1 cell and I am getting a result of -2.27374E-13. The formula is D235-E235-F235-G235-H235-I235-J235, each column has a dollar value and no matter how I ...
