Tagged Questions

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

learn more… | top users | synonyms (5)

0
votes
1answer
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
1answer
18 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
2answers
37 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
1answer
28 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
1answer
15 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
1answer
26 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
2answers
21 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
2answers
118 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
1answer
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
1answer
32 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
1answer
38 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 ...
2
votes
1answer
24 views

Excel Formula…Complex…Invoice tracking without inputting info into multiple spreadsheets

I have a list of store# in one worksheet(1) and another worksheet which has (2)invoice tracking for these stores. As the store# & invoice # are added to one worksheet(2), I want the invoice number ...
2
votes
2answers
36 views

How can I get information from one cell in a row, based on the clue of 2 other cells in the same row?

I'm trying to get Excel to give me information in Column C based on the values in Columns A and B. Cell A and Cell B will contain duplicates, and what I'm trying to do is build a formula which finds ...
1
vote
1answer
32 views

Excel formulas: Dates A, B, C and D. How many days between A and B also fall between C and D

Example: A1 = 8/5/2004 B1 = 29/12/2004 C1 = 1/1/2003 D1 = 21/11/2004 I want to work out how many days between A and B also fall between C and D. I can't get my head round what the formula to work ...
0
votes
1answer
21 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
2answers
19 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
0answers
17 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
28 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
30 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
50 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
42 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
30 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
39 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
44 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
28 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
35 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
39 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 ...
-1
votes
0answers
36 views

In need of a formula in excel to pick a day based on it's availability. [on hold]

So what I have is a spreadsheet that ranks a group of technicians from 1 to 23. And I have a list of available days off and a list of techs and there preferences for those days off. What I want is a ...
2
votes
2answers
67 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 ...
-3
votes
0answers
61 views

I want to compare two different columns and get a value from another workbook using VLOOKUP

I want a value from the workbook(2) by comparing two different columns from workbook(1) using VLOOKUP. I want a VLOOKUP coding like, if WorkBook(1)columns A matches with workbook(2) columns A then it ...
-2
votes
1answer
53 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
26 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
29 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 ...
-1
votes
1answer
33 views

Excel Average by excluding top and bottom 5 values

Trying to get the average by looking up a text and excluding the top and bottom 5 dates based on worst and best values. I am currently using this formula which gets me the all but excluding the top ...
0
votes
1answer
54 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
29 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
26 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
25 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
107 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
54 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
47 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
42 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
23 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
31 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
21 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
32 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
11 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
20 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
37 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 ...