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

**0**

votes

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**2**answers

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

**8**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**3**answers

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

**1**answer

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

**0**answers

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

**2**answers

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

**3**answers

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

**4**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**4**answers

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

**1**answer

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

**2**answers

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

**3**answers

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

**0**answers

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

**3**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**4**answers

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

**2**answers

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

**0**answers

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

**1**answer

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

**2**answers

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

**4**answers

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

**6**answers

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

**1**answer

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

**0**answers

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

**2**answers

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

**0**answers

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

**1**answer

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

**0**answers

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

**1**answer

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.