# Tagged Questions

17 views

### Need formula to add enough zeros to total 10 characters

I need to tweak the below formula, so that instead of always adding 5 zeros, it adds enough zeros to equal 10 values (characters). I'm using this in a system (iCIMS), and the result (depending on the ...
36 views

### Searching an array from bottom to top

I have a table array \$E\$14:\$G\$851. Column E just contains unique identifiers, lets say a/b/c/d etc, column F contains hundreds of numbers all less than or equal to 20 and column G contains the values ...
24 views

### How to calculate revenue equally based on start and end dates?

My company phases revenue based on slightly obscure start/end dates and I need calculate how much revenue falls into the current year, based on these dates. If a contract starts on or after the 16th ...
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) ...
35 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 ...
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, ...
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 ...
26 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 ...
27 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,...
25 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 ...
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 ...
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 ...
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 ...
38 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 ...
21 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 ...
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: ...
26 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?
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: "...
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 ...
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 ...
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 (...
41 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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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)?
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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. ...
71 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 ...
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 ...
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 ...
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 ...
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, ...
43 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/...
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 ...
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, ...
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 ...
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 ...
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 ...
45 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 ...
34 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 ...