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

learn more… | top users | synonyms (5)

0
votes
0answers
4 views

Excel - Sum assigned numeric value of 1 to different phrases in different cells in a row

I'm assessing a survey of multiple choice answers using Excel. Need to assign a numeric value of 1 to fixed text answers and show the sum on another worksheet in the same workbook. Incorrect answers ...
1
vote
1answer
14 views

Aggregate a range of worksheets with errors

I have several worksheet in Microsoft Excel and I am currently adding them like this: =SUM(MySheet1:MySheetN!A2) This funtion adds the content of all A2 cells on all the worksheets contained ...
2
votes
1answer
18 views

Adding values among text in an Excel cell

I have an Excel sheet with 7895 lines, 6 columns in column B I have values like: “Classification 124: Item 1 (€2345,70) item 2 (€123) item 3 (€1456,75)” What I am trying to do is create a formula or ...
0
votes
0answers
10 views

Formula code in Word 2016 [mac]

I'm currently making an invoice template in word, but I'm fighting with the formula code which is used. Currently this is my table: But, the number of rows wil depend on the days which I've ...
0
votes
1answer
8 views

Excel: How to obtain depured colum B after deleting matching values from column A

So I have in Column A a Blacklist of sites that i can not use any more In column B i have listed all of my current sites (more than 5000 rows). So i need to look if any of the values of column A is ...
0
votes
1answer
22 views

EXCEL SUMIFS with dynamic tabs (INDIRECT)

I'm trying to get below SUMIFS formula to work with INDIRECT. I have data for each week in separate tabs .. ie WEEK05, WEEK06 etc. The result of the formula (a date) will come from column W based on ...
0
votes
0answers
12 views

Cleaning up data fetched from Google Spreadsheet to Excel

I've been trying to synchronize and build a local database with Excel data. I need to build the database and program to create user accounts, manage the packages, track and log the status of the ...
1
vote
0answers
26 views

Calculating partial values for use in a weighted average

I have a spreadsheet with the following information: Column A contains a list of courses (eg Course 1, Course 2, etc) Column B contains the number of credits available to each course Column C ...
2
votes
1answer
16 views

Index-Match returning value in cell above desired value

I have a couple index matches going on in a workbook I'm playing with. I basically have a huge lists of addresses in a table and I'm trying to pull specific ones to get mailing codes. I index match ...
0
votes
1answer
22 views

Selecting a value based on a lookup value that fits between two values

I have a Worksheet in which I have two "tables" looking like this: What I'm trying to achieve is to do the following: Take the first two digit in Column D (for the first row 77) See which row in ...
2
votes
2answers
22 views

if indexmatch = #N/A keep original cell value

I have 2 worksheets in excel. Sheet 1 is a price list given to us by a supplier Sheet 2 is a list of all the products we supply (can be from multiple suppliers). In the first column of each Sheet is ...
0
votes
0answers
18 views

Ms excel while entering more than 15 digit no [duplicate]

I want to add more than 20 digits in an Excel cell. The current format of the cell is general, it converts the number to an exponential format. I tried with a number format and accounting, but when I ...
1
vote
1answer
38 views

Cannot Use Autosum on cells that Contain Formulas

I have cells that have IF formulas in them. Excel won't let me autosum the results of those. It's as if Excel does not recognize those cells as numbers. Granted, numbers do show, but in actuality the ...
0
votes
1answer
23 views

How to display unique numerical values from different columns into a single cell (separated by commas)

I have a unique problem I have been trying to tackle with no avail. I have scoured through the internet but only found partial solutions and not a complete solution. I am looking for a function (or ...
0
votes
1answer
20 views

How can I count unique comma-separated values - Early Bound vs. Late Bound

I am trying to display unique values from a string of values in a single cell.(separated by a comma). I've come across this solution: How can I count unique comma-separated values in Excel 2010 ...
3
votes
1answer
33 views

How can I look up the 2 axis values by inputting a number?

I'm a graphic designer and not very familiar with Excel but I'm fairly sure it's the simplest solution to my problem. I have a table (see below) with 2 axis going from A to L. Inside that table are a ...
1
vote
2answers
39 views

Microsoft excel - formula dragging

How can I drag a formula to all the adjacent columns with similar intervals in it? For example: in column F, the formula is ('Time sheets'!G10-'Time sheets'!F10)*24 and in column G, it's ('Time ...
3
votes
1answer
25 views

Excel formulas - how to make a cell blank down the page

I am having trouble figuring out the formula for my budget. Cells look like this A B C D DATE WEEKLY BILLS TOTAL 2015 $50 $1,956.73 Jan ...
2
votes
1answer
26 views

Cells evaluated by user-defined module do not update automatically

I read the questions existing but no answer seemed to help. I have a function I wrote in VB which evaluates sum of cells in a certain range by font color, it all works well when I type the function in ...
1
vote
1answer
25 views

How to grep summarized value from excel cell range?

I want to get summarize information from the following sheet. I don't know how to summarize this information. My scenario is like this. If I input -6, I want to find my target value in this ...
-1
votes
0answers
23 views

How can I automate the process of printing 2 rows of data from one sheet after pasting it onto the form on another sheet?

Sheet 1 of my spreadsheet has the following form: A B C D They are just columns named 'A','B','C' and 'D'. Sheet 2 has the following data: A 1 2 3 4 5 6 7 8 9 10 I want to print pages of ...
0
votes
1answer
22 views

Clamping values while averaging

I have two formulas that are calculated using the same dataset. One formula calculates the average of the values, while the other calculates an average with a maximum allowed value of 60. I'm ...
0
votes
2answers
36 views

Data validation in Excel- all values in comma-delimited list within cell range

I have a reference table of memos and some information about them, including what other memos they reference and are referenced by. I want to make a data validation rule that will appear red if any of ...
4
votes
2answers
340 views

Generate Combinations in Excel

I have an input row which contains the cells: 30 30 30 50 50 60 100 I want to generate all possible combinations of length 3 from this set. So I would want rows containing 3 populated cells and ...
2
votes
3answers
25 views

Excel SUMPRODUCT - exclude header cells from formula

I have two columns, one with a date and one with values, and I want to sum up all cells, where the year of the date is the desired year. My problem is, that I have headers in the table, and I don't ...
-1
votes
1answer
22 views

EXCEL: Can I get a count of a certain value from columns with certain headings, only from the columns with certain text?

A screenshot of the spreadsheet1 Hi I am looking for a way (in the form of an Excel formula) that can do this I need to count the number of "o" in columns with headings "Threat", for each row. For ...
-1
votes
0answers
40 views

Excel Formula to show All dates in a range after today

I am trying to build a spreadsheet that I am using the lookup feature to take data entered on sheet 1 to locate that data on sheet 3, I need it to then list all Dates after today for the current ...
0
votes
0answers
10 views

Auto-Fill data between two spreadsheets, adding new row, keeping same column, when new val

So I have a list of items that need to be constantly added to a list for auditing purposes. Problem is, the item names constantly change and also on occasion, new items are added. Essentially, I am ...
0
votes
0answers
12 views

Excel save relative formula for re-use

I need to go through a sheet, filtering on a series of fields and running a formula against the resulting columns. However, typing the formula each time is slow and annoying. How can I save the ...
0
votes
2answers
11 views

Excel filtering all cells in column instead of those filtered

I am trying to add column value in a sheet with a filter applied. The problem I have is that Excel is adding values for rows that have been filtered out rather than just those the filter is showing. ...
0
votes
1answer
32 views

How does this SUMPRODUCT(COUNTIF( )) function work?

I was searching for a solution to count distinct values in a range, and came across this article. The example provides a list of ten names comprised of four unique entries. It then uses the ...
-1
votes
1answer
65 views

How do I reference another cell based on the value of another cell?

No one understood my question which is understood due to my bad explanation. I'll put an example to avoid further confusion. In Sheet2 I want to change A2's formula then it will fill B2,C2, and ...
1
vote
2answers
31 views

How to use OR in a COUNTIF function when dealing with dates and “>” characters

I want to write a function whereby I could count a cell if it is blank OR greater than a date. This is a piece in what will be a much larger formula so looking to avoid a solution of ...
-1
votes
1answer
61 views

Formatting any cell referenced in a Formula [on hold]

I have two sheets, one with formulas pulling data from the second. I need to create a macro or some conditional format to highlight the cell green if the data was pulled from the second sheet. sheet ...
0
votes
0answers
41 views

multiple IF conditions and answers

This is a cashflow spreadsheet. I want the column next to the expenses column to show me the source of the expense. The expenses are arranged in spreadsheets, so for example; checks, taxes, general ...
-1
votes
2answers
23 views

In a table of groups and members, how to reduce a certain percentage of members per group?

I have an Excel spreadsheet with a table of groups and members. For example: A Jonathan A Roger A Donald B John B Lilya C Richard C Lorry What I want to do is to reduce the number of members per ...
-1
votes
1answer
30 views

Formula to return “N/A” when other cell value is “No”

How do I get one cell to come up with "N/A" when "No" is selected from another cell's drop down?
0
votes
2answers
27 views

Adding components of formula (net vs. gross)

I would like to add the components of a formula across a range. We have one section that shows the net cash flows for specific items, and multiple specific items. For example =8+9-5 shows "12" ...
0
votes
1answer
19 views

Why is the MATCH function is not resolving my lookup array?

In Excel 2010, I am using two Excel tables. One table (ActiveDS) has a single column and contains a list of our active data sources. The other table (ApproverRole) has a number of columns, including a ...
1
vote
0answers
26 views

Excel - How to link Index/Matching formula with Indirect formula for dynamic column reference

I have a worksheet in which I am looking to utilize the Index/Matching formula to pull in exact values for the unique intersection of Department & Account values on a separate data sheet. However ...
-2
votes
2answers
36 views

I want to change excel [closed]

My cell looks like this 3s4446V6PK1HVGT, I want to change it to look like this, PK1HVGT after the formula is done. The pretext is different for all cells but the outcome will be 7 characters long ...
3
votes
2answers
34 views

How can I how extract/parse variable info from the END of a string in Excel?

Example1: 01/28/14 Interest Earned 0.10 Example2: 12/27/13 CHECKCARD 1226 LIONHEART BOOKSTORE SEATTLE WA -25.13 In the two examples provided, my goal is to separate out the information ...
1
vote
2answers
86 views

Enter time without colons (hhmmss) and calculate time differences in Excel

I want to enter the time without colons as hhmmss and calculate the time differences with another cell. For example I enter the beginning time in one cell and the ending time in another. Then I need ...
1
vote
1answer
34 views

Find the last data in a table with conditions [duplicate]

I want to get the latest END BALANCE of customer, when the STATUS is success. Picture of the table and result wanted is attached above.
0
votes
1answer
29 views

How to synchronize text in Excel in multiple sheets

I have Excel file with four sheets. How to make duplication using Excel built-in function using specific parameters? Example: Sheet 1 - Total: A B C 1 Iveco ...
0
votes
1answer
19 views

Excel TODAY() function

I have two tables in excel The first table is called Oranges, it shows where the oranges were produced and where they were exported to: The second table is supposed to show the total oranges ...
2
votes
2answers
48 views

Creating an Invoice in Excel

I have only dabbled with Excel and not much in quite some time now. I have an Excel Invoice laid out so Column A is a name of a job and each row in column A can be a different job. Now column B ...
-2
votes
1answer
30 views

# of days in a month based and start and end dates

This question relates to Calculate Total Number Of Days Per Specific Month. If you look at the original question what IF you have a start and end date Jan - March and you need to know how many days ...
0
votes
0answers
28 views

Excel formulas linking sheets in workbook

I am looking to automatically update a results sheet by moving down by one row every time I input a set of data in another linked sheet (raw data) in the same workbook. I will be overwriting the Raw ...
0
votes
0answers
15 views

Aggregate values by month in Excel [duplicate]

For each row, I need to aggregate values by month. Date Jun Jun Mar Jun Name1 10 11 14 12 Name2 2 34 4 7 Need Result Date Jun Mar Name1 33 14 Name2 43 4