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

**0**

votes

**1**answer

23 views

### Picking N Values at Random From Column

Is there a way to have Excel pick N values at random out of a column where the total number of items in the column is greater than N? I was thinking of just doing =RANDBETWEEN(0,1) but this has the ...

**0**

votes

**2**answers

32 views

### How to assign rows in multiples of a number (Excel/Google Docs)

For example, I want the first entry in Column B (B1) to show A5, then B2 shows A10, then B3 shows A15.
How would I go about doing this?

**0**

votes

**0**answers

30 views

### Dynamic Scenario output in excel

I have a model where in the input numbers going into the model depend on the cases/scenarios i select from a drop down menu ( Base, Best and Worst). These numbers are then used across all remaining ...

**0**

votes

**1**answer

29 views

### Copying worksheets in Excel

I have a shared Excel workbook with two worksheets in it named "PREVIOUS" and "CURRENT". At the end of the night, I would like to click a button or perform some action to have the CURRENT sheet copy ...

**-1**

votes

**2**answers

88 views

### Excel: Complete row Hash Value calculated as a Numeric value in a cell on same row

I have to manage "Change Control" on Excel spreadsheets comprised of 1000's of rows and dozens of columns.
Downstream Teams often submit revised columns that I have to incorporate into a master ...

**0**

votes

**4**answers

56 views

### Excel - How to add cells that have letters and numbers

For example:
A1 - "AA123"
How do I get B1 to read "AA124" and C1 to read "AA125"?
I know that in A1 I can drag the box to the right and it will populate with this, but I need a formula for this. ...

**-1**

votes

**1**answer

50 views

### Closest thing to variable in Excel formulas( not VBA)

I have following formula
=MID(G34,FIND(" ",G34),FIND(" ",G34,FIND(" ",G34)+1)-FIND(" ",G34))
G34 ="begining middle end"
The goal of this formula is to return "middle" and it achieves it, but the ...

**2**

votes

**1**answer

34 views

### How did I hide a Microsoft excel 2003 formula?

I have a problem with a spreadsheet that I have had for some years and cannot recall how I managed to set up the condition that the formula takes.
I keep a record of fuel costs whereby I enter the ...

**0**

votes

**2**answers

55 views

### How to calculate with Microsoft Excel the correct amount of minutes?

64 people receive services 105 minutes per day. How can i use Excel to calculate the total minutes of service per day and then multiply by 7, so i have the total amount of minutes per week, and then ...

**0**

votes

**3**answers

54 views

### group values in set of 5 and then return the first value from each group

I have a long list of values of a stock in minute format. The column C shows the open values(per minute) and column F shows the corresponding close values
Now I want to covert this data into 5 min ...

**0**

votes

**1**answer

36 views

### Adjust sequence position of barcode scanning result

I've been improve our warehouse record method by barcode scanning.
But the problem defaut scanning result inside excel format is after scan cursor will move to below cell ( ex : A1 to A2 to A3 etc).
...

**-1**

votes

**2**answers

40 views

### larget/smallest number in a range in excel without overlap

I have a sheet with 1000 rows and 5 columns.
1) What I want to do is in a cloumn find the biggest number in 5 adjacent row (e.g. row 1-5 in column A). then find the biggest number in next 5 rows (i.e. ...

**0**

votes

**2**answers

47 views

### Using the SUM Function in Excel with N/A values

I am trying to create a formula for the Total cell at the bottom of a column. The problem is that some of the values in the blocks I am adding up are N/A values. (All the values in the blocks are also ...

**2**

votes

**2**answers

506 views

### A blank cell not registering as zero in excel

I am running a complex IF equation in Excel 2003 (yes, the computers at work are that old too) and it is important to the equation that blank cells are valued at zero, which is, I understand, how ...

**-2**

votes

**1**answer

167 views

### Excel, using cell text references as input for formula

First of all thanks for your help. I have a problem regarding the use of text into formulas. I am working on a huge dataset of stocks of several years. The stock are not correctly grouped. I used an ...

**-1**

votes

**1**answer

28 views

### How to specify a month in cell A1 and have all the dates that fall in that month appear automatically in row B?

I have a question about Excel. I am using Excel 2010 and I want to be able to create a table that will auto populate the dates within the month that is specified in cell eg A1 into a row in B for ...

**1**

vote

**1**answer

37 views

### Excel 2007 formula to find out date from year and week number?

I'm trying to make my work hour notekeeping a bit easier. I note my hours by project so that Monday - Friday are in their own columns. I report my hours based on this weekly, so each worksheet has one ...

**0**

votes

**1**answer

250 views

### How to reformulate Excel formula with too many levels of nesting

I need to be able to use the following formula in Excel to choose a formula used on a different sheet based on the values in drop-down menus in cells D2 and D3. The only problem is that the formula ...

**1**

vote

**1**answer

92 views

### How to fix the sheet name part of an Excel cell reference when the whole sheet is copied?

I want to reference another cell in my worksheet in such a way that when I copy the sheet, I am still pointing to the cell on the original sheet. If the reference is to a cell on a different sheet, ...

**1**

vote

**1**answer

33 views

### How to efficiently reference an entire row in Excel?

I am working on a budget spreadsheet in which I have to pull in cell data from another external spreadsheet. I have to do this for several different sheets with massive amounts of data in the sheets, ...

**-1**

votes

**2**answers

49 views

### Excel create reference to whole column throught a formula

I have a spreadsheet where I need a reference to a whole column, catch is that I don't know which column ahead of the time, so I use MATCH function to find that out. Once I find the right column I ...

**0**

votes

**1**answer

41 views

### Comparing 2 Cells and then based on their values Multiply another cell

I have a spreadsheet for my insurance business. I want to have a spreadsheet to determine my commission on a monthly basis. So I have one column for Line of Business (Auto, Home, etc) and then another ...

**1**

vote

**4**answers

251 views

### How to construct a conditional COUNTIF formula

I have a worksheet where in D I have time (0600,1800,2400) and in R I have disposition e.g (accomplished, pending etc).
What I want is a formula to count the disposition in column R as per the time ...

**1**

vote

**1**answer

73 views

### How to combine data from two sheets and conditionally format rows based on sheet of origin

I have 3 sheets in Excel "Open", "New", "Combine"
"Open" Sheet (Old list)
Ticket ID | Priority | Department | Status
36009 | 1 | Finance | 01-Open
34781 ...

**-1**

votes

**2**answers

61 views

### Excel possible to fill two cells with one if-formula

I have searched on the internet to look if this is possible :
IF(logical test ; true value ; false value )
This is what i try to make for example
A2 =IF( B2 = 20 ; "True" & give C2 value ...

**0**

votes

**1**answer

51 views

### Can't get VLOOKUP working with roster spreadsheet

I have an Excel spreadsheet set up as a roster. The way the roster is setup is for the ease of the person creating the rotation. This roster is not easy to read for the staff.
Mon ...

**1**

vote

**0**answers

47 views

### ArrayFormula + Average giviing me div/o error

I have the formula on B2
if(D2="", "", AVERAGE(D2:AF2))
This gives me the average of all numbers in the row, and takes off the ugly error when there is no numbers. I tried spreading this formula to ...

**0**

votes

**2**answers

34 views

### How to use different formula in case of en error?

I want to run the following formulas on a cell, in this order,
in case the previous formula returns an error.
=MID(P2,SEARCH("q=",P2)+2,SEARCH("&",P2)-SEARCH("q=",P2)-2)
...

**0**

votes

**1**answer

23 views

### Excel: How to strip search terms from a cell (referring URL)

I have cells that contain a referring URL of visitors who entered my websites.
this URL always has a different lenght, but it's constant format is such that the search term always shows up between p= ...

**1**

vote

**1**answer

135 views

### Managing different currencies in Excel budget sheet

I need to enter the values as I write cheques (ie $100 USD and $100CDN, not $111.55 USD and $100CDN) otherwise it will be a nightmare for me to match entries to invoices. I could create a column for ...

**1**

vote

**1**answer

12 views

### Why does ADDRESS function return an error across different worksheets?

When I try to count cells that match my criteria using the address function, I get mixed results depending on which sheet the formula is nested.
For example, if I use the following to count the ...

**0**

votes

**1**answer

31 views

### Excel Values Require Enter within Cell to recognize as number

I have a worksheet where I am getting direct output from STATA that has a weird character format that included " =[ number ]". I put in a new column to strip the characters and leave the numbers. ...

**0**

votes

**2**answers

53 views

### Importing multiple CSV files

I have to run a daily stock analysis where I pull multiple .csv files, and then have to go through an annoying task of deleting unnecessary fields from the .csv's. Only then can I start compiling the ...

**2**

votes

**2**answers

38 views

### How to find the last value in a column in excel?

I am tracking rooms booked in a hotel on a daily basis in Excel. Every day I input the number of rooms booked in a column to show a trend of pick up over a period. I'd like to return the value of ...

**0**

votes

**1**answer

31 views

### Count Row and Column in Pivot Table

Hi I have a sheet that collects the following
++++++++++++++++++++++++++++++++++++++++++++++++++++
+ Name + Address + Borough + Postcode + Date Added +
...

**0**

votes

**1**answer

479 views

### VBA: How to replace entire Excel cell value basing on its content?

I have an Excel Spreadsheet with some names in a cells. There can be as one name per cell as multiple names in one cell.
For example: Cell A5 contains "Joe", cell BD54 contains "Joe;Harry;Molly", ...

**0**

votes

**1**answer

26 views

### Excel INDIRECT function worksheet names

So I have a series of worksheet in my workbook names A1-A4,B1-B4 etc. I'm trying to make an index page that pulls some of the data from each worksheet into a table but can't seem to get the INDIRECT ...

**1**

vote

**1**answer

35 views

### How to compare two sheets and get the common rows

I have a excel file which has two sheets and below is the first sheet and second sheet. I want to compare the two sheets (ex: each row of first sheet with with all the rows of second sheet ) and then ...

**1**

vote

**1**answer

51 views

### Matching and updating columns in Excel from two documents

I'm completely green with Excel, whenever I think there has to be a quicker/easier way I usally just GOOGLE it and sure enough I find an answer, fab!
Now I'm trying something a little more tricky and ...

**1**

vote

**1**answer

80 views

### Is there a way to do a partial text vlookup?

I am trying to vlookup partial text which will return a specific value if found within my data. I have a list of postcodes (zipcodes) and would like a vlookup to look up the first part of the postcode ...

**0**

votes

**1**answer

19 views

### how to get the count of column data based on some condition

I have the excel with two columns like below
ID Address
GID1 address1
GID2 address2
GID1 address1
GID8 address1
GID2 address8
so i want to get , for all same ID's , count ...

**0**

votes

**1**answer

139 views

### Sort & Ranking Functions in Excel for Time AND Number Values in one Column

I have a spreadsheet that is being used to track and score a fitness competition, each workout has a time cap, so athletes that complete the workout under the time cut off are given a time as their ...

**1**

vote

**1**answer

63 views

### Excell formula for multiple pasts without repeating previous cells

I have a data set for 12 years of 4,380 days. I want to take the sum of each week. when I make a formula {=SUM(A1:A7)} and drag to drop button to the next cell it shows {=SUM(A2:A8)}. This is not what ...

**-1**

votes

**2**answers

62 views

### From date of last event, calculate number of days until next regularly scheduled event

I need to calculate the number of days since last occurrence.
For example, I have to go to doctors every 90 days. My last doctor visit was on 1/9/14. I need a formula to tell me how many days until ...

**0**

votes

**1**answer

55 views

### Count Unique values based on criteria within 2 columns

I hope someone out there has the brains to help me with issue.
I have a spreadsheet that has multiple columns and duplicates. What i want to do is count all the unique values in column A, only if ...

**0**

votes

**1**answer

71 views

### Make formula impossible to find / read

I have developed a spreadsheet for performing various calculations which our employees will complete and then distribute to clients / competitors. To protect the "intellectual property" of the ...

**0**

votes

**2**answers

50 views

### Converting comma seperated values in multiple rows

I am in urgent need of importing huge data from excel to SQL.
one of the excel sheet contains data as follows.
ID Name Category Section
1 Books Stationery KG1,KG2,1
2 Pencils ...

**0**

votes

**0**answers

54 views

### Search rows containing a certain value for other values and comparing them

Here is what I'm trying to do:
This spreadsheet contains 40k+ component serial numbers. These assembly serial numbers are in column c.
These components contain sub assemblies and those also have ...

**0**

votes

**1**answer

21 views

### Formula to determine available options based on prerequisites

Example:
A spreadsheet for a single studient has four columns: Course|Completed?|Prerequisites|Available
The "Course" column lists the names of courses.
The "Completed?" column indicates a "yes" if ...

**0**

votes

**1**answer

62 views

### Excel - Find a specific column in a CSV file, and copy to the current sheet

I get a regular data export in a CSV format, which I like to pull into excel and report on via helper columns and pivot tables.
I do this by linking to the csv file, and copying the cell contents ...