The formula tag has no wiki summary.
0
votes
0answers
12 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
2answers
67 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 ...
0
votes
1answer
23 views
How to mix Latin and Greek script in Libreoffice formulae(Math)?
I need to write a formula which uses both Latin and Greek scripts.
So far I can only make it so that either all characters are Latin, or they're all Greek.
1
vote
1answer
20 views
How to calculate values between three cells using conditional calculation
I am new to this forum and not very new to Excel (not in depth)
I have to perform a calculation for three cells by conditional calculation. so here is the scenario
Column Name: Total Area (Cell ...
-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
74 views
If cell B1=1 then DELETE cell A1 (excel 2003 formula) - How to do it?
I work with Excel 2003.
If cell B1=1 then DELETE cell A1,
and if cell B=0 then UNCHANGED.
How can I do this?
Example:
http://oi33.tinypic.com/mwsm03.jpg
Many thanks :)
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
1answer
47 views
On Excel 2010, the =COUNTIF function displaying the value improperly
Well, here is the code I am using below:
=IF(COUNTIF(G5:W13,"10"),"","$10")
This code is working perfectly fine, however the number is not actually been treated as a number.
I have done a number ...
0
votes
3answers
71 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 ...
0
votes
1answer
47 views
Change formula font size in LibreOffice
I am trying to change the font size of the formula tool in LibreOffice. I have been told:
To permanently change the default size (12 pt) used in LibreOffice
Math, you must first set the size ...
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
71 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
132 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
2answers
197 views
Formula help for matching data from multiple columns in multiple worksheets
What formula do I need in my worksheet DFW column C that will do the following?:
On worksheet DFW take data from cells A2 together with B2 and see if there is an exact match in worksheet Feb columns ...
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
162 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
321 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 ...
0
votes
1answer
33 views
Spreadsheet fill down for a formula
I want to add a formula to multiple cells in a column without typing them all out. now don't just tell me about the fill option as that does not do what I want (as far as I can tell).
When I used ...
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
...
2
votes
1answer
40 views
How do I copy the results of a calculation without copying the formula behind it? Open Office Calc
How do I copy and paste only the results of a calculation without copying the formula behind it? I'm learning to use Open Office Calc. I remember other spreadsheets that had a values function, or ...
3
votes
2answers
273 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
2answers
157 views
Display multiple cells if data on another workbook contains “X”value
I export a complex report daily and I transfer that information manually into another excel sheet. My question is: In workbook 1, can I get a group of cells in a row to display the text in workbook 2 ...
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:
...
0
votes
0answers
40 views
LibreOffice Calc: how do I dynamically modify a formula?
I'm running LibreOffice Calc 3.6.2.2 (Linux Mint 14 Cinnamon).
I have a summary sheet and 12 other sheets (1 sheet for each month - Jan..Dec). In the summary sheet, there is one column for each ...
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 ...
3
votes
4answers
845 views
How can I show values in the formula bar instead of cell references?
Here is what I have in my worksheet:
A1=2 B1=3 C1=A1+B1
I want the actual cell to show 5 but when I copy C1 I want the formula in the cell to show =2+3, not =A1+B1 or 5 is there a way to do ...
0
votes
1answer
417 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
172 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 ...
1
vote
2answers
205 views
Comparing Two Columns in Excel, Returning a Third
I've hit a bit of a brick wall in my Excel-ing. Below is the example and explanation.
Sheet 1:
Column A | Column B | Column C | Column D
_ Name _ | _ Date ._ | _ Cost .._ | _ Code _
...
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 | ...
0
votes
2answers
239 views
Math formula editor with export to SVG, EPS or AI
I'm looking for a free formula editor, which can export formulas to Vector-based formats. (svg, eps, ai, pdf, cdr, etc..)
Do you know something?
1
vote
3answers
102 views
Is there a tool for windows that creates formula-pictures out of a text-formula
I am looking for a tool that would batch process text-files containing formulas (possibly latex style) and convert them into a gif/png/jpg. Since I want to do a batch conversion, the tool ideally runs ...
