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

**1**

vote

**1**answer

45 views

### Make Excel formula yield `blank` as an ISBLANK() = TRUE

Make Excel formula yield blank as an ISBLANK() = TRUE
I found couple poster asking about what are the differences between "" and simply (blank), but now how do make formula return blank?
Example:
...

**0**

votes

**2**answers

20 views

### Does a range of cells contain a value in a list?

I want to answer - does a range of cells (A1:E1) contain any value from a list (G1:G50)
In other words: Does A1 OR B1 OR C1 OR D1 OR E1 contain any value that appears in G1:G50?

**0**

votes

**1**answer

122 views

### Excel, how to check cell value against a column of values?

Using excel 2007, I have 2 worksheets.
The first one is 1 column of about 600 numbers/IDs.
The 2 worksheet is about 30 columns for about 5000 IDs.
Im basically trying to filter out the 600 IDs ...

**1**

vote

**2**answers

332 views

### Convert Text Value to Time Value in excel

I am trying to convert text to time. Basically I copy and pasted the total time of employees but it pastes as text value and then in order to add the time I have to convert it to time and I have to ...

**0**

votes

**1**answer

55 views

### Index/Sumproduct calculation error

I have been working on an excel problem for the better part of 24 hours. I have a formula that almost does what I want but it is not returning what I imagine it should return:
...

**1**

vote

**1**answer

56 views

### How to count blank cells at end of row in n-column table

Suppose I have a table with 50 rows and 50 columns. Each row has a certain number of blank cells, often occurring in the last few columns, with the first few columns being relatively dense. I would ...

**0**

votes

**1**answer

53 views

### How to combine two columns with no data loss for further analysis

Hopefully you good people can help me as I'm pulling my hair out doing this manually and can see no way to automate
At present I have two sets of data consisting of three columns each - Date, Amount ...

**0**

votes

**1**answer

28 views

### Is there a function to get the cell your equation is pointing at as the output rather than the value inside the cell?

For example if I had an function such as vlookup that put me into a cell could I then have another function that gives what cell it found rather then the value inside the cell?

**-1**

votes

**2**answers

45 views

### How to compare multiple columns and set values based off of it

I Reference to the picture in the link below, I need to find the best way to compare which Start Month and End Month row the current month is between and then set a cell somewhere else in the spread ...

**-1**

votes

**1**answer

20 views

### How do I reference a specific row number using a defined column name?

I'm using Excel 2010 and am creating a workbook to define details about products in a store (including category, price, accessories). I also want to define default details to use if the specific ...

**-1**

votes

**1**answer

66 views

### How to make COUNTIF only count subtotal for visible records when filter is applied

I would like a formula to COUNTIF the Brand 3 column and if filtered the COUNTIF changes to filtered criteria.
TOTAL COUNT 0 0 3 COUNTIF(H3:H5,"VoID")
Area ...

**0**

votes

**0**answers

26 views

### Copy distinct values in calc

I have some text data in column A, adding data to that column is the standard use case. I would like Calc to dynamically copy only unique values to, say, column D.
I am aware of OpenOffice: get ...

**0**

votes

**2**answers

120 views

### Find partial text in Excel 2013

In Excel 2013 I would like to find the value in column B in the row where a string matches a string in column A. What complicates it is that the strings in column A are allowed to be shorter than the ...

**0**

votes

**3**answers

54 views

### Excel last appearance of a given string in a column

I am using the LOOKUP function to find the last appearance of a given string in a provided column, like this:
LOOKUP(A3;Runs!D3:D6;Runs!A3:A6)
However, the bounds in the vector are hard-coded: ...

**0**

votes

**1**answer

130 views

### Auto Sequential numbering based on cell criteria - create two independent automatic numbering sequences

How to make the numbering in column A skip any rows that have the Word "TOTAL" in column C but pick right up again in the next rows? At the same time can the rows with the word "TOTAL" in column C ...

**0**

votes

**1**answer

53 views

### How to display a given number of names randomly selected from a list

I have a list of 148 names in Column A. I need a function that would let a user enter in a number and use that number to return a list of names in column B or C. There can be repeats. So column B ...

**0**

votes

**2**answers

23 views

### Lookup and match functions

I have a problem that I have been unable to solve, or find a result that works for me.
I have a sheet with data entered from a lab. The dates are based on sampling time, and each sample point is ...

**0**

votes

**1**answer

28 views

### I need a formula so that if a value in column a change then add +1 to the value of another column

I have a list of product codes in column A. In column B i have a list with text values which i will call "positions" (1B 02 1 etc). In column C I give a serial number for each product code (52001001). ...

**0**

votes

**1**answer

31 views

### Get Excel function that expects a list to use the contents of a cell

A recent question involved cells that contained a comma-delimited list of values, each if which needed to be individually selected and manipulated. The data looked like this:
Cell B1: ...

**0**

votes

**0**answers

12 views

### Prepend text for spreadsheet

I want to have a string value prepended to a XL-style formating in a spreadsheet program (gnumeric). The current formatting is [$-f4f2]hh:mm which should take 1:00 PM as input and convert/display it ...

**0**

votes

**1**answer

43 views

### How can excel take two seperate cells with comma seperated data and pair them up?

I have looked all over for an answer to this and seem to be stuck fast.
I have something like this:
Cell B1: 01/01/2014,01/02/2014, 01/03/2014, 01/04/2014, 01/05/2014, 01/06/2014, 01/07/2014, ...

**0**

votes

**1**answer

90 views

### How to fill down formula so that range references are offset by 5 rows rather than by 1 row [duplicate]

How can I get Excel to increment ranges arguments by more than one row when I fill down a formula?
For example, if I want Excel to keep averaging the next five numbers in a column, how do I set the ...

**0**

votes

**2**answers

29 views

### Excel Highlighting First Empty Cell Below An Entry

I've been trying for hours what I thought would be simple but clearly is not for me!
I have a column where every ten cells has the word "TRUE" in it, the rest of the cells in between are empty. So it ...

**0**

votes

**1**answer

29 views

### Adding another value in Excel cell/column if another cell has particular value

I wanted to be able to create a macro to change the value in Cell B to EPTBSIET if it equaled that value in Cell E - SIETCO. I've been able to compile an IF formula but would rather have the value not ...

**1**

vote

**1**answer

44 views

### Excel duplicates in one column based on another

Hoping someone can assist with this. We have a spreadsheet with data as below:
A B C D E F G
777142 233 4 45151 te01-ccm 0
777303 233 6 45151 te01-ccm 0
...

**0**

votes

**2**answers

23 views

### Match-Search function in Excel?

So I've got 2 columns. Column "Mess" has gazillions of random numbers x digits long. Column "Check" has specific sorted numbers. Now I make a 3rd column called "boolean".
I want a function to check ...

**0**

votes

**3**answers

47 views

### How can I merge 2 sheets in excel?

I have to update a defect database in excel that is filled with data that fluctuates between 160 to 700 rows every day. This database has 19 columns, some of which are numerical and some are ...

**0**

votes

**3**answers

71 views

### Create custom parts for the header and footer in Excel

I have a spreadsheet with several worksheets. On each printed page of each worksheet I would like to have references to some fields that i define somewhere in the document.
More specifically, i would ...

**0**

votes

**0**answers

13 views

### Excel referencing: Go To Box does not fully have the whole path

When I reference to another workbook in the formula line I see the whole path to the workbook. So that is good. But when I have the referenced workbook closed (the one I get figures from) and I want ...

**0**

votes

**1**answer

158 views

### Excel: Matching and counting records (rows) to multiple conditions and unique values

I have two sheets: ‘Data’ and ‘Report’. In the report sheet, I need to calculate some data which requires 3 different formulae.
The Data sheet has column headers in row 1 and many records of data in ...

**0**

votes

**1**answer

31 views

### How can I return corresponding value from Master List by comparing Child List?

I have 3 columns MasterList, Value and ChildList. Now I want to compare Childlist against Masterlist and get the value from corresponding MasterList.
I have below values in worksheet.
MasterList ...

**0**

votes

**2**answers

251 views

### Excel formula to calculate percent of growth over multiple months

I am trying to determine if a customer's number service requests have increased or decreased of a 12 month cycle and by what percent -
F2 thru Q2 show each individual month's number of requests - in ...

**0**

votes

**0**answers

19 views

### Microsoft Excel 2010, Adding/deleting lines in linked workbooks by only adding/deleting them in the Master Workbook.

I have Multiple Woorkbooks linked to one Master Workbook. Each WB has 13 columns and 452 rows(Different Products I sell). Only one column(My Selling Price) is linked to the Master as of right now. ...

**0**

votes

**1**answer

145 views

### Convert Array to String with Excel Formula, is it possible?

Convert Array to String with Excel Formula, is it possible?
I know how this would be done with VBA, but I have to share my workbook, and I have to assume that other people will not be able to enable ...

**0**

votes

**1**answer

40 views

### How to use SUMIF to calculate 12-month moving averages

On a single sheet I have Datapoints listed from top to bottom (A:A) and months from left to right (C:AI). In Column B I have an indicator which is used with SUMIF to 'group' certain data points.
...

**-3**

votes

**1**answer

35 views

### Lookup that Use a cell that contains range as lookup value and lookup vector

Sorry, I can't post image here because of restriction. But, I can't find another way to explain my problem.
I have a sheet like that. And then, I need cell B5 to return a value based on data from ...

**0**

votes

**2**answers

39 views

### Find most frequently used value

I know that that question has been asked before but my question needs more scalable solution!
I have a column G with States, there are 10,000 States in that column.
I need to find out what is most ...

**0**

votes

**1**answer

16 views

### Excel max values across many worksheets

I have multiple worksheets each with 4 columns of 32 names and values. I need to be able to find the highest 8 values(with associated names) from across all sheets. I have been able to determine the ...

**0**

votes

**2**answers

61 views

### Shortening an Excel formula if possible

I am unable to shorten an Excel formula I am using in an Excel database. I have googled it, but this appears to be the shortest way already from the first page on Google.
...

**3**

votes

**1**answer

59 views

### How to convert data in Excel to percentages of total?

Is there a quick way to convert all cell values in a table to a percentage of a total listed in a separate column? I tried posting an image of my data as an example, but my reputation here isn't ...

**0**

votes

**1**answer

33 views

### Formula Required for Costs to date based on number of project days completed

I am trying to put together a template for project costs and am looking for a way to roll forward daily costs for each account code that avoids the need to change the formula on a daily basis.
I have ...

**0**

votes

**0**answers

26 views

### Continue from one excel sheet to other without merging them together

i have an Excel workbook whic contain monthly sheets of sales. Each monthly sheet contains sale figures for various items (in columns) on a daily basis. the days are grouped in weeks.
Now i want to ...

**0**

votes

**1**answer

53 views

### Lookup Values Based on Values in Other Cells

I have an inventory list that contains a manufacturer name and product category for each product. I want to look up the code for the manufacturer and the category code for the product and place the ...

**0**

votes

**2**answers

40 views

### Reference cell range parameter from other cell dynamically in excel

I've got a simple range based formula
=SUM(A3:A$5)
I would like to dynamically assign the range value in the formula by referencing another cell's value, for example
cell B1 = 3 and cell B2 = 5
...

**1**

vote

**1**answer

41 views

### Inserting N Values into N Blocks

I have a column with values that repeat an arbitrary number of times before the next block begins. Some of these blocks, however, contain no number at all. While every other block is unique, these ...

**1**

vote

**1**answer

20 views

### How do I have a Date format show in a Formula that is looking up that cell?

I have a formula:
="('"&A6&"','"&B6&"','"&C6&"','"&D6&"','"&E6&"'),"
And B6 is a date and formatted as dd/mm/yyyy (have tried all forms though) so the ...

**0**

votes

**1**answer

38 views

### in Excel, Can I replace “sheet1!” with a formula?

I have 100 sheets named as 1,2,3....100
then in each sheet, there's a value in cell A1.
I am building a summary sheet now, in the summary sheet, I want A1 = 1!A1, A2 = 2!A1, A3 = 3!A1 ...... A100 = ...

**0**

votes

**1**answer

35 views

### Convert a range to a percentage in Excel

I have a range of figures I want to convert to percentage.
For example I have temperature readings taken every hour over a full day. The range is from 4.5deg to 15.5deg. I want 4.5deg to equal 0% and ...

**0**

votes

**0**answers

29 views

### save a sheet in new closed workbook without links

I have this code which work pretty good but I have a problem that the new exel workbook have links from the old one.
Sub archive()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As ...

**0**

votes

**1**answer

25 views

### How to compare a column with previous column when the new column has more data sets in it?

I am trying to compare weekly account sales numbers to their previous weekly amount. However the new week has more accounts in it (on average 50 more) than the previous one, how can I add these ...