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

**-1**

votes

**1**answer

31 views

### Excel data validation: restrict to 10, 20 etc

I am trying to use Data Validation in Excel 2010. How can I restrict entry into a cell to numbers divisible by 10?

**4**

votes

**1**answer

37 views

### Formula bar view changes when document is saved and reopened

I have an Excel 2007 document with a lot of formulas of the format:
SUMIFS(view1[COLUMN1],view1[COLUMN2],$C13,view1[COLUMN3],I$10)
but when I save the sheet and reopen it each formula is changed to ...

**1**

vote

**1**answer

29 views

### Manually editing Excel worksheet, need my edits to save as written in .csv without reformatting back

Here is my issue:
I have exported about 2000 records from a database to manually clean up the dates attached to them. (It appears that someone exported these records before, then re-imported them ...

**0**

votes

**1**answer

35 views

### Can I use conditional formatting to format a cell based on color in the cell itself and the cell before?

Conditional formatting Excel 2010 how to format a cell based on color in the cell itself and the cell before.
I have with Conditional formatting marked a start cell and end cell with red I want to ...

**1**

vote

**1**answer

31 views

### Higlight a specific cell based on ADDRESS() return value

I want to give a style (highlight) a specific cell based on a date value. This date is, say, in cell
A1.
To achieve this, I thought I would calculate the address of the cell I want to style with the ...

**0**

votes

**1**answer

30 views

### in Excel 2013, I want to have the column letters automatically increment in a formula

I have a table with multiple tabs.
On Tab "Reviewers", I have the reviewers' names in column A, and the items (documents) being reviewed in columns G through AG.
Each column contains the number of ...

**-1**

votes

**2**answers

34 views

### In MS Excel, what function should I use?

I have an original table:
Class 1 Class 2 Class 3 Class 4 Class 5
Ram Shyam Madhu Peter Xavier
Micheal Cynthia Helen Rama Hema
Peters Veronica Amy Sydney ...

**0**

votes

**1**answer

33 views

### How to use =sum function properly in excel 2007?

In the above picture, for the column G7, I wan sum of D7, E7, F7. So, I am using =sum(D7:F7), but excel is showing error for the formula by highlighting a green flag. If I ask it to correct the ...

**0**

votes

**1**answer

28 views

### link data from another .ods file

I'm using openoffice calc and I have 2 .ods files (complete.ods and summary.ods).
I have 1 cell from summary.ods which depends on complete.ods. I managed to get the value by using this formula:
...

**-2**

votes

**1**answer

63 views

### Excel - Need to get a figure from one worksheet to another (it's complicated) [duplicate]

I have an Excel workbook with 2 worksheets.
One worksheet is for the input of stock transactions, the other has an overview of the portfolio.
So on the portfolio worksheet I would like to have a ...

**1**

vote

**1**answer

26 views

### How do you create a bracket tournement sheet on excel [closed]

I am trying to create a bracket sheet that automatically moves a teams name to the next cell
example:
Team 1 in cell 'a1', Team 2 in cell 'a2'
The score for each team in cell:
'b1' for Team 1, 'b2' ...

**0**

votes

**1**answer

31 views

### How to find last row that has same value as my last one and output the number of that row?

I have a worksheet with stock transactions, so in column C I will input the name of the stock that I'm buying or selling at a given time.
.....A.....|....B....|...C....|
DATE | TYPE | STOCK
...

**0**

votes

**1**answer

19 views

### Hiding Date in Formula if nothing is entered

I want to have a Date forumla that adds 7 days to a date entered in another cell, but if nothing is entered I would like said cell to be blank
A1 if blank then have B1 blank
A1 is 2/1/2015 then have ...

**0**

votes

**1**answer

26 views

### MS Excel formulas displaying #REF when data is entered in a cell in a certain way?

I have various complected formulas pulling data from two pages (used as data entry pages), formatting it in specific ways, and displaying it on another third page (all in the same work book).
This ...

**2**

votes

**1**answer

101 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

**1**answer

35 views

### I need some help in excel validation [closed]

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

**1**answer

42 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

**1**answer

82 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

**2**answers

28 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 ...

**2**

votes

**0**answers

23 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

**1**answer

132 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

**1**answer

53 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

**2**answers

86 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

**1**answer

33 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. ...

**0**

votes

**0**answers

31 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

**1**answer

48 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

**1**answer

30 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

**0**answers

21 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

**0**answers

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

**1**answer

99 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

**0**answers

16 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 ...

**1**

vote

**1**answer

83 views

### EXCEL - How to use two cells for input and output at the same time [duplicate]

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

**0**answers

32 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

**2**answers

26 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**

vote

**1**answer

28 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

**1**answer

23 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

**1**answer

70 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

**1**answer

50 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

**1**answer

33 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

**0**answers

24 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 ...

**0**

votes

**1**answer

59 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

**1**answer

31 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

**1**answer

61 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

**1**answer

20 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

**1**answer

58 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

**1**answer

65 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

**1**answer

156 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

**3**answers

91 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

**2**answers

143 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

**1**answer

45 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 ...