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

learn more… | top users | synonyms (5)

1
vote
3answers
117 views

Writing excel formulae that reference columns by number rather than letter

Is it possible, and if so what is the syntax, to write formulae in Excel using the column's integer number rather than the lettering system that is the default? I know it is possible to change the ...
-3
votes
3answers
573 views

Need a formula for Leap Year

I need a formula in which, if the date is equal to or greater than 03/01/2015 and less than or equal to 02/29/2016 then I get the result as "Leap Year" or "No Leap Year". Thanks! Sumeet
1
vote
1answer
147 views

Getting a webpage title into Excel

I'm attempting to create a cell that will pull in a hyperlink based off of what is filled out in another excel cell. I have the hyperlink portion working, but I'd like to have a better label for the ...
1
vote
2answers
127 views

DATEVALUE() gives different values for similar data

In one column, I have timestamps in the format Jun 10, 2015 11:36:51 AM So, to get the date from that column, I created another column and used the formula =DATEVALUE(CONCATENATE(MID(D2,5,2)," ...
2
votes
1answer
45 views

Not getting desired output with VLOOKUP - Need assistance

I have Table 1, which looks like this - A1 B1 C1 1 X Date11 2 Y Date12 3 Z Date13 I have another table, Table 2, that looks like this - A2 B2 C2 1 M Date21 2 N Date22 3 X ...
1
vote
1answer
130 views

Excel - how do I retrieve corresponding values from another worksheet?

I have a "DETAIL" worksheet which tallies numbers: DETAIL WORKSHEET (Column A) (Column E) (Excel Row #) DATE TOTAL ... 700 6/8/2015 =sum(D701:D709) 701 702 703 ...
0
votes
0answers
14 views

Matching data between two separate worksheets and transposing the data from another column next to the matched data

I have two worksheets. I am trying to input the stock number next to the matched claim number. How would I go about doing this? The first sheet is my inventory list. The second is a list we get from ...
1
vote
1answer
473 views

How to hide formula results in a cell until conditions are met - percentage discount [duplicate]

My question relates to this post, but with different formula criteria: How to hide formula results until conditions are met. Here's the setup for a spreadsheet to calculate the percentage discount ...
1
vote
2answers
85 views

Is there an excel function which displays a string but outputs a value when referenced?

Let's say if the function called "LABEL". Then in cell A1 I would enter =LABEL(4,"Four Apples") and it would render as: Four Apples But if I performed a calculation, say =A1+2 I would get 6, not ...
-2
votes
1answer
84 views

Need to sum values associated with specific words, in Excel [duplicate]

I want to sum values associated with specific words. For example, calculate Sum for Food: Column A Column B Food $8.50 Food $5.10 Maintenance $12.00 Laundry ...
0
votes
0answers
35 views

Microsoft Excel 2011 concatenate each row in a cell

I am trying to use an if function for each row for a result in a single/destination cell. The if function checks if a row meets a criteria, if it does, it adds the row details to the destination cell ...
0
votes
1answer
60 views

VBA Code Isn't “holding” ThisWorksheet Parameter

I want to retrieve one column of values from another workbook and populate a tab in my current workbook. This will ultimately go into a much longer module I've written that works great. I just wanted ...
0
votes
1answer
115 views

Excel 2013 - Referencing multiple checkboxes on a different sheet

Good morning, Superuser! I am facing a pretty tough problem at work this morning...my supervisor wants me to do something I have never done before, nor can I find a solution online. The spreadsheet ...
0
votes
1answer
50 views

What formula would you use to add up a column based on results from two drop down menus? [duplicate]

I would like to calculate a column based on selections from two other columns with drop down menus. For example : In one column, "C" you have 3 options (A team,B team,C team), In column "F" you ...
0
votes
1answer
63 views

Want to get the unique combination of data for dynamic number of rows/columns

I have a few rows (the number of rows should be dynamic) of data that I would like to combine, and get each unique combination. I am able to do that for a specific number of rows of data, but when the ...
0
votes
1answer
242 views

Excel not adding decimal numbers correctly

If I have a cell (A1) with a constant of -1.1 and then 11 subsequent cells referring to the first A1, and then to each previous one consecutively, after 11 cells, I'm expecting an answer of 0. ...
1
vote
1answer
68 views

Excel SUMIFS based on two criteria

I would like to total a list of minutes based on two criteria to make a graph after the data is calculated. The item "Department" (Column C) and "Day of Week" (Column F) are both drop down menus. I ...
0
votes
1answer
31 views

Count two different text items in a row or column

I have a table (schedule) that I have a COUNT set up to count the number of times a text occurs. My table: A | B | C | D | E | F | G | H John | Rooms | RoomsPM | OFF ...
1
vote
2answers
266 views

Excel formula daily average of weight

I am looking to set up an excel spreadsheet to calculate cattle weight gain per day. We weigh our cattle once a week so column A will be a date and Column B will be the weight on that day(day of the ...
0
votes
1answer
107 views

Excel Run Length Encoding

If I have a column of data in Excel that looks like: 1 1 1 0 1 0 0 1 1 Is there an easy way to convert it into a run length list that looks like this instead: 3 1 1 0 1 1 2 0 2 1 (Three 1's, ...
0
votes
1answer
67 views

Finding Common and uncommon values in excel 2010 [closed]

I have some values/numbers from A2 thru F8, I need the common values in 1 column (i.e the values which are common in A,B,C,D,E & F)-THIS CAN BE IN COLUMN H I also need values uncommon only in A, ...
2
votes
1answer
234 views

Excel formula sum multiple rows/column once on cell has reached it's max number and continue to sum

I am trying to come up with a formula that will split three rows of numbers evenly until one cell reaches a max number and then continue to sum the same three rows but split all the new values in two ...
0
votes
1answer
22 views

I want to take two columns of cells (not the whole columns) and copy them and sort them into different cells

My Table: I want to copy the names and scores from the desirability score area into the "Most to least desired rooms area but place them in descending order. How do I do this? Edit: I want the ...
0
votes
2answers
567 views

How can I return the second last text value from a row?

Could anyone tell me the Excel formula to return the second last text value in a row? I managed to find this formula for last text value: =INDEX(U2:Y2,MATCH(REPT("z",255),U2:Y2)), but not sure how I ...
1
vote
1answer
2k views

Find the previous Row with a specific value at in column A

Given the above sample sheet, which has semantic 'header', 'subheader' and 'subtotal' rows, I am trying to determine a formula to locate the previous subheader row relative the current cell. For ...
0
votes
1answer
18 views

Find missing values on spreadsheet 1 when compared to spreadsheet 2

I have the following scenario: I have a list of ID's on a spreadsheet generated from a web app that has to be compared to our internal system. Any missing values from SP1 have to be alerted since ...
0
votes
1answer
36 views

MS Excel UDF - Time series difference

I'd like some help with manipulating some data please. Given: i) A range "oRng" of say "MaxR" rows and "MaxC" columns; ii) and an integer i such that 1 I'd like to figure out the easiest way to ...
2
votes
1answer
283 views

Excel, Setting a cap for values that meet a criteria on a column

I'm having trouble finding a way to limit the values of a column by setting a cap, but only for some values meeting a criteria. An example of what I have: >Criteria | Value >--------- + ...
2
votes
2answers
120 views

Excel in Cell Function VALUE for Formulas

I have an excel sheet which I need to run on Android. Only problem, I used VBA for the Evaluate Function and VBA does not work on Android Apps. I was searching for an alternative and came a round the ...
2
votes
1answer
71 views

Need to add newline(s) and space(s) inside a cell in Excel

I have data in an Excel cell which is delimited by ,. For example: abc,bde,fgh. I used the formula =SUBSTITUTE(A2, ",", CHAR(10) & CHAR(13)) to change the commas to line breaks. Therefore, the ...
0
votes
1answer
692 views

Excel countif formula that will subtract from another number if cell contains certain values

I am dealing with job applications for 8 different sites. People can apply for any combination of sites i.e 1 of them, 2 of them, all of them. Some applicants have specifically said they don't want ...
1
vote
1answer
38 views

Summing up values from a user-defined range

I have a spreadsheet from January 1 to present. and I needed to sum up the value parallel corresponding from the range which the user defines. For example: in Column A are the dates January 1 - ...
0
votes
2answers
214 views

How to combine values from multiple rows into a single row with a comma separator

I have an Excel file that lists product SKUs in one culumn and product image URLs in a another column. My problem is that the file has multiple rows for the same product... ...when the CMS I use on ...
1
vote
2answers
139 views

Counter in Excel

My file has 50 sheet inside, which each sheet is a standard form. Lets name as Form1, Form2, Form3. I want to create a print sheet, where I select which form I want to see, so that I don't need to ...
7
votes
2answers
872 views

Conditional concatenate cell content across rows

I have a worksheet with thousands of rows which contain a unique ID A, and one 3 different error codes M. Each individual ID may have 1, 2 or 3 errors. They will always be in the same order. I have ...
0
votes
1answer
35 views

Excel Formula Is unwantedly automatically changing

I have created a product referral log and needed to have a system that automatically calculates points for those products once they are closed. My solution for this was to make a separate sheet ...
1
vote
2answers
408 views

How to find value of last cell in an Excel row

I have a spreadsheet with a delimited list of directories and files in rows with varying numbers of columns. I just want the filename value (e.g. 123-E-001.xls, situated in the last cell of each row) ...
2
votes
1answer
21 views

generate line graph from spreadsheet with month and category column?

Trying to generate a line graph that shows the amount a certain category happened per month. The spreadsheet has the category of an instance as well as the month it occurred. Easy way to do this? ...
0
votes
1answer
33 views

in ms excel, is there a way to look up an array and edit data on the looked up row?

Just for a quick example, I have this table: A B 1 student grade 2 john 90 3 paul 93 4 peter 87 5 bill 84 6 7 Range or array is A2:B5. I ...
0
votes
1answer
115 views

Excel - Look up list of substrings within large strings

I've been struggling with the following; might be a bit of a challenge. I have an Excel file that was exported from an XML application. Sheet 1 contains rows of strings like those below. Every ...
1
vote
1answer
140 views

What exactly is this SUMPRODUCT and MID formula doing?

In an Excel spreadsheet I have text entries H23, w67, P45, etc. and I want to sum the numeric parts of these, i.e., 23,67,45. I have the formula ...
0
votes
1answer
56 views

Read one worksheet and load data into another one but not just as a reference [closed]

I have a list of equipment assets and attributes which is a raw data dump from a database. Among attributes like equipment status (like running, in standby etc.) or Oil level it also includes things ...
0
votes
1answer
55 views

Microsoft Excel 2010 if(or(and I am confused

I am trying to query two cells by using If function but unfortunately have no luck. I will give you an example below. Please let me know how I can solve this issue. If C2="Car" and D2="Toyota_Mazda" ...
1
vote
2answers
109 views

How to populate a cell with values from another worksheet but increment cell address by 3?

I know how to populate a cell with values from another worksheet. But how do I skip to the 3rd cell? Here's what I am trying to do: A1 ='2nd Worksheet'!C4 A2 ='2nd Worksheet'!C7 A3 ='2nd ...
0
votes
2answers
70 views

Keep same row when new rows are inserted

I have a CountA formula: =COUNTA('0 Data Summary'!B10:B108) and a macro that creates a new client data row in row 10 every time and all other client data shifts down by one row. It works fine but, ...
0
votes
1answer
710 views

How do I stop the Now() function from updating everytime I open the file?

I need it to automatically grab the value of current time at a particular moment when the user does a specific action (like pressing a button maybe). The thing is, I need it to keep that value of ...
1
vote
3answers
177 views

Subtract values only if all are numbers

I want to subtract four cells in excel from one specific cell but only if any of the four cells have a value. More specifically in my case the formula in cell L2 is currently ...
0
votes
3answers
174 views

How do I break up a number in a single cell that doesnt have commas and put it into multiple rows?

So here is what I am looking to do. I have a spreadsheet with a cell that has a string of numbers in it (some are longer than others) I need to extract every eight numbers and put them into single ...
-1
votes
1answer
64 views

Array formulas and Logic, checking against multiple criteria

I'm currently just troubleshooting a report spreadsheet I've written. The logic I had written isn't working, so I thought I'd see if I could ask for some help here. Essentially, I have a list of ...
0
votes
0answers
225 views

Average If function not returning correct value

I am trying to do an average if function, it is the average of some cells which provide a number based on a formula in that cell subtracting dates in other cells giving a number representing a span of ...