Tagged Questions
0
votes
0answers
13 views
Runtime Error 1004 Unable To Set the Locked Property of the Range Class
Have searched around but I could not find the answer. The only thing it pointed to was merged cells however I do not have any. If someone wants to look at the file, I can only email as its too big to ...
0
votes
1answer
8 views
Excel: Using MATCH as row argument of OFFSET
Cell F10 is a choice-box: 'Best', 'Base', 'Worst' case. I'm trying to change Revenue growth (F11) based on that selection.
How do I use match as the row argument of the OFFSET function in cell F11 to ...
0
votes
3answers
72 views
How do I separate three different phone numbers concatenated in one cell into three new rows?
I am doing work for a client and need to know how to separate these phone numbers.
I have
0121 396 0501 / 0121 123 434* / 0797 978 8265
in one cell and I want to end up with the same row ...
0
votes
1answer
22 views
Excel 2010 Formula Bar - autofit?
In Excel 2003, the formula bar would autofit the height of the active cell. In Excel 2010, this doesn't happen. Is there a way to revert the behavior to the Excel 2003 functionality without trying ...
-2
votes
2answers
122 views
Nested IF formula
I want a formula that will capture the following three scenarios below. In column S, I have Orange in cell S2, Mango in cell S3, Grape in cell S4, Pawpaw in cell S5, Onion in cell S6, Apple in cell ...
2
votes
3answers
85 views
Include an OR condition in a COUNTIF?
This question has been asked before and the answer always seems to be to add together two COUNTIFs. This is fine for most applications, but won't work for me.
I have a column that can contain 0, 1 or ...
0
votes
1answer
34 views
Focusing on particular information within a cell when doing a calculation
When performing a calculation using two cells in Excel, is there a way to focus on specific information within a cell, when entering the formula?
For example, I have two time cells (start time and ...
0
votes
1answer
58 views
How do I add values in column B if column A contains the same employee surname more than once?
State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once?
I have been using what I ...
0
votes
1answer
86 views
Excel Time Subtraction
I think there is a way around the time subtraction issue I have in Excel. The problem is that when the time in cell F (for example 11:29am) is less than the one in cell A (for example 11:30am), the ...
0
votes
3answers
73 views
How to update cell references when setting the Formula property in VBA?
Here is a snippet of my code:
For i = 1 To k
If Range("D2").Offset(i, 0).Interior.ColorIndex > 0 Then
Else
Range("D2").Offset(i, 0).Formula = Range("D2").Formula
The ...
2
votes
2answers
33 views
Excel macro checks if there is formula
I want to make a macro that checks if that cell get its value from a formula. I cann't find anything similar. Any tips??
1
vote
1answer
50 views
Hide “inconsistent formula” error in a selection in Excel
This may seem easy to more advanced users, but I can't seem to find this out there.
I have a complex Excel worksheet. It has various formulas/calculations throughout the sheet.
Unfortunately, ...
-1
votes
3answers
72 views
How to lock a formula with self reference?
I'm trying this:
C9 formula: =-40+C9
What I want to is being able to enter some value in C9 that will not overwrite the formula and get a result in that cell. For example:
If I enter the value 140 ...
1
vote
1answer
101 views
Excel formula creation
I have a spreadsheet with a lot of job related data. One column has names of people in it and another column has the dates on which those people completed jobs. Each person may do more than 1 job per ...
0
votes
1answer
135 views
Excel Formula for filtering data from one sheet to another
I have table that have the following columns (Agent Name, Client, Policy #, Premium) with the name of agents repeating several times since they have multiple clients...
I don't want to give all the ...
0
votes
3answers
102 views
change link cell formula across sheets
I have a workbook named "stu", it has 1 to 30 sheets. In cell H4 I have linked a formula:
=[sub.xlsx]Sheet1!$B$2
in sheet1: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$2
in ...
0
votes
0answers
66 views
How to get new data to automatically slot into correct column in Excel?
I am trying to move new data into an existing database with set columns. The existing database has a column for every possible type of data, but not all new entries contain all columns. So I can't ...
0
votes
1answer
88 views
Complex excel formula help needed?
I have a complex formula i need to create to look up various fields, with percentages of totals to be given if certain criteria are met, and need some guidance.
In column P i have a monetary figure.
...
0
votes
1answer
164 views
Excel formula sorting data by date but group by column value
Morning people
I've been roped into helping someone with an excel sorting issue and I've got myself a bit stuck to be honest. I need to sort a spreadsheet by date (oldest to the newest) but group ...
1
vote
1answer
322 views
How do I set up Simpson's rule in Excel?
I know there are example spreadsheets online, but I look at the formulas and don't quite understand what is going on.
I know the formula for Simpson's Rule but I don't know how to implement it in ...
1
vote
3answers
51 views
Complex Summing Values in a Cell
Let's say I have formula result that returns this +10+5+1+40, as text. I want to then sum the those numbers to get the total.
The result could be any number of values, so like +2+5 or +10+5+7+1+8.
...
1
vote
1answer
46 views
Excel formula for extracting prefixes
I have a table of 2 columns - file name & PC ID.
What I need is list of all 3-letter prefixes - for each PC ID separate. For example:
For:
BUSstop1 PC1
BUSstop3 PC2
TRMstop8 PC1
BIKnet1 PC1
...
3
votes
2answers
276 views
Is there a way to express a complex Excel formula in terms of final (non-formula, value-only) cells?
I have a very complicated Excel spreadsheet (formulas accessing cells with other formulas across multiple worksheets) that in the end computes a single cell (output) with input from several other ...
0
votes
3answers
65 views
Excel how to SUM a column filled with Yes's and No's
I'm not summing numbers, but rather the number of occurrences of a keyword.
How is that done?
Sample Data:
Item Completed
---------------
a No
b No
c Yes
d Yes
e No
Total ...
1
vote
2answers
195 views
How does Excel's VLOOKUP handle multi-word lookup values?
I have a worksheet that's returning unexpected VLOOKUP results, and I'm trying to narrow down the issue. I've run into a question I can't answer in my search for the solution.
Consider:
I have a ...
1
vote
1answer
80 views
Excel =+ formula impact
I can't for the life of me remember but what does putting + after = in a formula actually do?
For example the difference between:
=+VLookup(A1,B1:B10,1,FALSE)
Compared with:
...
4
votes
2answers
272 views
Restrict and validate date format in Excel
I'm trying to put together a 'Data Validation' formula which restricts the user to entering a date in the following format: dd-mm-yy.
From the research I've done, I've come across what I thought ...
0
votes
1answer
419 views
How do I calculate percentage change in an Excel formula?
I currently have 2 cells in Excel and I'm trying to find a formula to display the percentage change between them.
For example:
If B2=100 and B3=102, the result should be: +2%
If B2=103 and B3=100, ...
1
vote
1answer
166 views
How to replace cells containing individual letters with another value?
In Excel, how do I write a formula that looks at a range of cells (say, A3 to A16), checks whether any of them are each individually letters, and if they are, replaces those values with "0"?
Replace ...
0
votes
1answer
262 views
How to convert currencies in Excel based on historical conversion rates?
I want to make a small program in Excel that allows the user to convert certain currencies (USD, Euro, AED, CHF and GBP) to Kuwaiti dinar. The user can set the amount, the currency and the month, and ...
2
votes
2answers
174 views
Calculating a duration in excel from date and time
I have 2 columns in excel, each column includes a date and a time in the following format: 'dd/mm/yyyy hh:mm'. One is a start date/time, the other is an end date/time. The end date/time could be ...
0
votes
1answer
120 views
Mapping of child elements
I can't find one good way to solve this problem. I will try to sum the issue as best as I can:
I use Hyperion Essbase Excel ad-in to navigate inside OLAP cubes. What I am trying to accomplish here is ...
1
vote
1answer
252 views
Excel Formula For Grading
I am looking to create a formula in Excel to sum a number of grades on student assignments. Here is the relevant info:
There were 11 assignments total, with grades entered into 11 columns (Columns ...
2
votes
2answers
106 views
Auto Updating an Excel Filter
Firstly I'm using excel 2010.
I have had a look at other "auto-update" and from what I saw they wanted you to type some information in to make it "refresh" the auto-update.
Essentially what I have ...
2
votes
2answers
74 views
I want to count the number of cells included in a formula
I've been looking for this answer and I'm sure it's embarrassingly simple but I can't find it anywhere.
I have a very simple but large formula =SUM(H2:H5,H7,H22:H23,H28:H30,H33,H41,H43:H46.....etc) ...
1
vote
0answers
25 views
Excel: Check if column A has any values from Column B, and delete that value from Column A [duplicate]
Possible Duplicate:
Excel: Check if column A has any values from Column B, and delete that value from Column A
So I have two columns in Excel with these values suppose:
Column A | ...
1
vote
1answer
52 views
import formula from corrupt / large excel workbook
I am trying to import formula from a closed Excel workbook that has gotten overly large and hence wont open without crashing Excel. I have a copy of the data but want to get at the formula in one of ...
1
vote
2answers
197 views
How to sum if there are values in range of cells and otherwise leave blank
How do I sum a series of cells only if there is a number in any of the cells (otherwise leave it blank)? The current formula I am using keeps returning a zero when there is no number in any of the ...
0
votes
2answers
140 views
Positive result, negative result and current balance. How do you make starting balance show current result?
I have 3 columns:
Column A shows positive result and if the result is negative then it is in a column B.
Column B shows negative result and if the result is positive then it is in a column A.
...
1
vote
3answers
1k views
Excel: Change all cells with one character to something else
Is there a formula I can use that will change all cells with one character to something else?
For example, I have cells with single letters and no matter what the letter is I want that cell to ...
1
vote
1answer
44 views
excel / open office - append an incrementing value to all non-unique fields
I have a large table of about 7500 store names. I need to search through those names and, if they are not unique, append an incrementing value, for example:
store_1
store_2
etc.
Anyone know how to ...
1
vote
1answer
65 views
MS Excel - splitting a formula into individual cells?
I'm not sure if this is possible, or if I'll have to do it manually, but I have lots of cells in the following format:
=87.12+56.52-16.50+98.21-9.51
If possible, I'd like to break it up into ...
0
votes
1answer
47 views
Excel Formula For Re-arranging Numbers
I have got a bit stuck with trying to make a formula in Excel, and last time I asked a question here I got great help, I have come back again. At the moment I have column A with sets of product id's. ...
1
vote
1answer
586 views
How to count how many rows contain particular values in a particular set of cells in libreoffice and excel
This Calc file : http://dropcanvas.com/521xc/107 have a sheet called "Etat" and a sheet called "Total".
In Etat.F20 I have a formula that counts how many rows in Total have the value "Bug" in the D ...
1
vote
1answer
60 views
Microsoft Excel Price Formula
I am doing a formula that takes a cost x a mark up % to get a sell price.
$11.47 x 1.35 = $15.48. I then need to go and copy and paste values to remove the mark up for my customers. When I do ...
2
votes
1answer
2k views
How do I extract excel data from multiple worksheets and put into one sheet?
In a workbook I have 7 sheets(Totals and then Mon to Sat),I want to extract rows which have the word "CHEQ" in its cell (this is a dropdown list with two options-CHEQ/PAID)from all sheets. On my front ...
-1
votes
1answer
204 views
Highlighting duplicate column-pair and counting the rows Excel
Given the data below, the column-pair with the same values for at least 4 consecutive rows should be highlighted.
Here's an image for better visualization:
2 2
3 4
3 4
3 4
3 4
2 3
1 2
...
0
votes
3answers
184 views
Highlighting duplicate column-pairs in Excel
Example, this is the data in Excel:
1 2
2 2
3 4
3 4
3 3
2 2
2 2
1 2
The rows which have consecutive duplicate column-pair values (in bold font) should be highlighted:
1 2
2 2
3 4
3 4
3 3
2 2
2 2
1 ...
3
votes
1answer
231 views
What does “calcs” formula do in Microsoft Excel?
I encountered an XLS file with this formula in some of its cells. I searched the web for information on the formula, but I couldn't find anything. Could you help?
The formula in question is:
...
2
votes
1answer
138 views
Excel Formula: What is the equivalent of =MATCH(), but for multiple columns?
Problem
This formula will return the row number where the value "Bob" appears. Say the value happens to be in $A$10, the formula would return: 10
=MATCH("Bob",A:A,0)
I would like a formula to do a ...
