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

**-1**

votes

**1**answer

28 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

18 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

42 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

51 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

71 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

33 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

21 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

23 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

27 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

9 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

37 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

45 views

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

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

21 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

17 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

25 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

21 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

40 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

30 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

12 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

53 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

21 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

41 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

11 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

28 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

30 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

27 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

31 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

12 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

34 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

41 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

28 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

16 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

50 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

30 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

17 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

35 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

33 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

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

**0**

votes

**1**answer

20 views

### How do I exclude blank cells when trying to count unique values separated by commas for multiple cells

I'm trying to come up with a formula that will count unique values separated by commas for multiple cells and count the blank cells as 0.
Example:
X2; 875 Y2; 575,628 Z2; Blank Should equal ...

**0**

votes

**2**answers

38 views

### How do I get SUM and COUNT of below in an Excel worksheet?

I have dates in one column, names in the next column and counts in other column.
I want the formula to count the individuals as per the date.
1. For example, on 11/1/2014, I want Sum of Kiran's ...

**0**

votes

**1**answer

29 views

### How to apply formula to a cell dependent on data from drop down list

I hope I can word this to where it makes sense. I'm working on a budget. Basically broken down by month. So in the budget, if it is February, I have to estimate the remaining months of the year. I ...

**-1**

votes

**1**answer

16 views

### populate cells from another worksheet

I need to populate a worksheet with a calculation based on 2 cells in another worksheet within teh saem workbook.
In worksheet one I have a table with 'Last Month' and 'This Month' that contain ...

**0**

votes

**1**answer

31 views

### Random Sampling in Excel based on specific criteria from other colums

I have a list of file numbers in Column A, a code associated with the file in column B, and a number of days in column I. Example:
File Code Days
12345 AAAA 100
54321 AAAA ...

**0**

votes

**2**answers

22 views

### Sort Excel Row Based on Values in Another Row

Sorry if this question has already been answered, I looked but I didn tfind anything that was quite what I am trying to do.
I have a lot of spreadsheets where I have something like:
A|B|C|D|E|F...
...

**1**

vote

**2**answers

122 views

### How to fit mm/yyyy in SUMIF criteria

I've looked around for answers regarding this, but am still a bit confused. I have a column A in Excel with dates in the format mm-yyyy (i.e. September 1014 is Sep-14). I want to add all the values in ...

**2**

votes

**1**answer

27 views

### Excel 2007 Formula Issue - Newbie

I have a cell containing 5;9;11;13;15;17. There could be upto 15 different numbers in the cell. These numbers represent attributes.
For example 5 = Padlockable, 9 = Keylock etc. There are around 50 ...

**0**

votes

**1**answer

34 views

### minitab or ms excel function for a conditional count

Is there a way to count how many times some number(zero in my case) occur before another number occurs in the same series in either minitab or ms excel(or any other statistical software)?
Eg: if I ...

**0**

votes

**1**answer

57 views

### Excel COUNTIF criteria using curly brackets for logical OR not working

Given the following in column A:
tom
bob
harry
tom2
bob
harry
tom3
bob
harry
I want to count "tom" OR "bob" using =COUNTIF(A:A,{"tom","bob"}). However the function returns only 1 instead of 4. If ...