Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [worksheet-function]

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

0
votes
2answers
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
0answers
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
1answer
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
2answers
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
0answers
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
0answers
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
1answer
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
0answers
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
3answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
2answers
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
0answers
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
1answer
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
2answers
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
1answer
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
3answers
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
3answers
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
1answer
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
0answers
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
1answer
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
1answer
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
3answers
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
1answer
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
0answers
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
1answer
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 ...