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

learn more… | top users | synonyms (5)

0
votes
0answers
27 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
1answer
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
2answers
61 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
4answers
54 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
1answer
46 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
1answer
33 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
2answers
50 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
3answers
47 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
1answer
34 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
2answers
37 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
2answers
45 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
2answers
171 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
1answer
92 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
1answer
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
1answer
32 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
1answer
131 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
1answer
66 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
1answer
31 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
2answers
33 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
1answer
31 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
4answers
197 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
1answer
63 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
2answers
52 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
1answer
47 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
0answers
37 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
2answers
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
1answer
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
1answer
67 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
1answer
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
1answer
27 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
2answers
45 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
2answers
34 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
1answer
29 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
1answer
324 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
1answer
23 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
1answer
30 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
1answer
47 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
1answer
49 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
1answer
18 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
1answer
98 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
1answer
51 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
2answers
51 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
1answer
48 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
1answer
64 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
2answers
49 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
0answers
53 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
1answer
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
1answer
52 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 ...
0
votes
0answers
24 views

In Excel 2013 how can you make every distinct number value have a matching distinct colour?

I have a table of computed numbers in excel (different every time it is computed), for example: A B C D E 1. 9 8 9 9 1 2. 7 1 1 1 9 3. 1 6 5 2 9 4. 6 6 3 2 2 5. 3 3 2 3 7 In the example I have ...
0
votes
0answers
25 views

I need to import data to an excel sheet

I originally went through a spreadsheet and created a new column called regions next to an existing column that had suburb data in there. It took me ages as there was 8000 rows of data. Anyway its ...