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

learn more… | top users | synonyms (5)

0
votes
1answer
24 views

Record multiple calculation results at each change of single input cell

I have a spreadsheet that calculates reduced generation and emissions per fuel source for the state of Wisconsin at different levels of solar photovoltaic capacity in the state, as a percent of the ...
0
votes
1answer
15 views

Excel Formulas won't work

I'm trying to combine =IF('Attendance Tracker'!B52=0,"Y",IF('Attendance Tracker'!B52=1,"T",IF('Attendance Tracker'!B52>=2,"N"))) and =IF('Attendance ...
0
votes
1answer
36 views

Excel Spreadsheet - Distributing Master Sheet rows and deleteing duplicates

I have a master sheet that I have the row distributing to different sheets with specific labels. I want to have each row distribute to the appropriate tab (from the Master) dependent on the ...
0
votes
1answer
50 views

How can I use three conditions in an IF formula? [closed]

I am trying to track training expiration dates and can't quite get the formula to work. Here is what I am attempting to do; the spreadsheet is set to update with the current days date anytime ...
1
vote
1answer
19 views

Can I (and If so How?) use the MATCH function to search two (or more) different spreadsheets for a value?

First I'll explain what I already have (which works), then explain my question, which is about expanding it: I have two different spreadsheets that will be worked on at the same time, I needed to see ...
1
vote
1answer
38 views

How to sum every nth row with Excel

How can I sum every nth row, without having to press Ctrl + Shift + Enter keys So I am aware of a formula like this =SUM(IF(MOD(ROW($A$1:$A$250),2)=0,$A$1:$A$250,0)) But that requires that you ...
0
votes
2answers
62 views

Excel dynamic named range ignore blank formulas

In column A I have a formula that collates two other columns together. =IF(ISBLANK(B5),"", B5&" "&C5) Then in the Name Manager, a dynamic named range to use for a data validation drop down ...
-1
votes
1answer
19 views

Finding Number of Items Present on Given Date [closed]

I have a series of start dates and end dates like so: 01/03/2015 01/07/2016 01/04/2015 01/04/2016 02/05/2015 04/08/2016 What I'd like to be able to do is count how many in this ...
3
votes
1answer
30 views

Excel lookup to assign level based on Age and Weight

I have a table of some 5,000 players with their age and weight. I have a level table where I have created the max weight for each age and assigned a level. The arrays in the lookup table are named ...
1
vote
1answer
42 views

How to mirror a column that has merged cells?

I'm working on a "consolidated grid" spreadsheet. It has sheets for each project and then a main grid that lists statuses from each sheet. Everything works great unless the project sheet happens to ...
0
votes
1answer
29 views

Excel Pivot Table - How can I return a date in the row value?

I have raw data that includes hundreds of employees who have completed many different training courses and I am needing to insert a pivot table that shows the course name as the column labels, the ...
0
votes
1answer
26 views

Can I use the output of a cell as the cell location in another formula?

Here's what I have: Cell D13=AN and Cell AE13=13 Cell K13 has this formula =D13&AE13 so K13 outputs "AN13". From there I would like to have a formula that grabs K13's output(AN13) as a cell ...
0
votes
1answer
87 views

Excel - find MAX value in multiple row ranges based on several criteria

I need help with worksheet function for searching and displaying the maximum value in excel. I have 3 input and 1 output columns defined as follows: *COLUMN A: contains numerical values (mostly ...
0
votes
1answer
110 views

Timesheet calculation for employees on public holidays in Excel

I need help with setting up an Excel timesheet for monthly salary calculation. I am using a free timesheet template I've got from the Internet, but I need a few more functions. So the hourly salary ...
0
votes
0answers
15 views

Merging spreadsheets with same columns, some data same, some dup and some needs merged [duplicate]

I have two very large identical spreadsheets. Both have data populated. They both have some data missing here and there, in many cases it's different elements, there is some duplication and needs ...
0
votes
1answer
29 views

Excel - Format cell showing always the first digit

So my question is simple, but I have trouble solving it. I want the cell to show always the first digit, no matter how long the number is - for example if I type 0123 - it displays 0123, and if ...
0
votes
0answers
37 views

Excel - Sort the data by number of matches, then by highest to lowest order

I have previously asked the same question, but the answer I had is based on array formula and slows down Excel when working with the large data and works for only numerical values. Example: Cells A1 ...
0
votes
2answers
26 views

Excel 2010 - How to push values into many cells based on a single cell

I have what can be seen as a questionnaire with up to 60 questions (all yes / no). If someone answers Yes, for example, in cell B1 I know questions in cells B6, B9, B22, and B50 will be NO. To be ...
0
votes
1answer
18 views

Reference worksheet by cell value?

I have a formula that references a value on another worksheet. Something like this: =('sheet1'!C5) However, I don't always know what sheet it will be on. I have another cell on the main sheet ...
0
votes
3answers
48 views

Excel formula for calculating work days based on number of resources

I have an Excel spreadsheet cell which looks as follows: A | B | C | D | E 1 TASK | WORK (days) | RESOURCES | PLUMBER ...
0
votes
1answer
23 views

How do I transfer a running total sum to another sheet in excel?

Here is my problem: Sheet1 contains a row A-I of info. I want the column of I, to sum automatically in B3 on Sheet2. column I has data starting in I3, down. I only have a few lines of date (I10), ...
1
vote
1answer
30 views

Excel count unique values if second criteria is also satisfied

I currently have a formula to count all unique values of a range, however I would like to expand this count all unique values, if a second column meets a second requirement, for example. A ...
0
votes
2answers
34 views

Count cells based on a comparison with value in the same row of another column

There are many questions about counting cells based on conditions, but I can’t quite find a way to count cells when a value in another column, in the current row, is less than / greater than etc. the ...
0
votes
2answers
50 views

Return multiple values without using an array formula

I want to use an Excel formula to lookup multiple values, but I'm not able to use INDEX or SMALL, rendering most array-formula solutions unfortunately unusable. An example scenario is below: 1 sheet ...
0
votes
1answer
24 views

How to stop Excel from creating “phantom” zeroes when copy/pasting

I'm running Excel for Mac, 2011 and dealing with a fairly large data set (~500 rows, 75 columns) and a lot of the data was entered with the incorrect signs (+/-) for entire columns. There are also a ...
0
votes
0answers
18 views

Google Sheets - Selecting latest date across multiple columns for a row that matches a criteria

Here is the spreadsheet setup Now I'm creating a report where I can enter the Member_# in cell K1 and the latest date from columns D, F, and H appears in cell K2 as well as the test label from the ...
0
votes
0answers
32 views

Need simpler way of displaying data without merging cells (Excel)

Unmerged cells: See attached screenshot. I want to show multiple limits/attachments/premiums for a single Insured/broker/industry. I've tried merging rows (see below) for Insured/broker/industry ...
1
vote
1answer
44 views

Calculating the average Interest Rate using a range of date inputs in excel

I need to calculate the average interest rate for a calendar year, based on a table of exchange rates which change at various dates. The table simply shows the start date and end date of the set ...
0
votes
2answers
33 views

Excel Formula Repeating Not Desired, Assistance Requested

Alright so I have a problem that is probably fairly simple to fix but I can't seem to figure it out. My problem is I can't figure out how to make it only display the count once not as many times as ...
1
vote
1answer
31 views

Stack Ranges into 1 Column

If I had two columns by 4 rows: A1 B1 A2 B2 A3 B3 A4 B4 I would like the result A1 A2 A3 A4 B1 B2 B3 B4 I've used choose function to combine different ranges before ... but it doesn't seem to like ...
0
votes
1answer
18 views

Excel Formula - How To Collapse Empty Data?

I'm using the formula below to create / compile data from other cells to form a description: =H2 & "<br><br>" & I2 & "<br>" & "Other Details: " & "<br>" ...
0
votes
0answers
2k views

Finding occurances of combinations of several values by row

I've really gotten myself in a pickle this time. Background: We made a survey where people got a multi-option question to "make their own favorite salad", prompting <6 choices from a list. The ...
1
vote
2answers
63 views

How to use an IF within a VLOOKUP function

How do I use an If inside a Vlookup function in Excel to look for if the problem status matches between two sheets knowing that a status column in each sheet differ in terms of letters. For example ...
0
votes
1answer
33 views

Detect If New Line Is Started In Excel?

I was just wondering if there is a way to detect if a new line(hitting enter to start a new row) can be detected in an equation and then create another blank row as to give space between two items. ...
-1
votes
4answers
39 views

Need Help Creating an Excel Formula To Compare UPCs

Ok so I have tried to create a formula to compare a list of UPCs that get scanned into another sheet I have on the same document and this is as far as I got. =MATCH(A1,Table2[[#All],[UPC]],0). ...
1
vote
1answer
34 views

Summing a List of Values

I can easily sum a comma-separated list of values with a formula like: =SUM(11,13,17,23) I want to do the same thing with the list in a separate cell, so if A1 contains: 11,13,17,23 I would like ...
-2
votes
1answer
59 views

Excel how to sum all cells with the same name

I have a workbook with 30 or more sheets. Specifically, each sheet may be an invoice, change order, etc. such that the format of each sheet is not the same as the next one. The named range Total on ...
0
votes
1answer
25 views

Conditional Formatting help in excel (highlight certain row if a cell in a column displays #N/A)

I am trying to use conditional formatting to highlight a specific row of cells if a certain cell displays #N/A. I tried to use the function = isna() but it did not work as it only highlighted the ...
1
vote
1answer
43 views

Weighted average, ignoring blank cells and avoiding the #DIV/0 cell error before sell has value in Excel

As stated in the title, I am having issues coding for a weighted average (in this example, if the client has bank savings and an annuity, I want the estimated return percentage average to take into ...
0
votes
0answers
58 views

Getting certain cells that have 'x' in each row and getting the header row cell

I am trying to copy things from one spread sheet to another. I was wondering if there was a faster way than doing it manually. I have posted a picture of a sample I'm currently working on. Looking ...
3
votes
1answer
63 views

3 column dependent drop down list

I have a 3 column table of data and I need to create drop down list based on these 3 columns. Column 1 has duplicate values. Column 2 also has duplicate values. The options are dependent on the ...
2
votes
1answer
31 views

MS Excel: Add the contents of the column A to the cell B, if column B is empty

how can I add the contents of the cell A to the cell B, if cell B is empty in MS Excel 2010? My situation in detail: Column A (column with some empty and some non-empty values): AD Column B (no ...
1
vote
1answer
52 views

why am I getting compile error when trying to copy data from master sheet to multiple worksheets

I am very new to VBA and macros so I apologise if this is simple. I have found similar posts but nothing that I am able to adjust to my problem. I found code to create a new worksheet for every new ...
1
vote
1answer
44 views

Excel - INDEX MATCH query

Hoping someone can help me! I've worked myself out a cricket scoring spreadsheet, which works absolutely fine - trying to sort out a summary page, where you see (funnily enough!) a summary of the ...
0
votes
1answer
24 views

Transfer only certain columns in excel 2007 to another sheet

I have an excel 2007 sheet I am using as my product database. It has various columns and rows with data as below: Code Company Image Code Picture Page UV001 UV Gear uv001.jpg ...
0
votes
1answer
34 views

Multiplication of cells containing words

I'm making a finance plan, where I have the columns 'Quantity', 'Cost' and 'Total'. The quantity column will contain how many of the item I buy. The Cost column will have the individual cost of the ...
1
vote
2answers
80 views

How to optimize a VBA function in Excel

I wrote a function in VBA and have provided a simplified version below. Basically, it takes an argument, preforms a vlookup on a named range in the sheet using the value of the argument, passes the ...
1
vote
2answers
24 views

Find total points for survey responses with associated point values

I have the results of some survey in Excel. Each row represents the answers of one person in text from some predefined answers (e.g "often" "always" "not so much"). I want, at the end of the row, to ...
0
votes
1answer
28 views

Excel sum with “criterias”

I would like to calculate the number of days pending/pended. There are three days column. Date issued, Date approved and today(). How to calculate the number of days pended if there is a value on ...
1
vote
1answer
42 views

VLOOKUP for latest non-empty cell

Basically, I've got the following spreadsheet. There are dates along the top. I do a certain amount of past papers each week, and record the highest score I get in a week in cell (paper, week). The ...