# Questions tagged [worksheet-function]

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

**0**

votes

**2**answers

14 views

### EXCEL SUM Rounding

Hello to all Super Users!
I would like to ask about one interesting thing I found today related to sum function within rounding in excel. I have the following set of numbers (they are rounded to full ...

**0**

votes

**0**answers

10 views

### Using a hyperlink to copy cells and insert the copied cells into a new row under the current row, including the hyperlink

I have a spreadsheet with a number of completed rows. Each row has been created as a template for subsequent rows. I would like to be able to click in the first cell of each row and trigger VBA that ...

**0**

votes

**1**answer

24 views

### Highlight ONLY THE FIRST of all min/max values in a row/column in Excel

I'm trying to THE TITLE, that would be the general question; in my specific situation I need the minimum value in each row of a matrix, using conditional formatting of course.
I've only been able to ...

**0**

votes

**2**answers

29 views

### In excel how can I sum all the cells above the current cell upto a particular cell

What formula should I apply to add all the cells above the current cell but only up to a cell with a particular text.
Like in case of the below example the formula adds all cells above it but only up ...

**0**

votes

**0**answers

14 views

### Creating Excel Financial model

I have created a model that calculates the monthly cost of a property and would like to return the annual cost in a table below it. What function would you use to return an annual value that could ...

**-1**

votes

**0**answers

11 views

### Excel page hidden the bottom edge [on hold]

Unfortunately
having same issue with my window 2016, and I am using excel as must of work is in excel, so I have hold down Ctrl + Alt then select F10 each time I open a file, its very annoying but I ...

**0**

votes

**1**answer

33 views

### Percentage calculation formula

I have two questions and I just can't figure it out. I have trying to calculate 3% into a base rate.
$28.54 + 3% = $29.40
The goal really would be to do:
$29.40 - 3% = $28.54
However, when I do ...

**-1**

votes

**0**answers

33 views

### Formula not Working After Date 12 for every month

I came through a very weird issue. In Image1 You can see I am converting Column B to TEXT in a specific format in Column D. And in Column E I am getting the Time Hour Value. (Ref - Image 1)
But my ...

**0**

votes

**3**answers

42 views

### Simple Array Formula in Excel not comparing the right values

I'm trying to calculate with an Array Formula (in Cell D2) what i'm calculating in cell D11. The issue i'm having is that it appears my Array Formula is not evaluating the inputted array one-by-one. ...

**0**

votes

**2**answers

60 views

### Advanced F&R Regex - Sheets / Excel - If “X” is in cell A1 and add “Y” to cell B1

I am looking for a formula or regex that I can use to find a value in a string in one cell and if found add another string to the adjacent cell in another column.
For example, I have a list of ...

**0**

votes

**1**answer

59 views

### Excel vlookup not working

I am using a Mac.
I have 3 categories of ZIP Codes, Zone 1, Zone 2 and Zone 3. I have a list of ZIP Codes and I need to see easily if a zip code is zone 1, 2 or 3.
Column E holds my ZIP Code. ...

**0**

votes

**1**answer

18 views

### Add a CountIF to a SumProduct Formula

In a previous question, Scott gave me this great formula to sum all the sales from salespeople who saw an increase in sales from 2017 to 2018:
=SUMPRODUCT(C2:C9-B2:B9, --(C2:C9>B2:B9), --(B2:B9>...

**0**

votes

**1**answer

52 views

### Excel formula matching sheet1 and sheet2 and getting result

Can someone please help on Excel formula:
I have a spredsheet With two sheets.
In sheet1 I have two column
In sheet2 I have tree column.
I want two column from sheet1 to match With two column in ...

**1**

vote

**1**answer

14 views

### Cell joining, bracketing, and rounding in one excel formula

I am trying to combine three functions in one formula that will achieve the following:
Join columns C & D
Put parentheses () around column D value
Round column D to one decimal place
I can ...

**0**

votes

**1**answer

27 views

### How to Add a 2nd Criteria to an Index Match Formula?

I know this has been asked before, but I'm not able to make sense of other people's examples.
Here is a formula I have that works:
=INDEX('[AL DOE.xlsx]DOE'!$E$2:$E$5000,MATCH(LARGE('[AL DOE.xlsx]...

**0**

votes

**0**answers

28 views

### Reconciling differences when data repeats in a sheet

In the example data, say we have Company 1 and Company 2. Under each company is the state in which employees work, followed by the name of the employee(s) which work(s) in that state. In the column ...

**0**

votes

**1**answer

49 views

### Complicated 'IF' function in Excel

In Sheet 1, I have the following:
If the Due Date is after the Date Submitted, then I want the value in column D of this sheet to be the Detail Status, which is on Sheet 2,
UNLESS the Detail Status ...

**0**

votes

**1**answer

39 views

### Fill Sheet1 ID with value of ID from Sheet2

im new here and i have a problem with filling data from another sheet.
Sheet1 looks like this:
_|___A___|___B___|___C___|
1| 1 |John |1,35,12|
2| 2 |Derek |45,2,1 |
and so....
Sheet2
...

**1**

vote

**0**answers

22 views

### In Numbers/Excel how can I do a SUMPRODUCT only on rows matching a specific criteria

Let me explain my spreadsheet a little bit. Here is a good screenshot of it:
I have created this spreadsheet essentially because I have gotten tired of the usability issues of just about every ...

**0**

votes

**0**answers

13 views

### How does one find out what weight data was used in samples of same weighted averages?

How to find what weight was used in columns of weighted averages? (the column data is known, the weighted average for each column is known, the weights used are known to be the same for each column of ...

**0**

votes

**0**answers

13 views

### Production Schedule Row Formating

I maintain a production schedule using excel that is constantly modified and updated with new customer orders, completed work, cancellations, priority changes; the average row height format is set to ...

**0**

votes

**1**answer

17 views

### Excel Formula - Match Duplicate and Highlight with different color

A to D column with title List-1,List-2,List-3,List-4 and "Main List" A12 to A106 (original "Main List" DATA I did cut/paste here for color showing purpose)
There are random name showing in A2 to D10 ...

**0**

votes

**1**answer

67 views

### How do I perform a calculation in rows that meet several conditions?

I have two columns of numbers: a 2017 column and a 2018 column. Some cells are blank, the others have numbers.
(Or some cells contain zero and some cells contain positive numbers.)
I want to know ...

**0**

votes

**0**answers

16 views

### Combining mismatched data with specific criteria

Example Data
I am trying to turn ABCD into HIJK. from another forum i understand now =IFERROR(VLOOKUP(H1,A:B,2,FALSE),"") will find unique values, and i just apply this to both lists to find unique ...

**0**

votes

**0**answers

17 views

### How to calculate a weighted average using sub columns in a pivot

I'm trying to figure out how to get average sale for a parent category using it's children's figures.
Example raw date:
Example Raw Date:
Incorrect Pivot Calculation:
Incorrect Pivot Calculation:
...

**0**

votes

**2**answers

31 views

### How to compute weighted sum in excel?

I have a table below and want to compute a sum of products of a value of an element multiplied by a square of a distance to that element.
For example, for F(1) it would be sum( SUMSQ(A1-A(i),B1-B(i)) ...

**0**

votes

**1**answer

52 views

### Find maximum value for each group and retrieve associated value from another column

Please see sample data in attached picture.
I need to extract a list of Dept for all maximum values in CountOfDocs, for each unique MatterNumber.
So basically, given the data in the picture, my list ...

**0**

votes

**1**answer

36 views

### Align two columns in excel based on 1 criteria

This has me stumped.
if you have data that looks like ABCD:
How do I get it to HIJK?
I am trying to put 2 pivot tables next to each other, so I can compare the two sheets, but when I do it ...

**0**

votes

**1**answer

15 views

### Named Range Changes when cells are shifted up even though it is locked

I have a named range of
=OFFSET('Student Information'!$B$8,0,0,Lists!$B$3,1).
When the row is shifted up in a later routine this range will change to
=OFFSET('Student Information'!#REF!,0,0,Lists!$B$...

**0**

votes

**0**answers

7 views

### using checkbox with data validation

Hi Wonder if somebody could help ,
cell "A1" contains a data validation list with names, when I put a "Y" in cell "B1" I need the name in "A1" to change to a blank entry in the list.
the idea is ...

**-1**

votes

**0**answers

17 views

### How to stop Excel 2016 from tracking cells formats or content? [closed]

I use excel 2016 as was possible in the early days when it was a great self-programming and thought tool. I build my own formulas, used them in my chosen content, and do not expect excel to try to ...

**1**

vote

**1**answer

18 views

### How to grab Excel value based on first iteration of another value?

I have an Excel sheet that looks like this:
Event City | Sale Date
Los Angeles | 6/19/18
Los Angeles | 6/25/18
Los Angeles | 6/29/18
Phoenix | 6/20/18
Phoenix | 6/27/18
Phoenix | 7/14/18
...

**-1**

votes

**1**answer

18 views

### Excel spreadsheet, is there a way to have a easily inputted value such as the letter x to denote a certain value for each cell in a column?

Im running a scavenger hunt. There are 30 teams and 100 "tasks". each task is assigned a point value based on its difficulty. This make a potential for 3000 task to be completed in a 5 hour span. a ...

**0**

votes

**1**answer

19 views

### Write Values That Are Present in Both Ranges

How do I coax excel into writing the values present in 2 ranges into another column? In the example below it would omit "q" because it's only present in one range.
Column A Column B Desired ...

**0**

votes

**0**answers

18 views

### Google Sheets Auto Fill a cell when dates are entered in 3 other cells [migrated]

I'm trying to create a way to keep track of our jobs by creating a google spreadsheet. We can only order our final product when the digital and hard copy proof is approved and we have an order from ...

**2**

votes

**2**answers

45 views

### Stuck in the excel formula for duplicate removal

=IF(COUNTIF('PRDA Data'!S$2:'PRDA Data'!S2,'PRDA Data'!S2)=1,'PRDA Data'!S2,"")
This formula is used to eliminate the duplicate from another sheet. Now the issue is Duplicates are removed from the ...

**-2**

votes

**0**answers

16 views

### How to call multiple images from folder in excel?

Hi Excel experts & VBA wizards,
Is there an easy way to call images from folder in Excel using a formula or VBA?
Also, I would like images in R2 to be tied to the campaign names in R1. If the ...

**1**

vote

**1**answer

54 views

### COUNTIFS and SUMPRODUCT are giving different results

Context: I am working on an Excel 2010 spreadsheet, summarizing a lot of data and deriving various results by comparing the contents of several columns. All of the details aren't important; I'll ...

**0**

votes

**2**answers

24 views

### How to merge Excel worksheets by stacking columns, using an ID column?

I have a Worksheet #1 containing:
ID VALUE1
----------------
ABC 8274
DEF 613
GHI 236
and a Worksheet #2 containing:
ID VALUE2
----------------
ABC 11.13
XYZ ...

**0**

votes

**1**answer

62 views

### Need conditional formatting on cells that already have a subtraction formula

I have a column (let's say column X) that subtracts dates in 2 other different columns (let's say S and T) to tell me by color of cell fill if something was delivered on time or late.
In addition, I ...

**0**

votes

**3**answers

32 views

### How do I force excel to display (potential) expressions as text, instead of attempt to evaluate them?

I've got an excel worksheet that contains (string) cell values like this
=-L
=-N
=-R
etc
Now, excel sees the "=" character and attempts to evaluate the expression. It cannot, and displays the ...

**0**

votes

**3**answers

39 views

### editing cells , excel

wonder if somebody could help,
cell B2 has a vlookup formula in it, I want to be able to type "Y" in C2 and the information displayed in B2 to be either colour blocked out or erased, keeping the ...

**-1**

votes

**1**answer

34 views

### Excel IF returning 'FALSE' rather than cell reference

I want to write an IF statement that returns a cell reference, and have entered the following statement:
=IF(B125="1",E124)
(I'm using several other IF criteria in the same formula, but it is the same ...

**0**

votes

**0**answers

35 views

### Moving selected cell data in excel right or left without using cut + paste or, copy and paste. Move data with arrow keys would be great

Hi I would like to know how to run a VBA or macro (or any other simpler way) to shift the values only of any current selection (varying from one cell in one row to multiple cells and selections across ...

**0**

votes

**1**answer

31 views

### How do I perform VLOOKUP or INDEX MATCH based on three different criteria?

I can't figure out the proper formula to lookup a value based on three different criteria. I want to fill out the summary table with data from the source table.
I tried using INDEX, MATCH and ...

**0**

votes

**1**answer

33 views

### Sequential conditional formatting

I hope someone can help with this as my knowledge of excel is relatively limited.
I want to apply conditional formatting to a spreadsheet (see screenshot)
On each individual row, I want to show the ...

**0**

votes

**3**answers

89 views

### Excel formula for cumulative costs in 5 year increments

I had a previous problem that was solved that was similar, but wasn’t a running total:
Excel formula for declining number, in 5 year intervals
So this number is about budge forecasting. It needs to ...

**-1**

votes

**1**answer

61 views

### How to look up the last occurance of one of 4 recurring symbols from a row that includes various other data?

Per the attached image, I am attempting to create a formula that would accomplish the following:
Look up the most recent of four recurring characters in a row of other text. Screen shot of the four ...

**0**

votes

**0**answers

27 views

### Excel match will not find true match

I've encountered a very annoying issue with my excel spreadsheet.
I'm ranking a bunch of values and attaching points to the values based on their rank, but consistently, the lowest value cannot be ...

**1**

vote

**1**answer

23 views

### How do I set a data cap in a SUMIF function?

I want to create a single formula that combines the two formulas together:
=IF(E1>500,500,E1)
and
=SUMIF(A1:A10,"Y",E1:E10)
Basically, I want to sum items from columns E1 to E10 when the value of ...