Questions tagged [worksheet-function]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
17 views

Copying cells for a larger dataset in Excel

My aim is to copy the same values for the same bank over different industries. Are there any functions in Excel where if you drag down and copy the same consecutive values depending on another string ...
  • 1
0 votes
1 answer
23 views

Finding and Displaying Unique Names

A bit of context: I am working on a short film, which requires a lot of props and assets that need to be made. To keep track of what I need and how each asset is progressing i have made a spreadsheet. ...
-2 votes
1 answer
39 views

Max value for multiple datasets in different orders

I need to find the max value of specific Cities on each sheet. So I would like to write a formula for Max of odd cities (City, 1, 3, 5). The tables are sorted by Salary, not by City alpha. (Cities are ...
  • 1
-1 votes
0 answers
30 views

#solved Formula - If multiple cells say no

I need to know how to do the below formula. IN CELL BZ14 IF Cell BN14,BO14,BP14, BQ14, BS14 and BT14 ALL say no. The result should be - NO LOGGED TRAINING. Please advise thank you
  • 17
0 votes
2 answers
39 views

Merge Rows and Sum Single Value in Excel

I want to merge rows with mostly similar data but sum their quantity values, using the unique "id" value while keeping the rest of the data in the row intact. The raw output looks like this: ...
0 votes
1 answer
17 views

VBA code to auto hide rows based upon text in cell A1

I cannot seem to get my code for this sheet to work properly. I have code in there functioning fine which will hide rows based upon whether cells in a range are empty. However I would like to ...
  • 1
0 votes
0 answers
10 views

How to use a specific formula in every row in a specific column now and in the future in google sheets? [migrated]

In Google Sheets I have a very simple formula to subtract the value in column A from the value in column B and show the total in column C. Column C formula: =(B:B-A:A)*24 I've selected column C by ...
-1 votes
1 answer
24 views

Excel: How to count how many times a consecutive text value appears

I'm making a work schedule and I have to pay attention to a specific rule that, no worker should be scheduled to work more than 6 days in a row. I got this spreadsheet where I make those schedules but ...
0 votes
0 answers
31 views

need advice two excel formulas

#SOLVED - =IF(AND(COUNTIF(b3:e3,"Yes")>=1,i3="Yes"),"Remove Online Module",IF(AND(COUNTIF(b3:e3,"No")=4,OR(i3={"Yes"})),"Complete Online ...
  • 17
-1 votes
3 answers
21 views

Excel: How To Transpose Every Random amount of N Rows From One Column To Multiple Columns?

I have a set of multiple choices questions with ABCD as answers, all answers are stacked on a column, but not every question has the same amount of answers. Instead of transposing every nth value, my ...
0 votes
2 answers
31 views

How do I convert a custom function to handle spill arrays (excel)

I need a function that produces a list of sheet names, I already have one that can turn sheet numbers into sheet names but it doesnt handle arrays The function I would like to convert is Function ...
1 vote
1 answer
33 views

Is there a formula for calculating the difference in minutes or hours between two times?

Is there a formula for Is there a formula for calculating the difference in minutes or hours between two times in two columns? In my spreadsheet I have the following two columns called start time and ...
0 votes
0 answers
21 views

Call a sheet using a variable name

I have this code in wich I add a sheet with a certain name and then I need to call this sheet to get a cell value but it doesn't assume. Dim nome1 As String Dim tableName As ListObject Dim addedRow As ...
0 votes
1 answer
36 views

updating SUM with latest data

I have a sheet that I am working on. It is a data caputure tool that will enable me to track the Value Added (VA) Score of student data vs their target. For example, a student with a target of 5 that ...
0 votes
1 answer
57 views

Excel Formula - Cell equals contents of another cell but display blank if the cell was empty

I often have several tabs on my spreadsheets but then have a main page collating data. I use the easy formula cell "insert cell" equals another cell "insert cell". If the original ...
  • 17
0 votes
0 answers
33 views

Food intolerance matrix in excel [closed]

I'm attempting to make a crude food intolerance matrix in excel. The matrix itself is simple enough, however the formula for ascertaining the number of instances where symptoms were found is ...
0 votes
0 answers
14 views

How do I convert this excel formula to work in google sheets? [migrated]

In excel I have this =INDEX(PayoutID,MATCH(TRUE,COUNTIFS(INDIRECT("'"&PayoutID&"'!$D:$D"),$A3)>0,0)) in B3 It looks at cell A3, then using a named range PayoutID (a list ...
2 votes
1 answer
31 views

Proper Syntax for AND OR NOT Excel Formulas

I was practicing some basic formulas in excel. The last 3 I worked on are as follows: =IF((OR(C4="Hourly",C4="Contract")),"Yes","No") =IF((AND(B4=&...
  • 23
0 votes
1 answer
27 views

MS Excel spot difference in a row, find which column the change is in and print out column header

First of all, Hi and thank you for taking the time to read this. I've been thinking of tangling this problem for a while now and decided now is as good of a time as any. The problem is as follows: Let'...
0 votes
1 answer
23 views

excel formula to get unique entries by week number

I have a excel sheet with a log of access dates,email addresses, and week number (calculated from access date). I can count the number of times the system was accessed the previous week using the ...
1 vote
1 answer
21 views

Is there a better way to count the number of records which fulfil conditions over two columns in Excel?

Sorry for the badly worded question, I'm struggling how best to put it into words. I'm looking to count the number of records in a database that are underage and have made an illegal purchase. The ...
-1 votes
0 answers
29 views

timestamp information when specific list value is chosen

okay so i am building an excel spreadsheet that will allow my users to input a scanned barcode on sheet 1. after that information is entered it needs to be saved onto sheet 2 with a timestamp of when ...
0 votes
3 answers
37 views

How to get coefficient from logarithmic regression in Excel

Suppose that I have two columns of data, C1:C5 as inputs and D1:D5 as responses, and I set a logarithmic trendline on the resulting scatterplot. As an example, set C1:C5 to 1, 2, 3, 4, 5; and D1:D5 as ...
0 votes
1 answer
11 views

How do I multiply a group of cells by a certain number, based on if they have a value in them or not

I want to have a cell (the K column, for allowance) multiply the number of days in it's row (the C-I columns, Sunday - Sat) only if they have a value, by 100. For example, if Monday, Tues, Wed all ...
  • 1
0 votes
1 answer
12 views

Combing cells in excel where Identifier and date match

I have data that is formatted as so; Identifier Date Currency figure ID1 01/02/22 100 ID1 01/02/22 99 And I'd like to transform that data so if Identifier and Date match in say Rows 2 & 3, ...
  • 101
0 votes
1 answer
45 views

Function to search for specific number and then to further search for the suffix

I have a huge amount of data to process in which 4 points with a related prefix needs to be subtracted from each other. Example on the picture attached. (let's call prefix a "ring number" ...
0 votes
1 answer
29 views

Excel COUNTIFS for CSAT ratings including zero but w/o blank cells

I have a set of data from customer survey ratings, 0-10, as well as data where no rating is given which reflects as blank. Column A has the agent name and column G has the rating. On another ...
2 votes
1 answer
33 views

excel sum range with two index matched values with multiple occurence

excel table Hi I'm trying to get a sum of 59 for 2 criteria and multiple occurence. See the picture. The problem with my current formula is that it only sums the first column mp1 the current formula ...
1 vote
0 answers
30 views

How to make an excel sheet dynamically reference another sheet

I am trying to make an excel sheet that generates material costs for construction. My plan is to have one sheet where I can enter the material and its quantity, and another that stores the prices of ...
0 votes
0 answers
28 views

Formula that depends on input from another cell?

I am attempting to write a formula where it subtracts the value of F from the previous F where G is true. Is it possible to have the cell know which cell is the previous true? for instance I want the ...
1 vote
1 answer
59 views

Take average of 90% of small duration with specific condition

I want to take average of 90% of smallest numbers from one column with below conditions Group1 = "Major Metro" and "VIP Metro" Group2 = "ACCESS" Severity= "Medium&...
  • 11
0 votes
1 answer
16 views

How to get Project Wise Summary numbers from one Google Sheet to another?

I am trying to create a Project accounting Google sheet. In one sheet I have all the expenses, revenues for the projects. (see below) and the other sheet will have list of projects with Summary of ...
  • 323
0 votes
1 answer
18 views

Highlight a cell when information in that cell's row is duplicate in 2 of it's columns

I'm looking for a conditional formatting rule that would highlight a cell if data in that cell's row is duplicated in more than one column. Specifically for this sheet, I want the cell in column B to ...
  • 1
0 votes
1 answer
37 views

Can't remove hidden characters?

I'm trying to make a simple tool for my job where I can decline job listings for a specific reason simply by checking their boxes and then copying and pasting the final cell where it will compile all ...
2 votes
1 answer
92 views

Using a date in an Excel function does not work when the date comes from another cell

I have the following problem: using a date as a condition in an MS Excel function works fine, as long as I write the date in the function code, such as ">1.1.2022". But when I write the ...
1 vote
1 answer
15 views

Show the smallest value for several conditions in Excel

I am trying to write a function that would show the smallest value for a given set A,B,.... and a subset of X,Y,.... I would like to represent this in the form of a table (red in the screenshot). I ...
  • 13
0 votes
2 answers
16 views

Find Multiple hits of the search value across all columns & rows of a table and return a list of the results from the 1st column of the table

I have a spreadsheet that holds a list of offices in the 1st column. Each column after this holds the list of Postcodes that this office covers (only the 1st part, ie se3). Some of the postcodes are ...
0 votes
1 answer
42 views

Finding the uses of a specific cell in Excel

I am working with a purpose-built excel software with 64 worksheets, a lot of named ranges and formulas. I want to target a specific cell and figure out how that cell is used in named ranges and ...
  • 103
0 votes
1 answer
21 views

Set a cell value based on duplicate rows in another column

I'm trying to identify trends for a text value. Basically, if column B repeats "Apple" three or more times, have a cell in Column G changed to "yes". Ultimately, I'd like it to ...
  • 1
-2 votes
1 answer
34 views

Calculate days to zero

I’m trying to see if there is a formula that can provide for the following question. If a tank has 120 gallons of liquid in it and loses .75 gallons per day, how many days until that tank is empty? Ex:...
0 votes
1 answer
37 views

X.Lookup fails in excel

Good morning, trying to create a x.lookup using data from a .txt file, copied to excel, using the text to column function to sort the data. small example below : 2.1 Dig. Oil Meter - Max. Oil ...
0 votes
1 answer
16 views

Drag down formula from multiple sheets on one sheet

I have 19 Sheets on each sheet in cell B17 I have a result. On my first sheet I have cell numbers from 1-19. In my first sheet I type in cell one the Formula ='sheet 1'!$B$17. I have the number in ...
  • 1
0 votes
1 answer
40 views

Using excel to find missing daily logs for people

I have an excel file with 2 worksheets. One has a list of daily logs and looks like this: Staff Person | Shift Date | Start Time | End Time John | Oct 31 | 8:00 AM | 4:00 PM John | Nov 3 | 2:00 PM | ...
  • 103
0 votes
1 answer
44 views

IF Function Not Populating Correct Data in Excel 365

I am completely stumped as to why this formula is not working properly. To provide backstory, I am trying to randomize a DMS grid coordinate, for example: 2339**01**N05005**51**W. I only want to ...
0 votes
4 answers
55 views

Simplifying a summation with VLOOKUP

Suppose there is an row like [100, 0.9, 50, 0.8, ...], I want to get a sum like 100×0.9+50×0.8+... Simply writing out each summation term seems very cumbersome for long rows, is there an easier way to ...
  • 3
0 votes
1 answer
46 views

Combining three formulas [closed]

I'm trying to combine three formulas in one cell but not having much luck, all work in separate cells. =K6/B6 =IF(B6="",0,G6) =IF(ISBLANK(B6),0,L6)
0 votes
2 answers
29 views

How to drag a cell reference formula down a column, but move the cell reference across a row

I am trying to drag a cell reference formula down a column, but have the row value increase as the formula is dragged down. For instance, in the shot below, cell B3 has the formula "=I3". I ...
1 vote
1 answer
49 views

Getting unique items from comma separated lists in Excel

I have the following setup (which is shared by a number of users through office 365 and changed frequently): I need to collect all unique items from a number of comma-separated lists in a range and ...
  • 13
0 votes
2 answers
34 views

Excel formula gives unexpected result

Each year I create a workbook within which there are several worksheets corresponding to each month of the year. I copy and paste the same table with formulas into each new month as the year ...
0 votes
0 answers
23 views

How to apply different formulas to column based on condition

I have been looking at how to perform these tasks for a while and couldn't find any help on Google. Background: I have an excel with different maintenance items for a product. Each item has a ...

1
2 3 4 5
153