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

learn more… | top users | synonyms (5)

0
votes
1answer
9 views

Excel: Month() Formula Returning Wrong Value

I have a Sheet that has dates in Column B and Numbers Column X to be Averaged by Month in the formula I am using is as follows take for instance the month of February ...
3
votes
1answer
25 views

Extract content from the formula in a referenced cell in Excel 2007

I have a cell A3 that says Name. Cell F16 contains =A3, which displays Name, just like I want it to. I need cell F26 to take the information from cell F16 and display the referenced location. In ...
6
votes
4answers
174 views

Average numbers if another column contains a certain substring text in Excel

I'm looking for a way to average the numerical numbers in one column, say B, only if the string in column A contains a certain text. For example: (In column A) (In column B) Hello table . . . 1 ...
0
votes
0answers
6 views

Excel - Pieces of data from large chart sheet 1, auto sort on sheet2

I have a large chart and need to take small pieces of data (3 cells each for 14 rows) and have them stack ranked on the second sheet. I need A21:A34 (Branch Name), M21:M34 (% of Goal) and K21:K34 to ...
2
votes
1answer
16 views

Excel: Calculate IF column based on another IF column

I am trying to use Excel to create a scorecard for different companies to use. The product I create needs to be a finished product - when a company representative fills out certain information on ...
-2
votes
0answers
46 views

lookupvalue and return multiple values in Excel do not work

I need to pull through multiple unique IDs for expenses. i.e. one timesheet consists of 5 expense payments for one date, but each of these expenses for that day contain individual unique ID. I need to ...
-1
votes
0answers
23 views

Find first value above 0 in Column A and B then find the first value above 0 starting from the row where the previous value came from

I have column A and B which are filled with values, mostly zeroes. All other values are above 0. If a value is above 0 in column A it will always be 0 in column B and vice versa. I now want excel to ...
0
votes
1answer
31 views

Count rows matching criteria for every row in excel

Hi here is a sample spreadsheet: +--------+-------+-------------+-------------------+ | Animal | Count | Animal Type | Animal Type Count | +--------+-------+-------------+-------------------+ | cat ...
0
votes
3answers
22 views

In excel, how do I group duplicates according to whether or not they meet a certain condition?

I have a list of people with each row showing a person, and their usage of a platform in percentages. if a person uses more than 1 platform, they will appear in multiple rows, each for a different ...
0
votes
1answer
2k views

Changing cell values based on another cell

Lets say that I have 3 parameters/cells: High (Cell A1) Medium (Cell A2) Low (Cell A3) the fixed values for these cells are 2, 2 ,2 how can I change the value of cellA1 based on the changes of ...
0
votes
1answer
9 views

SharePoint Formula =IF(ISBLANK)

Is anyone able to tell me how to fix my formula. I have tried everything and I continue to get an error message. I am running out of options. I am trying to hide the SharePoint default date of ...
1
vote
1answer
11 views

Inserting formula through VBA in “A1” reference style

I insert this formula through VBA, Cells(1, 1).FormulaR1C1 = "=LEFT(A2;5)" But the formula which gets inserted ends up with apostrophes. =LEFT('A2';5) This works perfectly with the R1C1 ...
1
vote
1answer
9 views

Syntax for proper use of INDIRECT function using SUMIF in Openoffice spreadsheet to specify a functional range

Similar to others who have asked related questions, I have a spreadsheet sheet which summarizes different expense categories on a different sheet (called PC activity)in the same file. PC activity is ...
0
votes
1answer
31 views

Excel Formula with conditions

This feels like it should be simple but my head is hurting now and I'm struggling. I need to add a condition to an existing sheet which already contains formulas and references to other sheets. ...
-4
votes
3answers
127 views

How do I get Excel to look up customer names from another sheet?

I have two sheets in Excel i.e. sheet1 and sheet2. In sheet1's column A there are account numbers of the customer and in column B there are names of the customers. Now I want that in column A of ...
0
votes
0answers
11 views

Copy Blank Count

i want to copy the blank color count under Color column from "5-Jul-15" sheet and "6-Jul-15" sheet to ABC sheet under "5-Jul-15" and "6-Jul-15" column (i.e. C13 and E13) respectively. It should ...
0
votes
1answer
36 views

Searching for multiple sub-strings within a single parent-string

I've been searching all day for a similar question to mine. Many questions come close, but my questions is a bit different: I have a list of last names in Column A. In Cell B1, I have someone's ...
0
votes
2answers
3k views

Calculate different discount levels in Excel

I have to create a price list with 3 different discount levels called eg. E1, E2, E3. E1 = 10%, E2 = 20% and E3 =30% discount. How do I write a formula to tell Excel if the item falls in class E1 it ...
1
vote
3answers
6k views

Look up the next highest value in another column with Excel

I have two columns A and B. A B 20 100 40 200 60 300 80 400 140 500 190 600 240 700 User inputs a value in a cell. I need to check ...
0
votes
3answers
2k views

Excel VBA or Formula to convert text to Date with Time type

I have a quite a number of data where its date/timestamp has been imported into excel as text. Excel does not really recognize it as date with time. e.g. 25.08.2011 16:17:59 (mm.dd.yyyy hh:mm:ss) ...
0
votes
0answers
18 views

Is this possible to generate excel combo box based on cell value?

As you can see, Cell C2 got 1,2,3,4. I assume this is a student id. So, I would like to generate a combo box in Cell B8, using Cell C2's data, separated by ,. So, in this case, the combo box should ...
0
votes
1answer
2k views

Combine duplicates in Excel, sum some columns and weighted average the others

This question refers to an existing subjexct on our site. I need a variation on this solution. I have a spreadsheet with 8 data columns. The number of records can vary from a few hundred to about ...
1
vote
3answers
49 views

How can I automatically organize data from a text file in a spreadsheet?

I have a text file like this: and I would like to organize into Excel like this: I could do this by hand manually for two data entries, but I would like to be able to automate this for a larger ...
1
vote
3answers
54 views

Summarize tables of several sheets into one final Table Excel and include new sheets in summary

I have got several excel sheets, every single one of them containing a table for user information and one last sheet which contain the sum of all the previous tables. Since my table is very big, is ...
0
votes
1answer
46 views

Dynamic array/matrix generating within formula

The following formula: =TRIM(A1:A20) would make no sense; you typically should decide a column, write =TRIM(A1) on first cell and copy until =TRIM(A20) ... BUT ... suppose you want/need to use it ...
0
votes
2answers
195 views

Shortening an Excel formula if possible

I am unable to shorten an Excel formula I am using in an Excel database. I've googled it, but this appears to be already the shortest way from the first page on Google. ...
0
votes
2answers
24 views

Counting number of each item assigned to customer with unlabelled columns

I have a table in Excel as follows: 1 2 3 4 5 6 7 Customer1 Z U A C D A A Customer2 B D J S F D B Customer3 S A C A A B B Customer4 X H A C D A A ...
2
votes
0answers
38 views

Use Excel to match data from a cell on a worksheet, then copy data from its neighboring column and paste on a different worksheet

I have a spreadsheet with multiple names and phone numbers next to them in each Sheet 1 - Sheet 5. Each Sheet has about 900 names on column A and their associated phone numbers next to them on column ...
0
votes
1answer
28 views

How can I get an average of times in Excel?

I Have a column of cells in excel of format hh:mm (The day is NOT included) like: 19:08 19:08 19:40 20:10 1:45 (Any Times before 12:00 noon are considered NEXT DAY) one (or more of these) is after ...
0
votes
1answer
43 views

Excel Formula - Match

Can someone recommend a way to write a formula that will achieve the following. I can achieve these things individually but stringing together seems to be difficult. I have a list of orders with ...
0
votes
0answers
8 views

Multiple values of a employee in several rows how displayed in another sheet [on hold]

I have employee details in one sheet, and each employees' 30 days works (30 rows) in another sheet. How could I display employee name and his 30 days work in another sheet.
4
votes
2answers
369 views

How to count if there's a cell with certain restrictions using Vlookup?

I'm finding if there at least one cell with the first character equal to "!" Formula I have used without success: =Vlookup("!";Left(N2:N49;1);1) Why doesn't it work? And what do I have to change ...
-1
votes
1answer
37 views

excel extracting data and concatenating fields? [on hold]

I have a large excel sheet which I want to extract some certain fields. Here is an example: Column 1 has a list of countries: Country: USA | UK | India | GER Column 2 has a list of route IDs: ...
0
votes
1answer
24 views

Pulling data from multiple spreadsheets [on hold]

Need to run a report pulling data from 10 time tracking spreadsheets. In column C, if the job type = Artwork, then I need to pull in the data from columns A, B, D & I of the same row. There may be ...
2
votes
1answer
85 views

Is it possible to reference two cells in Excel to find a third cell?

I have a spreadsheet that has a decent amount of data on it. I need to return some of that data to certain cells. The data that I need to return is always near a cell with "Attached Components" in it. ...
0
votes
1answer
26 views

True and False 2 sheets

I have to input a value in column I in sheet 1 from excel and in the column "D" from sheet 2 it needs to appear true or false. If there's any value in I than it should be true in "D" otherwise ...
2
votes
2answers
4k 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 ...
0
votes
1answer
35 views

Excel, pair 2 columns, then find all rows where column J is in two different Column D

SO I would like to have a formula or macro, to look at a table in Excel, pair two columns say J and M. Then find rows where M is connected to more then one value of Column J, Column J is Super Group, ...
0
votes
1answer
64 views

Count based on multiple alternate conditions in Excel

I need a formula to count any trailers that are due in but do not have a cell that says "yard" or have a date next to that trailer. Please see screenshot. I tried the COUNTIFS formula and was ...
0
votes
1answer
24 views

In excel how could I generate a row of data from another sheet?

In excel I have a sheet named "sales", and another one named "factor". In factor I have some fields for factor ID, customer name, product code, quantities and price. I want when I add this data to ...
1
vote
1answer
21 views

Excel - pivot date values with max function

I am trying to pivot date values in a pivot table in MS Excel. I am getting incorrect date values. I do not want "Grand Total" as they do not apply for dates. Is it possible to do such pivots in ...
1
vote
2answers
29 views

How do I use IF in MS Excel-Mac 2011 for 3 variables?

When I type A, B or 0 in one cell, I want to see in other two cells (separately) to show 2,1 if I type A 1,2 if I type B 0,0 if I type 0 Can someone help me how to do this? I currently only have ...
5
votes
2answers
4k views

How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?

In a Google Docs spreadsheet, I can use this cell formula: =GoogleFinance("GOOG", "price") to download the latest price of a stock. Is there an equivalent function in Excel 2013? Earlier versions ...
0
votes
3answers
43 views

Excel Formula issue

I've been trying to write a formula to help me with rostering. It seems to work if I put the first few parts in, but after that it says "The formula you typed contains an error." Can anyone see the ...
1
vote
1answer
32k views

Need a formula that looks at the first digit or letter in a cell

I have a column of numbers. Some begin with the digit 8 and the rest begin with other digits. I want to write a formula that will return "2" if the number in column A starts with 8, and "1" if the ...
0
votes
3answers
69 views

How to keep a running balance of payments vs. billings in Excel

I have a row of cells with 12 Monthly columns, G4 (January) through R4 (December), in which I will input the amount paid towards a monthly billing. [Note that an early comment discusses multiple years ...
1
vote
1answer
29 views

How to create a text mask in Excel 2007

I have a list of numbers: 59046 64940 77505 101409 140331 1036135 3166909 I want every number to be exectly 9 digits. For example, i want 59046 as 000059046, 1036135 as 001036135. How can i create a ...
2
votes
1answer
36 views

How to make a formula-based cell read-only in MS Excel?

I'm trying to create a unique ID for each row in Excel, based on the formula [RowNum + X] (X is given). The formula autocompletes on each new row, based on Excel Tables. After creating the new row, I ...
1
vote
1answer
22 views

Checking column for value. If true, check same row in another column and count if not blank

I have a spreadsheet of employees. Column A is a list of Dept types (sales, HR, payroll, etc). Column B is the date their appraisal is completed, or blank if incomplete. I would like a function that ...
0
votes
1answer
19 views

Deleting all cells in row after blank space

I was just wondering if there is a simple way to, for each row, move right and check if there's a blank cell and if so delete everything to the right of it. Can this be done with a formula?