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

**2**

votes

**2**answers

12 views

### if indexmatch = #N/A keep original cell value

I have 2 worksheets in excel.
Sheet 1 is a price list given to us by a supplier
Sheet 2 is a list of all the products we supply (can be from multiple suppliers).
In the first column of each Sheet is ...

**0**

votes

**0**answers

18 views

### Ms excel while entering more than 15 digit no [duplicate]

I want to add more than 20 digits in an Excel cell.
The current format of the cell is general, it converts the number to an exponential format. I tried with a number format and accounting, but when I ...

**1**

vote

**1**answer

32 views

### Cannot Use Autosum on cells that Contain Formulas

I have cells that have IF formulas in them. Excel won't let me autosum the results of those. It's as if Excel does not recognize those cells as numbers. Granted, numbers do show, but in actuality the ...

**0**

votes

**1**answer

21 views

### How to display unique numerical values from different columns into a single cell (separated by commas)

I have a unique problem I have been trying to tackle with no avail.
I have scoured through the internet but only found partial solutions and not a complete solution.
I am looking for a function (or ...

**0**

votes

**1**answer

18 views

### How can I count unique comma-separated values - Early Bound vs. Late Bound

I am trying to display unique values from a string of values in a single cell.(separated by a comma).
I've come across this solution:
How can I count unique comma-separated values in Excel 2010
...

**2**

votes

**1**answer

29 views

### How can I look up the 2 axis values by inputting a number?

I'm a graphic designer and not very familiar with Excel but I'm fairly sure it's the simplest solution to my problem. I have a table (see below) with 2 axis going from A to L. Inside that table are a ...

**1**

vote

**2**answers

35 views

### Microsoft excel - formula dragging

How can I drag a formula to all the adjacent columns with similar intervals in it?
For example:
in column F, the formula is ('Time sheets'!G10-'Time sheets'!F10)*24
and in column G, it's ('Time ...

**3**

votes

**1**answer

25 views

### Excel formulas - how to make a cell blank down the page

I am having trouble figuring out the formula for my budget.
Cells look like this
A B C D
DATE WEEKLY BILLS TOTAL
2015 $50 $1,956.73
Jan ...

**1**

vote

**1**answer

23 views

### Cells evaluated by user-defined module do not update automatically

I read the questions existing but no answer seemed to help. I have a function I wrote in VB which evaluates sum of cells in a certain range by font color, it all works well when I type the function in ...

**1**

vote

**1**answer

25 views

### How to grep summarized value from excel cell range?

I want to get summarize information from the following sheet. I don't know how to summarize this information.
My scenario is like this.
If I input -6, I want to find my target value in this ...

**-1**

votes

**0**answers

19 views

### How can I automate the process of printing 2 rows of data from one sheet after pasting it onto the form on another sheet?

Sheet 1 of my spreadsheet has the following form:
A B C D
They are just columns named 'A','B','C' and 'D'.
Sheet 2 has the following data:
A
1
2
3
4
5
6
7
8
9
10
I want to print pages of ...

**0**

votes

**1**answer

22 views

### Clamping values while averaging

I have two formulas that are calculated using the same dataset. One formula calculates the average of the values, while the other calculates an average with a maximum allowed value of 60. I'm ...

**0**

votes

**2**answers

35 views

### Data validation in Excel- all values in comma-delimited list within cell range

I have a reference table of memos and some information about them, including what other memos they reference and are referenced by. I want to make a data validation rule that will appear red if any of ...

**4**

votes

**2**answers

336 views

### Generate Combinations in Excel

I have an input row which contains the cells:
30
30
30
50
50
60
100
I want to generate all possible combinations of length 3 from this set. So I would want rows containing 3 populated cells and ...

**2**

votes

**3**answers

21 views

### Excel SUMPRODUCT - exclude header cells from formula

I have two columns, one with a date and one with values, and I want to sum up all cells, where the year of the date is the desired year. My problem is, that I have headers in the table, and I don't ...

**-1**

votes

**1**answer

22 views

### EXCEL: Can I get a count of a certain value from columns with certain headings, only from the columns with certain text?

A screenshot of the spreadsheet1
Hi
I am looking for a way (in the form of an Excel formula) that can do this
I need to count the number of "o" in columns with headings "Threat", for each row. For ...

**-1**

votes

**0**answers

40 views

### Excel Formula to show All dates in a range after today

I am trying to build a spreadsheet that I am using the lookup feature to take data entered on sheet 1 to locate that data on sheet 3, I need it to then list all Dates after today for the current ...

**0**

votes

**0**answers

10 views

### Auto-Fill data between two spreadsheets, adding new row, keeping same column, when new val

So I have a list of items that need to be constantly added to a list for auditing purposes. Problem is, the item names constantly change and also on occasion, new items are added.
Essentially, I am ...

**0**

votes

**0**answers

12 views

### Excel save relative formula for re-use

I need to go through a sheet, filtering on a series of fields and running a formula against the resulting columns.
However, typing the formula each time is slow and annoying.
How can I save the ...

**0**

votes

**2**answers

11 views

### Excel filtering all cells in column instead of those filtered

I am trying to add column value in a sheet with a filter applied.
The problem I have is that Excel is adding values for rows that have been filtered out rather than just those the filter is showing.
...

**0**

votes

**1**answer

31 views

### How does this SUMPRODUCT(COUNTIF( )) function work?

I was searching for a solution to count distinct values in a range, and came across this article. The example provides a list of ten names comprised of four unique entries. It then uses the ...

**-1**

votes

**1**answer

61 views

### How do I reference another cell based on the value of another cell?

No one understood my question which is understood due to my bad explanation.
I'll put an example to avoid further confusion.
In Sheet2 I want to change A2's formula then it will fill B2,C2, and ...

**1**

vote

**2**answers

30 views

### How to use OR in a COUNTIF function when dealing with dates and “>” characters

I want to write a function whereby I could count a cell if it is blank OR greater than a date.
This is a piece in what will be a much larger formula so looking to avoid a solution of ...

**-1**

votes

**1**answer

57 views

### Formatting any cell referenced in a Formula

I have two sheets, one with formulas pulling data from the second. I need to create a macro or some conditional format to highlight the cell green if the data was pulled from the second sheet. sheet ...

**0**

votes

**0**answers

39 views

### multiple IF conditions and answers

This is a cashflow spreadsheet. I want the column next to the expenses column to show me the source of the expense.
The expenses are arranged in spreadsheets, so for example; checks, taxes, general ...

**-1**

votes

**2**answers

23 views

### In a table of groups and members, how to reduce a certain percentage of members per group?

I have an Excel spreadsheet with a table of groups and members. For example:
A Jonathan
A Roger
A Donald
B John
B Lilya
C Richard
C Lorry
What I want to do is to reduce the number of members per ...

**0**

votes

**1**answer

23 views

### Excel help, formula for one cell looking at information in another

Excel, How do I get one cell to come up with "N/A" when "No" is selected from another cells drop down?

**0**

votes

**2**answers

27 views

### Adding components of formula (net vs. gross)

I would like to add the components of a formula across a range. We have one section that shows the net cash flows for specific items, and multiple specific items.
For example =8+9-5 shows "12" ...

**0**

votes

**1**answer

19 views

### Why is the MATCH function is not resolving my lookup array?

In Excel 2010, I am using two Excel tables. One table (ActiveDS) has a single column and contains a list of our active data sources. The other table (ApproverRole) has a number of columns, including a ...

**1**

vote

**0**answers

23 views

### Excel - How to link Index/Matching formula with Indirect formula for dynamic column reference

I have a worksheet in which I am looking to utilize the Index/Matching formula to pull in exact values for the unique intersection of Department & Account values on a separate data sheet.
However ...

**-2**

votes

**2**answers

36 views

### I want to change excel [closed]

My cell looks like this 3s4446V6PK1HVGT, I want to change it to look like this, PK1HVGT after the formula is done.
The pretext is different for all cells but the outcome will be 7 characters long ...

**3**

votes

**2**answers

33 views

### How can I how extract/parse variable info from the END of a string in Excel?

Example1:
01/28/14 Interest Earned 0.10
Example2:
12/27/13 CHECKCARD 1226 LIONHEART BOOKSTORE SEATTLE WA -25.13
In the two examples provided, my goal is to separate out the information ...

**1**

vote

**2**answers

77 views

### Enter time without colons (hhmmss) and calculate time differences in Excel

I want to enter the time without colons as hhmmss and calculate the time differences with another cell. For example I enter the beginning time in one cell and the ending time in another. Then I need ...

**1**

vote

**1**answer

33 views

### Find the last data in a table with conditions [duplicate]

I want to get the latest END BALANCE of customer, when the STATUS is success. Picture of the table and result wanted is attached above.

**0**

votes

**1**answer

29 views

### How to synchronize text in Excel in multiple sheets

I have Excel file with four sheets.
How to make duplication using Excel built-in function using specific parameters?
Example:
Sheet 1 - Total:
A B C
1 Iveco ...

**0**

votes

**1**answer

19 views

### Excel TODAY() function

I have two tables in excel
The first table is called Oranges, it shows where the oranges were produced and where they were exported to:
The second table is supposed to show the total oranges ...

**2**

votes

**2**answers

47 views

### Creating an Invoice in Excel

I have only dabbled with Excel and not much in quite some time now.
I have an Excel Invoice laid out so Column A is a name of a job and each row in column A can be a different job.
Now column B ...

**-2**

votes

**1**answer

30 views

### # of days in a month based and start and end dates

This question relates to Calculate Total Number Of Days Per Specific Month.
If you look at the original question what IF you have a start and end date Jan - March and you need to know how many days ...

**0**

votes

**0**answers

28 views

### Excel formulas linking sheets in workbook

I am looking to automatically update a results sheet by moving down by one row every time I input a set of data in another linked sheet (raw data) in the same workbook. I will be overwriting the Raw ...

**0**

votes

**0**answers

14 views

### Aggregate values by month in Excel [duplicate]

For each row, I need to aggregate values by month.
Date Jun Jun Mar Jun
Name1 10 11 14 12
Name2 2 34 4 7
Need Result
Date Jun Mar
Name1 33 14
Name2 43 4

**1**

vote

**2**answers

33 views

### Need SumIf based in two Criteria

I Need Help I need a SUMIF, when name and date are match.
Date Jun Jun Mar Jun
Name1 10 11 14 12
Name2 2 34 4 7
Need Result
Date Jun Mar
Name1 33 14
Name2 43 4

**0**

votes

**1**answer

7 views

### Display empty cell if referencing empty cell (formula)

I'm working on an excel spreadsheet, and im aiming for a clean interface with almost all cells filled with specific formula.
Ex formula on column V : =IF(G1=" LNN ","PENDING", "N/A")
What's ...

**3**

votes

**2**answers

24 views

### Drop Down List and one formula

I am trying to get an average depending on two drop down lists.
But i would like to get an average even when the second drop down has no input in it.
The formula I use is a simple: AVERAGEIFS with: ...

**0**

votes

**1**answer

31 views

### Is there a formula to look for partial text in a cell?

This is what I'm using right now in the formula:
=IF(E6="Mix Design Gr S","By Mix Design","")&IF(E6="R-Value","By Soils","")&IF(E6="PS Package","By Soils","")
Is there a way to only look for ...

**0**

votes

**2**answers

33 views

### How to extract the string from between the Nth and N+1th occurence of a character in Excel 2010?

I have a spreadsheet with a column that contains a list of one or more database names. Unfortunately, the turnaround time to get this report altered is long. So I need to manipulate the data.
The ...

**0**

votes

**2**answers

23 views

### Range mapping in Excel

I have a column of data in Excel with numbers [0..100]. I need a new column, which maps these to a new set of values such that:
> 90 : 4
80-90 : 3
70-79 : 2
60-69 : 1
< 60 : 0
I attempted to ...

**0**

votes

**1**answer

21 views

### Trying to create sequential hyperlink to worksheets in the same book

I have a summary page, and 50+ other pages, numbered 1-50. what I'm trying to do is create a hyperlink that will go to the correct sheet when clicked. I've got it working for one, but when I drag it ...

**0**

votes

**1**answer

23 views

### How do I convert sql to excel syntax?

I have two sheets in an excel file, "sheet1" and "sheet2".
The first sheet has a table called Oranges, it shows where the oranges were produced and where they were exported to:
On the second sheet ...

**1**

vote

**1**answer

28 views

### How to Calculate Total Less Duplicates?

I am attempting to create a formula that calculates deviations from a certain parameter threshold in hours. The parameters being recorded are water flow in gallons per minute and chemical addition. ...

**0**

votes

**1**answer

35 views

### Using libreOffice Calc function in macro

I am trying to create a basic macro function in calc to perform a hexadecimal to binary conversion with more then 10 bits. But I've got a lot of troubles.
First, where can I find actual ...