Tag: cell

Google Sheets: How do I identify text in a column of cells and use a relative cell for addition if specific text is found?

I am looking to make a function that finds if a name (string) is in a particular cell, and then add the cell 2 columns over every time that specific name is found.

I think im looking for a type of if statement that identifies if any of cells from x to y have a specific string, then adding all the cells 2 to the right of every time the string is identified.

Specifically, I am calculating run differential in a baseball stat keeping sheet. I currently have it set up so The columns of the game logs are as follows: WINNER, LOSER, RUN DIFFERENTIAL. I am looking to create a function which finds the run differential each time that specific team played.

Example: if Team A won by 9 in their first game, the name of their team would be in the WINNER column, followed by the team they beat in the LOSER column, and then finally followed by the RUN DIFFERENTIAL (+9) which shows how much Team A, the WINNER, beat TEAM B by, the loser.

I think the idea is to first locate where the team won and lost. A team’s name in the win column indicates a win and is easy to identify, so my initial idea was to give a simple if statement for wins (If name is found in this cell, ADD the run differential which is 2 rows over, meaning they won by that much) and then sum whatever that number comes to with all the times they’re found in the loser column and the run differential of all those times, which would be how much they’ve lost by.

google sheets – Results of formulas in same cell

I have a weekly tracking sheet which I’m trying to get the result of 2 averages next to each other in the same cell.

Column B has a number value in each cell for each day of a week
Column G has a number value in each cell for each day of a week
In a cell in column A i want to display the averages of each of the cells from these columns for the last 7 days next to each other with a label.

The formula I have so far in the cell I want to display is:

=AVERAGE(C34:C40) &AVERAGE(G34:G40)

I’ve got it to display the values but all in a continuous string and the first average result has lots of decimal places after even though when it is in a cell by itself it shows as 1 which is correct, I can’t seem to change this?

I also want to add a label to each of the results with the final result in the single cell being:

Weight:result1 Calories:result2

Hope that makes sense if someone can suggest what I need to tweak to get the right display that would be great

thanks

How to randomly pick a cell out of a range and print its value in a different cell in Google Sheets?


Your privacy


By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.




worksheet function – Excel validation of cell as per other sheet

How can i validate excel cell from other sheet below is my sheet example want to validate Sheet 1 from Sheet2 as reference.

I have done validation with same rows using FIND , Search function from Right Left but other sheet have no idea seeking for help here

Sheet1

enter image description here

Sheet2

enter image description here

Filtering views based on a cell having a value

I am trying to sort out a spreadsheet which has multiple columns in but don’t really know where to start!

I am looking to filter the sheet where column C has more than 3 errors for the ID on column B.

Where it finds a result like this I need it to show all of the data, hopefully the screenshot below helps explain what I am trying to do!

Showing how I would like it to respond

Thanks

microsoft excel – Formula return cell defined name

I don’t believe there is a built in excel function that will do this. You could use a user defined function to return the cell name.

The two functions below will return the name of the cell. One takes a cell refernce, the other takes a row and column. Both are only set up to work on the local sheet right now but you could add an optional variable to take a worksheet name if required.

First function would be called with =cell_name()

Gordon

Function cell_name() As String
dim rng as Range
On Error Resume Next

set rng = activeCell

If Len(rng.Name.Name) < 0 Then
    cell_name = "No named Range"
    Exit Function
End If

cell_name = rng.Name.Name

if instr("cell_name","!")>0 then
    cell_name = Right(cell_name, Len(cell_name) - InStr(cell_name, "!"))
end if

End Function

second function returns cell name from a given row and column

Function cell_name2(r As Long, c As Long) As String
Dim rng As Range

Set rng = Cells(r, c)
On Error Resume Next

If Len(rng.Name.Name) < 0 Then
    cell_name = "No named Range"
    Exit Function
End If

cell_name2 = rng.Name.Name

End Function

Edited first function to work on the activecell.

google sheets – How Can I Take A Formula From One Cell And Display It In Another Cell?

When one cell references another that contains a formula, only the value is displayed. Is there a way to display the actual text of the formula in this second cell?

Here’s a simulated version of what I’m trying to achieve:

enter image description here

Cell A4 contains the SUM formula. Cell B4 displays the text that makes up the formula in cell A4.

Is there a shortcut to insert current datetime as a static value in a cell in Google Sheets?

I have a table like this:

Expense Date What it was for
120 CZK 4.5.2021. 12:30 Pizza coz I’m lazy

I use it to log some expenses, not exactly for pizza but that’s just an example. I also have similar tables where I really NEED both date and time.

I found out that Ctrl+; inserts current date, but without time. Is there also a shortcut for both date and time?

google sheets – How to use use cell references in the WHERE part of a QUERY / IMPORTRANGE formula

The second parameter of the QUERY functions is not using the correct syntax to concatenate values.

Instead of

"SELECT Col1 WHERE Col1 =" A3 "AND Col2 =" B3 ""

if Col1 and Col2 holds only number values use

"SELECT Col1 WHERE Col1 ="& A3 &" AND Col2 ="& B3

if Col1 and Col2 holds only text values use

"SELECT Col1 WHERE Col1 ='"& A3 &"' AND Col2 = '"& B3 &"'"

NOTES:

  • & is the concatenate operator.
  • QUERY requires that text values be enclosed using '.

google sheets – How to pick a random row that also does not contain text in one cell?

so I have a google spreadsheet full of youtube videos.
Column A looks if the video is already in column B, and if it is it says “duplicate”

Column B is youtube link

Column C is the title of the video

Column D is the name of the author

Column E is the genre (eg. car review, lets play, comedy…)

Column F is the lenght

And finally column G contains “x” as in I already watched it. And few times “unlisted”.

So I want to create a randomizer which picks a random link and all the info about it, but does not contain “x” – random video that i havent watched.

I came up with this =INDEX(B5:G; RANDBETWEEN(1; COUNTA(B5:B))). Works great as a randomizer, but it doesnt care about the filter I have set.

If someone has a idea how to pick a random link which does not contain a text in collum G and lists the info – title,author,genre ect., I would be very happy.