Questions about using in-cell functions/formulas in spreadsheet software.

learn more… | top users | synonyms (5)

0
votes
1answer
8 views

How to count the number of cells in a range containig a specific (sub)string in Excel/Calc?

I have the following situation: a range of cells containing values, assume it's A1:B10, and a string I want to look for, for example assume it is contained in C1. Now what I want to do is count ...
0
votes
1answer
11 views

Extended Subtraction Between Columns on same row in area

I want to calculate the transaction value so by that i mean i've got different accounts in column "A" and for each transaction i got a column like "B". The transactions show the new value, after the ...
0
votes
0answers
21 views

Excel: Can I use text references without volatility?

A1= "Namedrange,1,23,3,bla" On the row I split it down to having each of the , delimited entries in its own cell. (B1=Name, C1=1, etc) The problem is I want to feed the Name to an index function as ...
-1
votes
1answer
18 views

Goggle Spreedsheets [on hold]

Can I get excel to recognize dates and provide me a sum for the data corresponding to that date? For instance. Column A, Rows 1-3 are dated 1-1-2016, Rows 4-7 are dated 2-1-2016, Rows 8-9 are dated 3-...
0
votes
2answers
31 views

How can I sum with partial parts of cells?

Let's say I have a spreadsheet look like this. A |B ---------------- 1 |1.0 2016-05-07 2 |0.5 2016-04-12 3 |x.y yyyy-MM-dd How can I sum those first values(1.0, 0.5)?
32
votes
8answers
142k views

How do I get the day name into a cell in Excel?

Given a date, how do I get the day of the week (e.g. "Monday") into a cell in Excel?
0
votes
1answer
19 views

CountIF/CountA using Array Functions

I have a list of projects in various countries (each row is a distinct project; countries are listed in Column A). Each project can have multiple tags (e.g., demand, pedagogy, ICT, etc.). For a much ...
0
votes
1answer
28 views

How to change a negative value to a positive number not exceeding 49

Hi I have the following question that I am struggling with solving I have si numbers from a lotto draw to which firstly I would like to subtract each number by 25 and record into cells , therafter if ...
0
votes
1answer
26 views

Trouble combining IF statements in formula

I am trying to create one formula that will do this: If <=10, then take 50% of the amount, if >10, then the lower of 5 or 25% of the amount. I converted the second statement to Excel by saying if ...
1
vote
1answer
13 views

Apply LibreOffice sum to multiple renamed sheets

I have a spreadsheet with many sheets, each tracking a particular topic. Within each sheet are columns of raw data, and a cell (always in F1) that contains a calculated stat for the raw data. I will ...
0
votes
1answer
28 views

Ignore blank cells for an average in Excel

I'm a little stuck with a sheet I'm creating at work. Basically the sheet tracks performance across 3 fields for our suppliers: Delivery, Product Quality and Supporting Documentation. I would like ...
0
votes
0answers
26 views

Excel - Can I use a date range to format a cell

I have a calendar and a table of events on a worksheet. The table of events include a Start and End date as well as Status columns. I would like to highlight the dates on the calendar where the ...
0
votes
1answer
15 views

Workbook sheet date to autofill weekly tracker

I'm building an excel workbook in which the first sheet, a user will type a date in A2 and pull down the property he visited in B2. The next sheet is a weekly tracker with the first row labeled weeks ...
0
votes
3answers
3k views

Excel saves a file in .csv but it keeps the columns and the rows as they were in the original sheet

I have a problem converting an Excel sheet in a *.csv file. I've searched all day for solutions, but nothing seems to work. After I select the sheet and save it as "CSV(Comma delimited)", pressing OK ...
0
votes
1answer
17 views

Split string and get the first part with IF

I have the following data in excel and would like to get the first part (before the "/") if there is a "/". If not, just get the value in the cell. A1 is the raw data and A2 the desired output. ...
2
votes
0answers
75 views

Excel- How do I find common text values from multiple (>2) rows?

This is essentially a variant of the question asked here: Excel- How do I find common text values from multiple (>2) columns? In this case, however, instead of multiple columns of data I need to ...
-3
votes
2answers
45 views

Pull out matches with formula

I am trying to compare two columns and pull out the data is the same? I have tried multiple formulas but unable to produce multiple data? I am using 2016 Excel for Mac. Sample info: I have tried ...
0
votes
3answers
35 views

How to change the font of a specific formula which is part of an Excel Cell?

="InterContinental Vienna @ "&M9&" "&M42&" p/room p/night" based on the above example, M9 = EUR and M42 = 150 Result I am looking for is: InterContinental Vienna @EUR 150p/room p/...
1
vote
4answers
40k views

Compare multiple columns for any two or more cells within the same row having the same content

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of ...
1
vote
2answers
1k views

How to interpolate intermediate values for arbitrary data in Excel 2013

I have data tables like this example, nine entries at A1:B9 in this case: A B -- --- 1 2.9 2 5.06 3 7 4 8.84 5 10.87 6 13.24 7 16.22 8 20.25 9 36.7 The above ...
0
votes
1answer
3k views

Use of array formulas within VBA

Excel array functions in worksheets are useful. E.g. for calculating the average of the sin values of an array A1:A5 I enter the following which works nicely: {=AVERAGE(SIN(A1:A5))} Now I would ...
0
votes
1answer
23 views

What is the correct If Then Else formula to use in this situation?

Column A Column B. Visa. MasterCard 5429********6324. 12.75 4728********7632. 75.16 Column A and B represent the format in which I receive downloaded credit card payment ...
0
votes
1answer
18 views

How to define an Excel function to evaluate specified cells in a spreadsheet

I have the following data on a spreadsheet (xlsx) (Excel 2011 14.6.4 for Mac): No Yes Yes No Yes Yes No Yes Yes I am trying to count the number of "Yes" values for just A1 and C1 skipping B1 (I ...
0
votes
2answers
77k views

How do you write an Excel formula that will paste a specific value in a different cell?

So basically I want to write a formula that will check a condition, and if that condition is met, then I want to paste a specific line of text in a different cell. I should note that I do not want the ...
0
votes
1answer
18 views

Microsoft Excel 2013 - Continual subtraction?

So to make my question as easy to understand as possible, I am in the process of writing what will start out as a table-top/text based style RPG game. I already have formulas for all my battle ...
0
votes
4answers
40 views

Excel Letter Plus Number Format

I'm having a problem figuring out a way to force a certain format on a specific column. What I"m dealing with is a spreadsheet I use to track inventory. It's used by several people and as a result, ...
0
votes
1answer
19 views

Trying to create a dynamic hyperlink

=HYPERLINK(c:\Users\tminnick\Desktop("#FileName",A159)_EmploymentAgreement.pdf) This is the link that returns an error message. the ("#FileName",A159) formula is intended to pull the text in A159, ...
0
votes
2answers
3k views

Defining Variables in Excel

I have two lists of data, x and y, one in a row one in a column. I would like to populate a chart of rows and columns with a complex formula that is the same for each chart point, the only difference ...
2
votes
3answers
2k views

Turn Excel spreadsheet into a formula

I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has about 10 different inputs (values a human enters in ...
1
vote
0answers
29 views

Create dynamic formula so that one Excel table looks-up the same column name in another table

I have two Excel tables, table A and table B. Table A contains the columns Customer ID, Postcode, Customer Name and several others that have the same column names as columns in table B. I want to ...
1
vote
3answers
4k 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 ...
0
votes
2answers
37 views

In Excel, I am trying to calculate a percentage of two sums

I have a rather large dataset, 44 columns and about 8k to 9k rows each month. The table has every trip that our customers have either completed or not shown up for. I have started with a simple pivot ...
0
votes
1answer
8 views

Excel query; drawing on rightmost column in sum

In Excel 2016 I have one column full of out-of-date time data (mm:ss) (information in each and every row) and in the second column I have more recent time data (mm:ss) but only in every other row Is ...
0
votes
1answer
24 views

In Excel, how can you copy/paste multiple cells into the editing of another cell?

If I open two separate Excel files in version 2007, I can copy/paste two cells from one to another that I'm editing. I can't seem to do this in the newer versions. For example, I want to copy two ...
1
vote
1answer
4k views

VBA: How to replace entire Excel cell value basing on its content?

I have an Excel Spreadsheet with some names in a cells. There can be as one name per cell as multiple names in one cell. For example: Cell A5 contains "Joe", cell BD54 contains "Joe;Harry;Molly", ...
0
votes
0answers
11 views

Excel Windows 2010 - have database and want one column to sort automatically as data changes

I have an excel spreadsheet with formulas a) one column sorts the difference from the current price (col K or L) and the old price ((g or I) b) there are 20 rows this is the column formula (M) ...
2
votes
4answers
53 views

Remove everything to right and left of semicolon

I have these two cells: 35hz 24.5/jy;8243-9884;100093 and aslhz/45.77;94-224444;103944234 I'm trying to isolate the number in between the semi-colons. I've used the function: =RIGHT(A1, FIND(";",...
0
votes
2answers
3k views

Lookup Multiple Results for Multiple Criteria

I've got a list of parent SKUs for items I need to create in my inventory system. This list has been finely paired down to the 165 products we would like to carry. However, each one of these 165 SKUs ...
0
votes
0answers
20 views

having to keep track of previous day on a qry and data mart update that updates daily in excel

I am currently the "numbers guy" on my team. I have been able to add multiple query data onto one sheet keeping track of multiple parts of my teams stats. I was trying to do something that keeps ...
0
votes
1answer
19 views

using named ranges in a sumif formula

I have named ranges. They are columns on another tab within the workbook. I'm using a sumif formula to find value based on multiple criteria. The formula is =sumifs(H4,array1,b4,array2,c4). H4 is a ...
1
vote
2answers
43 views

Add values in a column until a certain point in excel and repeat

I have several thousand values similar to those shown in the picture provided. I am attempting to sum the time values in column K, but only if they have a value of "flag" in column M, and only ...
13
votes
4answers
54k views

Is there an Excel formula to identify special characters in a cell?

We have about 3500 documents whose filenames need to be manually scrubbed to remove special characters like brackets, colons, semicolons, commas, etc. I have a text file that I've dumped into excel, ...
0
votes
6answers
3k views

Excel COUNTIF Slow

I'm having an odd issue in excel that I haven't run into before. I have an excel sheet that I'm attempting to pull some specific records from, requiring that I know of duplicates. I am using a COUNTIF ...
1
vote
1answer
26 views

using <li> on each newline

I have following pattern in every single cell in the coloumn in Libre Calc - Spec One: This is one Spec Two: This is spec two Spec Three: This is spec three Is there any way to convert this to - &...
0
votes
0answers
16 views

Comber cell contains to rang (not match not vlookup not index)

Blue is range "Alist", cell yellow which contains names which one of them is to be compared ie, if the yellow cell contains a name from blue range = OK. How to return the value of the green cell to ...
0
votes
2answers
8k views

Count from a certain cell until a blank cell

I'm trying to create a dynamic formula which counts all the rows from cell D3 downwards until a blank cell is reached. I want to be able to add new data everyday and have the range automatically ...
-5
votes
0answers
38 views

Match function in Excel doesn't seem to perform as expected [on hold]

I have a problem with the Match function. For some reason, when I am looking for an exact match to (example: a1 + b1) using an array of numbers (c1:c10), it will not find the match. Even though I'm ...
0
votes
1answer
3k views

Updating another Excel spreadsheet from current spreadsheet

I used the VBA code in "How do you write an Excel formula that will paste a specific value in a different cell?" posted here in May 2013 and it works well to populate/fill another cell in the same ...
-1
votes
0answers
12 views

Unexpected result from Google Sheets MATCH function

When I use Google sheets' MATCH function to look up the row number for a value in a column, the wrong row number is returned. Example: MATCH(Q10;A:A) where Q10 has a value 'thuja plicata' This seems ...
1
vote
1answer
27 views

Excel mod function rounding error

I am testing multiples of 0.4 in Excel. =MOD(1.2;0.4) returns 0.4, when it should returns zero.