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

learn more… | top users | synonyms (5)

0
votes
1answer
21 views

Excel formula to read 2 of 3 cells repeating or not

I am having trouble getting a formula that will work the way I want: IF 2 or 3 of the last 3 cells (A2:A4) were b, AND cell A5 is b, then C5=+, otherwise -. IF 2 or 3 of the last 3 cells (A2:A4) ...
0
votes
1answer
32 views

VLOOKUP across multiple worksheets in different workbook (Excel 2010)

Apologies if I placed this in the wrong place. I am trying to create a VLOOKUP across multiple sheets in from another workbook, all the sheets have the same data and same layout, the VLOOKUP is in a ...
0
votes
2answers
30 views

Excel reverse range

When doing a MATCH or anything that returns the first result in a range (say A1:A10) is there a way to reverse the range (A10:A1), such that the it effectively returns the last result? Currently, ...
1
vote
1answer
15 views

How do I return a value that is not in it's own row?

I have a list of serial numbers that have different scan dates: Serial # Started Ended Prev A: L50099 28/02/14 11:50 AM 28/02/14 3:20 PM B: L50099 25/11/15 ...
0
votes
1answer
25 views

Excel custom function | formatting

I'd like to get the SUM of cells with a specific format in the same format. For example, I have a column of column of cells with: 23/02/11 00/01/02 05/12/06 28/15/19 < I want the total to be ...
0
votes
0answers
26 views

Excel If() function not working properly

I'm trying to set up a royalty payment that is the greater of $2000 or 30% of net revenue and the formula checks out, but does not render the correct value of $2,000 when the condition is met. Rather,...
0
votes
2answers
24 views

Is there a more elegant IF solution than nesting duplicate formulas?

Currently, I regularly write index match formulas that, if they pull an empty cell, pull a zero value. I then need to remove this from my results. I don't want to faff about with Find and Replace, and ...
0
votes
0answers
13 views

Relocation of values with an empty cell beetwen them

I need some help with an excel formula for a specific task. I have a raw excel sheet imported from Autocad. It basically looks like 4 columns(A to D) full of data, from 1 to almost 800. My job is to ...
0
votes
1answer
13 views

Subtracting from a SUM range by the number of positive entries returned?

So I have a Formula like this: =SUM(SUMIF(A1:A10,">0")-B1) But I want to subtract the value of B1 multiplied by the number of positive returns from the A1:A10 range. For example, if only 3 entry's ...
0
votes
1answer
20 views

Excel - vlookup with multiple conditions

I'm trying to do a lookup in Excel based on the following. Situation - I want to look up what the speed limit is in a given section of road. Essentially the table I'm looking up specifies that the ...
0
votes
1answer
37 views

Excel 2013 Wildcards on specific range and Sumif

I'm trying to use a wildcard limited to a specific range within a Sumif function. Example: I got these codes: ABC-1 ABC-2 ABC-3 ABC-4 ABC-5 ABC-6 And I want to SUMIF from ABC-3 to ABC-5. I found ...
0
votes
0answers
17 views

Excel SUMIFS Formula Sequential Date Criteria?

=SUMIFS($I$2:$I$2221,$M$2:$M$2221,"VISA",$B$2:$B$2221,"June 1") This is the formula I am using for a sales analysis worksheet, that takes the clutter from the POS report and breaks it down in a ...
0
votes
0answers
24 views

Microsoft Excel reference a table into a single cell/single line

I'm creating a table in where i have references/formulas. I want to derive only values from the table and paste into a single line in the same sheet. Example: This is my table now: ...
1
vote
2answers
25 views

How to link 2 Excel files located in different PC's

I have one Excel file in my laptop and another in my desktop on the office of the work place. How to connect them together?
0
votes
0answers
21 views

getting a cell to reference itself (need it for conditional formatting)

My spreadsheet involves highlighting cells that have values that are also found in a separate range. When using a formula to apply a rule in excel im not sure if the syntax is the same. It says: "...
-3
votes
1answer
34 views

Count in column B if column A=x

In Excel, I want to count the number of Y in column B if column A equals F. Does anyone know the code? For example, in column A, I have people's gender. In column B, I have if they are pregnant or ...
0
votes
1answer
23 views

How can I select the whole string except for the first character from a cell in Excel 2007

I have multiple tables where 1 column contains a string which is a combination of a letter and a number. For example these values can range from B1 to B1000000. My present solution to automatically ...
0
votes
0answers
23 views

Excel formula to match all the possible substrings from a cell to another string

I would like to come up with a formula in Excel to try to find an exact match of any sub-string in column D to any string in columns A, B, or C. I have a list of all the states (column A), counties (...
1
vote
1answer
39 views

Excel to count number of concurrent sessions based on start/end times and PC Name

I know there is a very similar thread to the this one already – Excel to count number of concurrent sessions based on start/end times – however, as much as I try, I am unable to replicate and ...
0
votes
1answer
32 views

How to optimize fields that has “if” function which return blank value on Excel?

Here is the screenshot, it's only an example since I can't post the original one: I made this worksheet to cross-check the employee present with another sheet that has information whether this ...
0
votes
0answers
32 views

I want to split my Excel data into a new row

I need some help in sorting my data. As in the image, the original file looks like the top image and I want to split the size into a new row below for all items as in the bottom image. The file ...
1
vote
1answer
41 views

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

Using 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 ...
0
votes
1answer
28 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
49 views

Excel: Can I use text references without volatility?

A1 contains Namedrange,1,23,3,bla On the row I split it down to having each of the , delimited entries in its own cell. B1 =LEFT(A1,FIND(",",A1)-1), C1= formula results in 1, D1 = formula results in ...
0
votes
2answers
41 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)?
0
votes
1answer
22 views

Combine data from two rows to make one row where that data alternates

If I have 2 rows of data, A1:E1 and A2:E2, and I want a new row to organize that data by alternating the numbers, A1,A2,B1,B2,C1,C2.... Is there a formula I can use that will copy over and keep the ...
0
votes
1answer
23 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
32 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 ...
2
votes
1answer
20 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
49 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
18 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
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. ...
0
votes
0answers
70 views

Excel - How can I use a Start and End date to format cells

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
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 ...
2
votes
1answer
25 views

How to do a conditional count in noncontiguous cells

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
1answer
19 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
1answer
29 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
3answers
39 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
0answers
55 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 ...
0
votes
4answers
45 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, ...
1
vote
1answer
13 views

How to drag and retrieve data from a column in another sheet, thereby skipping a given number of rows in Excel 2013

I have a column (E) containing data in sheet "insulineratio". On another new sheet I want to retrieve those data in column A from this first column "insulineratio" by dragging down. I want this ...
0
votes
1answer
10 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 ...
-3
votes
2answers
52 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
2answers
44 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
33 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 ...
0
votes
0answers
15 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) ...
0
votes
1answer
27 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 ...
0
votes
0answers
23 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 ...
1
vote
2answers
55 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 ...
1
vote
1answer
27 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 - &...