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

learn more… | top users | synonyms (5)

0
votes
0answers
5 views

Find and pick smallest value on row based on the code in column so that no value can be picked twice based on column code [on hold]

I just joined here, this is my first post and I am a newbie when it comes to excel or vba. I have a problem: I have a product list in column (for example column A). On each row I have the ...
0
votes
0answers
8 views

Excel Formula Multiple If Statements with text value

I am trying to develop a closed product log. The hope is to have a product, say "Apples" or "oranges," be selected and then once it is also selected as a closed sale, it will apply a point value for ...
0
votes
3answers
39 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
19 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 ...
22
votes
8answers
339k views

How to compare two columns and find differences in excel

I have two columns in Excel that I want to compare and find the differences between them. Suppose Col A has 50 numbers i.e 0511234567 Col B has 100 numbers in the same format
0
votes
0answers
27 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 ...
0
votes
3answers
26 views

Excel multi table lookup, can you find a more elegant solution?

This is partly for fun, and I hope it doesn't get closed on subjectivity grounds because although "which solution is the most elegant" is subjective, "the current solution is convoluted and ugly" is ...
1
vote
2answers
15 views

Excel - copying links without forumla

I have a column of hyperlinks in an excel file. I've used formula: "=HYPERLINK(A2,A2)" and it gives me a hyperlink to a file on drive. In column A there are text versions of alll of the links - ...
2
votes
0answers
239 views

How to get new data to automatically slot into correct column in Excel?

I am trying to move new data into an existing database with set columns. The existing database has a column for every possible type of data, but not all new entries contain all columns. So I can't ...
0
votes
1answer
33 views

Count instances of letter or number in a single cell

I would like to count instances of numbers from a number in a cell. For expample we have the number 78812557839. I need a formula or function to count how many instances of 7,8,1,2,5,3,9 this long ...
1
vote
1answer
2k views

LINEST function in excel vba

I am trying to figure out the 2 order equation (Y = ax^2 + bx + c) for a set of data values. When I use the inbuilt function in excel =LINEST(C2533:C2537,B2533:B2537^{1,2}) I get the following ...
1
vote
2answers
34 views

Excel Spreadsheet sheet formulas

I would like to be able to have a cell in a 2nd tab equal what was in a previous tab, then create a new tab, copy and paste and have the spreadsheet formula update to the previous tab instead of ...
1
vote
2answers
22 views

Concatenate cells but exclude empties in Excel

I am wondering how I can concatenate a row of cells with comma separated values but exclude empty cells. For Example the list would be: A1 - Dog B1 - "empty cell" C1 - Cat I am looking for a ...
2
votes
1answer
20 views

Convert multiple unicode values to corresponding text in Excel spreadsheet

If my Unicode character contains more than one hex value in an Excel cell, how do I write a function in Excel to convert it into the corresponding character? For example: Unicode Hex values in Excel ...
7
votes
3answers
19k views

Using find() in Excel to remove text from string

I'm using find to remove some of the test string in lookup cell value For instance I look-up the following in cell A2 Application: sales_rep_support and use find =RIGHT(a2,FIND(" ",a2)+4) to change ...
0
votes
3answers
1k views

Excel: How to print unique pages with only one sheet

I am trying to create a template in MS Excel to print similar pages but with an unique ID within each printed pages (e.g. ID: 001, 002 and so forth) but printing from one sheet only. I am creating ...
1
vote
1answer
27 views

How do you create a conditional nesting formula on excel 2010 with more than 3 conditions?

I have "A, B, C and D." I need " if a>0, use a, otherwise, use b. If b has the error "#N/A", use c, otherwise use b. If c=0, use d/61, otherwise use c. So far, I have this: ...
1
vote
1answer
13 views

Cell references in formulas keep moving with old data in Excel

When I update Excel to the latest figures my formulas keep moving with the old data. I have already tried using absolute and or relative references like $A$1, $A1, A$1, and A1. I've also tried using ...
0
votes
2answers
21 views

Is there a way in Excel to have a formula stretch its row extent to include adjacent inserted rows?

In Microsoft Excel 2011 for Mac OS X, I have several columns of numerical figures and a final row at the bottom that shows a maximum value for each column. My data set is ordered, such that most ...
0
votes
2answers
5k views

Excel - how to either have direct data input or cell value from formula

I would like to either populate a cell value from direct key input or have the cell value derived from a formula. Example: either directly input a number into cell A4 or have the value for A4 = A2 + ...
-3
votes
1answer
46 views

Formula with 4 conditions (IF)

I have a hard time to figure out a formula with multiple conditions in Windows Office (Excel) If A1<=5 or A1>=5 Then number should be "2" If A1>=10 or A1>=-10 Then number should be ...
0
votes
1answer
28 views

Apply Names in Excel is bugged?

I see people had this problem 10 year ago and it's still not fixed. Or maybe I just don't know how to use this feature. In my sheet I have these named ranges: A1 - "name1" B1 - "name2" C1 - "name3" ...
0
votes
2answers
3k views

Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula. problem is the rota is written using a number of different notations for different shifts some are ...
1
vote
4answers
15k views

Calculating the calendar week in Excel

I am using the following formula to calculate the calendar week if referenced to a cell with date: =INT((A1-DATE(YEAR(A1);1;1)-WEEKDAY(A1))/7)+2 but it does not work properly. If I enter 24.08.2012 ...
0
votes
0answers
15 views

Determine a Scrum team's historical performance [closed]

I manage a Scrum team that calculates "Planned" versus "Actual" velocity each sprint (2-week timebox). Velocity is measured in points. For example, in sprint #1, the team may plan to deliver 50 ...
-1
votes
1answer
23 views

Excel CONCATENATE function produces error when referencing a worksheet with space in name

In Microsoft Office Excel 2013 on a Windows 8.1 PC, I get an error message, #NAME?, when trying to do the following: =CONCATENATE('Calculation Sheet'!D, "- Customer Sales") Doing the same with an ...
0
votes
1answer
14 views

Using ISNUMBER and SEARCH

I'm trying to use the formula =IF(ISNUMBER(SEARCH("180",A1,0),1,0) but for some reason it isn't working. I tried removing the quotes and the same problem occurred, I see entries (e.g. "it is 180 ...
-1
votes
3answers
47 views

Formula is not working, cannot find error in the formula

I'm sure that this has an incredibly simple answer but I've looked over this formula for hours and can't figure out the error. Essentially what I want the formula to do is if F3 = "Freshman" then ...
0
votes
0answers
11 views

How do I split a worksheet into multiple files dependent on the column values, calculate sub-totals and plot graphs?

I have a large data set and would like to split these into separate files/worksheets and perform the same analysis over each data set. I have found a macro to split the data into separate ...
0
votes
2answers
2k 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 ...
2
votes
1answer
27 views

Replace contents of Excel column with realtional data from another column/sheet?

I have two excel sheets that have been exported from MySQL, with relational data in them, and I need to replace the reference ID's with the actual data from the relative column. For example, in one ...
0
votes
1answer
26 views

Excel Office Pro 2010 SUMIF

Hi Im trying to SUMIF(range;"<>#N/A";range) but the cells im selecting to sum are not in a row adjacent, its every 4th cell in the row. But when i try it gives an argument error each time i try ...
0
votes
3answers
54 views

calculating end times with working hours and skipping weekends

In my worksheet, I want to calculate estimated end times of processes. However, i want to restrict this to a predetermined time constraint. So for example, when i add 4 hours to 14:00 I dont want the ...
3
votes
1answer
38 views

Excel, how to use text functions on lookup array without putting in another column?

If I'm using a vlookup, or a match function but I need to do some mumbo jumbo on my lookup material, is there a way of doing this in the lookup array without simply adding another column? Let's say I ...
0
votes
1answer
40 views

Excel - find multiple pieces of data in several different columns at once

I've currently got a spreadsheet on the go which lists contracts. I need to know if a contract is a renewal, new to product, or new to company. I'm currently struggling with the logic behind it and ...
0
votes
1answer
26 views

VBA highlighting a cell if corresponding range in the same row is not blank

I am trying to enforce food name in column B of a file if column D to DX (Qty) has any value in it. Rational is if any new request of food is received which doesnt appear in the file, the user can ...
-2
votes
1answer
32 views

Attendence Points Rollover

I have an attendance worksheet where we calculate employee attendance based on points, if employee absent they get 1 point and if tardy or did not complete shift get 1/2 points also if employee has ...
1
vote
2answers
20 views

Finding repeating digits at the end of a string of digits

I have a list of 10 digit toll free phone numbers. I would like to identify those that contain at least 3 repeating digits in the last 4 digits of the number. For example: 8xxxxx5444 8xxxxx2220
0
votes
0answers
19 views

Column order and names for table objects in Excel

I have a table in Excel that is connected to MS SQL Server table in the database. When I created a connection I specified a query that returns col1, col2, and col3 in this particular order. Then I can ...
0
votes
1answer
32 views

Functions (specifically text functions) within a countif function

Is there any way to nest text operators within a countif? I want to check if the first 4 characters of a file are in the form A**. =Countif(Left(A1,4),"A??.") doesn't work, but I can put in a ...
1
vote
1answer
50k views

Can Excel use formula-input data for cell references?

I have a cell in Excel, to which I want to pull data from another cell that is defined as a value in a third cell. For example: A1=5 A2=10 A3=insert syntax here, using A4 as source for cell ...
2
votes
1answer
13 views

Can you apply names in OpenOffice Calc the same way you can in Excel?

In Excel, you can apply names to a formula (semi)automatically by selecting the formula/formulae in question and then using the Apply Names function. This will change something like =B3+C9*D16 into ...
16
votes
5answers
29k views

Type math formulas in Microsoft Word the LaTeX way?

I wonder if there is some free solutions for typing math formulas the LaTeX way in Microsoft Word document (Office 2007)?
1
vote
0answers
29 views

Matching a column range based on a column label

I have written a spreadsheet for Excel 10 and am struggling with the last bit of formula. I wrote this: =SUMPRODUCT(--(ISNUMBER(FIND(B7,'PO SHEET'!$K$7:$K$74))),('PO SHEET'!$W$7:$W$74)) However, ...
1
vote
2answers
8k views

Excel compare two columns and list a third column

I have an Excel sheet which has 3 Columns: A B C D 1 A 1 2 B 3 3 C 4 6 5 D 7 E I want to check column C for values present in column A and list corresponding values of ...
0
votes
3answers
75 views

Excel Formula average without blank cells

I have a row of values and want to find the average of the first 3, not counting blank cells. Example: A B C D E F G 12 blank 8 blank 7 9 4 The average of the first 3 ...
-1
votes
1answer
27 views

How do I isolate certain characters in cell content of an excel to new cell?

How do I isolate certain characters in cell content of an excel to new cell Example: I have a big paragraph of information wrapped in a cell and I need to separate only few characters of that data ...
-1
votes
2answers
8k views

How do I split single cell contents into multiple columns without seperators?

I have an excel spreadsheet that has the following: Cell A1 ABCD 1234 I would like to split this to be CELL A1: ABCD CELL B1: A CELL C1: B CELL D1: C CELL E1: D I tried using the ...
0
votes
1answer
21 views

Run formula from different sheet

Can you run formulas from different sheets? (not simply showing the formula as a string/static text) Say I type =Sheet1!A1 where in that cell I have a formula =concatenate(ice&cream) ...
0
votes
1answer
28 views

Copy every nth column on a specific row from one sheet to another (in Google Sheets)

I want a function to select every 2nd column on a particular row and display it in another sheet. I have two sheets: Sheet1 and Sheet2 In Sheet1 A2 I have a list of numbers that start from 1 to ...