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

learn more… | top users | synonyms (5)

0
votes
0answers
6 views

Need a formula for attainment of 75% 100% and 125% from 50% payout to 200% payout [on hold]

need a formula for 75 attainment you get 50% payout for 100% you get 100 and if you attain 125% you get 200%. Thanks.
2
votes
1answer
25 views

Excel Vlookup two conditions

Need to search in tables, found to exactly same lines in these two tables. Between these two tables I have working VLookup search, but need to have condition "AND these columns are also equal on ...
-3
votes
1answer
19 views

I need some help in excel validation [on hold]

I'm not good in excel. I need to include an excel sheet in my page, and I need an expert who can write these custom formulas for cells validation. 1) A column that accepts only letters, no numbers or ...
1
vote
1answer
24 views

How do I count multiple words available in sentences and multiple rows (countif, search)?

here is screenshot for the sheet. https://flic.kr/p/qZb8ot in column A there are some senteces list below: A2. T-shirts, Varsity A3. T-shirts, Sticker, Mug A4. T-shirts A5. T-shirts, Pin, ...
2
votes
1answer
39 views

What would be the the mathematical equivalent of this excel formula? =PMT()

I need to create a javascript form that does the same calculation as this =PMT() function. mortgage = 220000 rate= 4.75% term = 30 The example formula I have is =PMT(4.75%/12,30*12,220000*-1) What ...
2
votes
2answers
16 views

Excel 2013 - Dynamically reference table by table name

The TLDR I am looking for information on how to dynamically reference a table in a formula by name. To be clear, I'm talking about an actual table object, not a named range, and I want to choose ...
1
vote
0answers
8 views

Excel Index Match with IFs?? + options for next in list

OK I have been working on this for some time and can't quite get things right in the various combinations I have tried, so I am asking for advice. I have a management data table where column C is a ...
3
votes
1answer
29 views

Excel formula to get first and last non-empty value in a row and return column header

Let's say I have an Excel spreadsheet aggregating votes in some poll over a period of time as per the example below. How do I find the min and max value people have voted on on a given day (the ...
0
votes
1answer
40 views

How do I display name in column2 dependent on value in column1?

I am doing an inventory for all the businesses of a street. I have a two sheets, where on one I have different categories for commercial uses. Each category displayed in sheet 1 has a number (1 to ...
1
vote
0answers
14 views

Select elements in an array that satisfy a criteria

The goal: Kappa-Sigma clipping in Excel I want to implement a selective linear regression formula in Excel (avoiding VBA). I have a data sample on which I need to apply a linear regression. But my ...
0
votes
0answers
40 views

Daily Calculation Average [on hold]

I have a calculator that I created for my teammates. The calculator asks for 3 pieces of data that outputs 1 piece of data. The output is then copy and pasted into a daily tracker by the user. The ...
0
votes
0answers
17 views

Spreadsheet finding and sorting most frequent appearance of names

I have a spreadsheet that looks like the following: https://docs.google.com/spreadsheets/d/1BN3GNRFCsBeHu9gQaKzIo7bfFRkcDqMJ6VMtidEwPD4/edit?usp=sharing There is a set list of names in Column A. ...
-1
votes
0answers
16 views

Excel- How to use lookup formulas to look up on part of the content of a cell [on hold]

I created a table with a searchable drop down list that gives you all the prerequisites for the class you choose from the drop down list. I chained it so that it gives you up to five prerequisites ...
0
votes
0answers
18 views

Formula to search two columns of a database and return value from different cell in the matching row

I am designing a spreadsheet for scheduling appointments. On one sheet I have all the data but the dates and times are not in chronological order. I am having trouble writing a formula that will ...
0
votes
1answer
33 views

Searching for a value across 2 tables in Excel 2010, returning yes or no

I have one workbook with 3 separate worksheets Sheet 1 contains 2 columns. Column A has a range of unique alphanumeric identifiers. Column B has a range of non-unique numbers. A B ...
0
votes
1answer
23 views

Frequency formula in Excel 2010

I am just trying to figure a way to get the frequency of occurrence of a number within a large array, not the freq of the number in the array belonging to a range. Eg, how many times does the number ...
0
votes
0answers
14 views

Excel automatically applying styles to new data

I'm having a problem with Excel 2013. I have a spreadsheet filled with names and related information for applicants. When I contact the applicants, I highlight the cells (using the fill tool) so I ...
0
votes
0answers
17 views

Excel locked columns

-Is is possible to lock a column, for example column F, and then set it so if i scroll from column E, all other columns will appear before the F column. I am trying to set F up at the end of a spread ...
2
votes
1answer
33 views

Excel averageifs multiple criteria on one range

I know how I can use average(if(... etc using an array, however I'm stuck on how to include other criteria in this This is what I've got so far: ...
0
votes
0answers
12 views

Can Excel automatically create an x-axis range?

I'm doing a basic financial analysis with Excel. The gist of it is I have an input parameter (discount rate) plus a bunch of static data that gets fed into the =NPV(rate, x1...x_i) function. I would ...
0
votes
0answers
24 views

Add content from cell in sheet1 to cell in sheet2 when cell in sheet1 is found dynamically [closed]

Reworded for clarity per site request 1 How do you find a value in a list on sheet1, get the value in the column to the right of it and enter it into a cell in sheet 2, then add a number to it, then ...
1
vote
1answer
20 views

EXCEL - How to use two cells for input and output at the same time

I have 3 cells, VALUE, DOWNPAYMENT, DOWNPAYMENT %. The 3rd cell "DOWNPAYMENT %" calculates the percentage of "DOWNPAYMENT" over "VALUE" Is there a way for the user to have the function to input a ...
0
votes
0answers
18 views

Excell 2013 Calculating across Multiple Sheets number of fields

I am trying to work out the total number of fields across multiple sheets to arrive at the total number of new members each month. Some months have little as 50 others as much as 250+. I thought I ...
0
votes
2answers
23 views

Defining Variables in Excel

I have two lists of data, x and y, one in a row one in a column. I would like to populate a chart of rows and columns with a complex formula that is the same for each chart point, the only difference ...
-1
votes
0answers
20 views

How do I get the data in a cell of the last column of a row? [migrated]

I'm using Google Sheets. Every week I add a column with new data, and I need the data from the latest column copied into another sheet. The formula needs to pull the cell of the last column in the ...
1
vote
1answer
21 views

Get the cell number from double clicking on it and put it into another formula

As part of my ICT work we need to create a battleship game and one way I was thinking of doing it was using =IF(ISNUMBER(SEARCH("*x*",C3)),"HIT","MISS") to check if that cell contains x but ...
2
votes
1answer
19 views

Adding up amounts in a column for every non-blank entry in another column

I'm having a bit of trouble coming up with a formula to do the following. Say I have a sheet like so: X 100 1000 200 200 500 Y 400 1250 400 300 150 Z 430 910 330 ...
0
votes
1answer
37 views

How can I use INDEX MATCH formula with two horizontal parameters?

Not sure if anyone can help me but thought I'd give it a shot... I'm trying to create a formula to retrieve information from a data sheet. The data sheet contains a significant amount of information, ...
0
votes
1answer
31 views

Excel conditional formatting for repeating values

I have a table of data where there are 50 rows and many columns. Each column's value is sorted by the most important result at the top. These are names of people in sports. I would like to see ...
0
votes
0answers
16 views

Scores results needs updating as you type in the answer- excel lookup or similar [closed]

I have a two tables the first is a table where results get typed into Table Name Rounds 1 2 3 4 5 6 7 8 9 10 bonus Round played ...
0
votes
1answer
26 views

What Excel formula can create a string containing sequential numbers? AKA REPT() with a counter

Say I have column A with a number in it. I want to put a formula in column B that gives me that number of repetitions of a string, concatenated together, with the number of the repetition embedded in ...
0
votes
0answers
17 views

Issue Using Advanced Filter with Multiple Criteria

I'm trying to use the advanced filter to filter multiple criteria in two separate columns. The example I'm about to show is simplified so it is very important that we stick with advanced filter as ...
-2
votes
0answers
37 views

Get list from range of data

I first need a way to do this: "List every unique occurrence from range A and display it in Range B". I can do it but I would like to know if there is a way to do it with merged cells? Range A can ...
0
votes
1answer
26 views

Excel: How to count cells, whose date values represent a day between monday and friday?

I want to count cells, whose values are dates, under the following condition: The date value entered must be a day between Monday and Friday. Assume the following data and my try: Column A -- ...
1
vote
1answer
25 views

How to count number of unique buyers in excel? [closed]

I am creating a workbook for sold items of my company. and I have a list of items in column O of one of my sheets. now I want excel to count the number of total buyers and number of unique buyers. I ...
0
votes
0answers
3 views

Google Sheets list key with highest value in key value pair [migrated]

I have a some data in key-value pairs setup like so: A B 1 Key: Value: 2 Cars 5 3 Bikes 4 4 Vans 9 5 Trucks 6 What I can't seem to ...
0
votes
1answer
27 views

Double click on one cell to change text bold and font color of another cell on the same row

I have some code written that was given to me by another user, Twisty (who I seam unable to reply to in the page that was used), which is amazing and what I needed. However, I need it to do a little ...
0
votes
1answer
15 views

Microsoft Excel - Functions sum values after mulitplying multiple criterias after looking up tables

have provided a link to the excel worksheet that I need to resolve. I have worked up for two values in cell B9, which I need to do for other criteria which goes beyond 18 or so. Formula that I used is ...
1
vote
1answer
33 views

How to return a value from a range if cell contains that value

I have a list of Names (First and Surname) in A:A. I also have a range called 'Surnames'. How can I apply a formula so that B:B returns the surname only of A:A if that name is found in the range ...
1
vote
1answer
35 views

Advanced data consolidation in Excel

I need to Combine/Consolidate Data dynamically between over 20 separate Excel workbooks. The Column names and their relative position at the top row are the same. The positioning of each word under ...
1
vote
1answer
33 views

Excel 2010 - Need to sum dynamic range based on trailing 3 months

I am trying to sum a dynamic range for a list of entries 3 months before and 3 months after a specific month. I think I need to use an array to get this, but I'm not able to find what I need to do. ...
2
votes
3answers
44 views

Excel - stipulate an average for random number function

I am wondering if anyone knows if there is a way to use the RANDBETWEEN function to generate a series of random numbers in Excel but also make those random numbers come out to equal a specified ...
1
vote
2answers
38 views

How to categorize text data based on a value in a column?

I am using Excel to generate a list of people working in the company containing information about the employees relevant for our department. The spreadsheet looks generally like this (fictive data): ...
1
vote
1answer
27 views

Excel VLOOKUP with named range not working

I have the following VLOOKUP on a worksheet: =VLOOKUP(D5,FilePurposes,2)&" in respect of "&C5 Where the named range FilePurposes refers to a block of two columns and three rows that look ...
1
vote
1answer
34 views

Sumif & Indirect

In using the following sumif/indirect function (=+SUMIF(INDIRECT("'"&$A$3&"'!$B$2:$B$102"),$C13,INDIRECT("'"&$A$3&"'!F2:F102")) my columns (F:F) wont move as I drag the formula to ...
1
vote
1answer
30 views

Filter MS Excel table using formulas

I have created an Excel Table (using Excel > Insert > Table option) [[TABLE1]] |Name|Location|Salary| |A |CA |100 | |B |WA |200 | |A |CO |800 | |C |FL |350 | |C ...
1
vote
2answers
36 views

Increment numbers inside a text in microsoft excel 2007

I would like to drag cells downward and increases the number next to letters. It work when there is no letter for me. For example, I want to increment the number this way AG1 AG2 AG3 AG4 ... I ...
1
vote
2answers
20 views

When I copy table data into excel, it ONLY pastes it into column A! How to fix this?

If I use the following link: http://web.utk.edu/~dhouston/excel/exam1.pdf to copy the table and paste it into excel, it will only paste the values into one long column in Column A (and each row has ...
0
votes
1answer
46 views

How to combine data set to create unique combinations [duplicate]

First of all I don't have experience with Excel scripting, just basic formulas. I've seen this other question on SU but I'm not sure if that's exactly what I need or how to adapt it to my needs. ...
0
votes
1answer
47 views

How do I get Excel to identify a country from city list?

I have a list of data with many columns. In one column - let's say C, there is a list of cities/places. There are multiple entries, i.e. one city pops up loads of times in a column in different ...