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

learn more… | top users | synonyms (3)

0
votes
1answer
7 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
2answers
29 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. ...
2
votes
1answer
34 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
1answer
24 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
0answers
11 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
1answer
42 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
2answers
24 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 ...
0
votes
1answer
24 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
1answer
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
1answer
32 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
1answer
28 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
0answers
10 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
1answer
21 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
15 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
35 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
27 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
23 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
19 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
116 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
26 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
33 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 ...
0
votes
0answers
18 views

link excel sheet to word doc and be able to change multiple cell values at one time [closed]

I would like step by step instructions on how to link excel sheet to word doc and be able to change multiple cell values at one time. I have linked up several tables but then if I move to another ...
2
votes
1answer
19 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
32 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
30 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
14 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
13 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
27 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
49 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
38 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
26 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
37 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
41 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
23 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
32 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
35 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
31 views

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

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
53 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
57 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
49 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
25 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
25 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
32 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
49 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
27 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, ...