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

**0**

votes

**1**answer

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 ...

**2**

votes

**2**answers

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 ...

**0**

votes

**2**answers

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 ...

**0**

votes

**1**answer

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

**1**answer

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 ...

**0**

votes

**2**answers

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

**1**answer

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

**1**answer

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 ...

**0**

votes

**0**answers

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,...

**0**

votes

**2**answers

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 ...

**0**

votes

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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 ...

**0**

votes

**1**answer

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 ...

**0**

votes

**0**answers

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

**2**answers

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?

**0**

votes

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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 ...

**0**

votes

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**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. ...

**0**

votes

**0**answers

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 ...

**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 ...

**2**

votes

**1**answer

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

**1**answer

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

**1**answer

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

**3**answers

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/...

**1**

vote

**0**answers

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

**4**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**2**answers

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 ...

**0**

votes

**1**answer

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 ...

**0**

votes

**0**answers

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

**1**answer

28 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 ...