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

**0**

votes

**2**answers

59 views

### Excel formula to change one cell value to another value based on a third cell

to make this simple a1 has a value that can change, b1 has the formula to test a1 value. c1 is reset to a chosen value based on a1.
I have a table that people can input values. I have a list box in ...

**0**

votes

**2**answers

70 views

### Excel Formula To Get First and Last Non-Zero Values

I have checked the similar questions but they are little bit different from what I need to do.
What I need to do is create a summary table for large data, some of that data is this:
[A] [B] [C] ...

**0**

votes

**1**answer

25 views

### Why does this Excel formula return 0.042361? =Sum(Indirect(Address

In sheet3 columns A,B,C,D are the value of row numbers. Thus, A1 to D1 contain 1, A2 to D2 contain 2,...etc.
Why does the following formula in G2 of Sheet2 return 0.042361? All numbers are integers. ...

**3**

votes

**1**answer

57 views

### Return multiple values from one reference id

is it possible to have an excel formula that would create the output table below?
initial data:
╔════════════╦═════════╦══════╦═══════╦═════════════╗
║ product id ║ item ║ size ║ price ║ size ...

**0**

votes

**1**answer

32 views

### How to fill up gaps in excel

My data in excel has for example the following data in columns A and B:
A B
1 4
2 6
5 88
9 67
12 45
and so on.
I would like to transform it as following:
A B
1 4
2 6
3 0
4 ...

**1**

vote

**2**answers

36 views

### Extract string containing a word from an Excel cell with hundred of strings

I have an Excel workbook, in column A each cell contains all URLs of a site (this means hundreds of URLs in one cell).
Among all the URLs within a single cell I want to extract only the URL that ...

**1**

vote

**0**answers

39 views

### Sum data from muliple cells, multiply by other cell range, reference on other sheet

Ive had a couple issues with this workbook recently this seems like the biggest. As you can see in the pics, this a time sheet that needs F15 multiplied by Q15, then added to F20 which was multiplied ...

**1**

vote

**0**answers

24 views

### How can I make an excel user defined function (UDF) work in an array function (CSE)?

I am using Excel 2010.
I wrote a UDF to create a unique number for each month:
yearmonth = 12*Year(date) + Month(date)
Now I want to use it in a CSE:
...

**-2**

votes

**0**answers

19 views

### I need to know the answer to a formula [migrated]

I have a question, I've got a bunch of cells that have formulas to display the day of the week according to the date above it. (I'm using google sheets by the way)
I want the cell underneath the day ...

**2**

votes

**1**answer

45 views

### On Excel I want to create a list from another sheet based on two values

I have 2 values in dropdown menus in cells B2(DATE) and B5(ITEM).
from these values I want to create a list of items(based on B5) that were sold on that date(B2).
The transactions are listed on a ...

**0**

votes

**1**answer

43 views

### Formula to extract certain character positions in a string

I'm busy here with excel but kind of struggling with some kinda formula.
I got first and last name in one cell and I want to combine them in an another cell. but I just want the first letter of the ...

**1**

vote

**2**answers

22 views

### Excel EDATE Formula

I'm using Excel 2013. I have an edate formula in column B which looks at at the date in column A then adds 5 months onto it. My question is, if there is a blank cell in column A a weird date appears ...

**1**

vote

**0**answers

28 views

### Excel Formula to autopopulate a cell and mark as Assigned

I am attempting to auto populate a excel cell with an account number with the following formula.
=INDEX(C2:C20001,MATCH(FALSE,B2:B20001,0))
Column B contains True/False and Column C contains the ...

**0**

votes

**1**answer

27 views

### Synchronize a part of two worksheets in Excel 2013

I am trying to build a workbook in excel where I want to synchronize a part of two sheets with each other. I search around on the internet but didn't find the solution yet. Basically what I want is ...

**2**

votes

**2**answers

28 views

### Excel fiscal quarter formula

How do I write formula to drive Fiscal quarter from Date
My 2016 fiscal year starts from Feb 2015 and ends in Jan 2016
So for Feb 2015 the desired out put is Q1-16 and for Jan 2016 is Q4-16

**0**

votes

**1**answer

16 views

### Taking data from an adjacent cell by name in cell

I am a basic Excel user.
I do a monthly rota which has the staff name in one cell and in the following cell it has the hours worked.
Is there a simple way to write an formula so that Excel takes any ...

**0**

votes

**1**answer

30 views

### VBA Script loops in all sheets but doesn't produce output in all sheets

This VBA script I have loops through all sheets but I don't see the output in all sheets. What would be the reason be?
Sub A()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim ct As ...

**0**

votes

**1**answer

13 views

### Keep an element as constant on Formula

I want to keep one element of the formula as "constant" but without writing it as a number in the formula e.g.
I have this function:
=COUNTBLANK(B2:M2)*Q1+N2
And if I drag it to the rows below it ...

**0**

votes

**0**answers

11 views

### Formula to recopy data while skipping columns in Excel 2013

I want to fill a column in Excel (say C column, starting at cell C8 for example and filling the following ones C9,C10,C11...) with values that are repeated on a same row but every 8 column ...

**2**

votes

**1**answer

46 views

### How can I execute this function using VBA in excel?

I'm trying to get this function in VBA but I'm new to it. I don't seem to know how I can get this done:
What I'm trying to do is, for each cell in column B that's numeric, I want to take the next ...

**-1**

votes

**1**answer

37 views

### How to do this function in Excel?

I'm not an advanced excel user but I have a query about it. I have a set of values in cells column B and there are random empty cells between one value in column B to the next value in a cell in ...

**2**

votes

**2**answers

77 views

### Copy formula down, skipping cells but have it pull data in single increments from other sheet

I have a time sheet I'm helping to create, and it pulls data from sheet 1 and adds it to a list on sheet 2. The data needs to reference sheet 1 at A42, then A73, A115, A146, A188, etc., following a ...

**2**

votes

**3**answers

47 views

### If AND OR nested Function with multiple answers

I having issues with a fairly complex nested IF/AND/OR formula to manage a sales incentive where there is a payout gate and multiple payout caps.
Rules are:
Salesrep earn 1$ for every product sold ...

**0**

votes

**1**answer

26 views

### Excel formulae to pick corresponding matrix value

Can you help with the formulae which can provide me the corrresponding row/column value at the intersection point
A B C D E F
A X
B X
C X ...

**0**

votes

**2**answers

41 views

### Count skipped cells

I'm aware of IF BLANK and COUNTA but I can't quite get what I'm after which is to have a formula (not VB script) to count the number of skipped cells in a column.
For example I have
a1: 10
a2: 12
...

**0**

votes

**1**answer

21 views

### Excel 2013 auto copy formulas to non-sequential rows

Can I auto copy a formula down rows when the formula references data in non-sequential rows? The formulas in column B on Sheet1 are set up as follows:
B1 references Sheet2!B1
B2 references Sheet2!A1
...

**0**

votes

**2**answers

47 views

### (Google Sheets/Excel) SUMPRODUCT but ignore empty cells

I'm building my girlfriend a Google Sheets spreadsheet that will allow her to automatically compute her students' grades based on grades she gives her students on different projects, exams, and ...

**1**

vote

**1**answer

29 views

### compare two sheets using vlookup [closed]

I have two excel sheets, 1st sheet (very long list, e.g 15 person) named register has 2 columns- ID and Name. 2nd sheet (shorter list e.g 8 person turn up ) named attendance has 3 columns - ID, ...

**1**

vote

**3**answers

34 views

### Automatic text entry dependent on text in other cells [closed]

I have two columns of text and want to automatically enter text into the third column depending on the text in the previous two columns. It's a risk matrix; for instance if A1 says 'Likely' and B1 ...

**0**

votes

**1**answer

36 views

### How to copy a formula or value into another formula

I want to know how to copy a formula into another, while keeping the original values. For instance, I have in range A1:A5 five values (a mix of constants and/or formulas) and I need (in that same ...

**1**

vote

**0**answers

42 views

### Excel 2010 Budget Spreadsheet [closed]

I have a nice budget spreadsheet made up and I'm trying to create a Money Transfer area to track transfers throughout the month. For example, from checking to savings. There's a box that tracks ...

**0**

votes

**2**answers

49 views

### Combining two IF statements into one cell

The following two IF functions work on their own but I want them in the same cell
=IF(AND(E4="female", C4=3),0,1)
=IF(AND(E4="male", C4>1.5, F4>3.5),0,1)

**0**

votes

**1**answer

35 views

### Search multiple sheets and return yes or no

I have four sheets in which data is recorded. I want a formula that will search all four sheets for an ID number and return yes if the number is found and no if it is not. If it doesn't find the ...

**0**

votes

**1**answer

36 views

### Excel formula to lookup row number

I have the following columns on a spreadsheet
1 ITEM LOCATION EXISTS?
2 apple 3
3 banana 6 TRUE
4 fish 9
5 dog
6 cherry TRUE
7 goat
8 horse
9 ...

**0**

votes

**1**answer

56 views

### EXCEL advanced date calculations

I have 2 columns of dates, Expected delivery and Delivery.
They could look like this:
expected delivery
12-08-2015 10-08-2015
12-08-2015 12-08-2015
12-08-2015 14-08-2015
01-09-2015 ...

**6**

votes

**2**answers

98 views

### How to find and replace the character “*” in excel text values in cell, but NOT formulas

Very similar to this question:
How to find and replace the character "*" in excel
But I need to leave formulas untouched.
I've got about 50+ sheets that have two types of cells with "*"
...

**0**

votes

**0**answers

32 views

### Populate information from on spreadsheet to another on same excel File

I am creating two sheets for one excel file for business conferences . One sheet will be an inventory list of items being sent to conferences, the other a sheet that contains details of the ...

**0**

votes

**1**answer

30 views

### Count matches between two arrays

How can I count the number of entries two arrays have in common, using just standard excel formulae (i.e. no VBA)?
Can't find an answer anywhere.
e.g.
A B
+-------------
1| 1 10
2| ...

**0**

votes

**0**answers

27 views

### Excel: Storing a Path to Access a Closed Workbook

I have a workbook (let's call it A) which accesses another workbook (let's call it B) which is closed.
From A, I need to access data on B which may be on different worksheets. However, B contains a ...

**3**

votes

**1**answer

19 views

### Checking a range of dates

I would like a formula that will return Yes only if a range of cells in the same row all have dates in the future, and if five other cells in the same row have the value "Yes".
I tried to just check ...

**0**

votes

**2**answers

34 views

### How do I get nothing to print when using ISBLANK nested formulas?

I have 6 columns and 3 equations : E*F=G, H*G=I & G+I=J
I don't want zeros to print if a cell in F or H is empty.
Currently I have these equations and everything is dependent on the F & H ...

**1**

vote

**4**answers

66 views

### Adding Quotations and Brackets to format of cell conditionally

I have three columns:
Word | Formatted Word | Type
Chicken |................| Noun
Running |................| Verb
If the word in A2 is a Noun, I would like B2, the formatted word, to look like ...

**0**

votes

**1**answer

13 views

### Nested Ifs for personal accounting sheet

I have a personal accounting sheet with 5 columns. C is Debit, D is Credit and E is Balance. I want to do 3 things but I've been confusing myself for the past hour.
If C5 and D5 are both blank, ...

**0**

votes

**1**answer

67 views

### ISO Weeknumber formula explanation

can anyone breakdown this ISOWeekNumber formula and explain to me in detail how it formula works?
=INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4))/7)+2
and this one?
...

**3**

votes

**1**answer

47 views

### Excel — Tell whether a string contains any value in a table column

Background
I'm using Excel 2013.
I have an Excel table named SiteCategories, with a number of Category columns, and then sites in those columns, like so:
So we could get to those values using ...

**1**

vote

**3**answers

44 views

### How to find average of first difference in one set of column excel/google docs?

I have one column with numbers eg. 130, 131, 135, 140, 120.., I would like to find the differences eg. 131-130, 135-131, 140-135.. and find their average without using another column to find the ...

**0**

votes

**1**answer

42 views

### Excel 2010 VBA to remove duplicates and then sumif base on the unique data

My work requires data comparison from 2 different sources (sheet 1 is source 1 and sheet 2 is source 2). Both sheets contain duplicate values (the Delivery Order)
Because I need to do this kind of ...

**0**

votes

**1**answer

17 views

### Share Excel “Template Formulas” between sheets

I have a spreadsheet that takes user inputted values and performs some fixed computations to generate a number of cells. I plan to use these computations in a number of sheets with different user ...

**0**

votes

**0**answers

21 views

### 3 conditions in a nested Excel formula

I am using 3 conditions in a nested formula, each condition has a number of variants. So far
=IF(I14=60,IF(AND(H10<10,D75=1),0.5,IF(I14=60,IF(AND(H10<30,D75=1),1,
and so on.
This works ...

**0**

votes

**1**answer

27 views

### Look up specific string in row (not column) and return value of cell x columns left of that location

My data looks like this:
Sheet 1:
A B C D E F
1 2 3 a b c
1 2 3 a b c
1 2 3 a b c
Sheet 2:
A B
a
b
c
Now on Sheet 2 in Column B I would like to return the result of: look up each value of ...