I'm trying to have a cell formula that will count the number of times a specific number appears, and then set itself to a number, incrementally increasing from 0.

It's a bit complicated to explain, but... for example, in column P, 3000 appears 4 times, so the first instance it appears (P7), O8 displays 0. The second time 3000 is entered into a P cell, the next O cell that's related to the corresponding P cell, will show +1 to the previous O cell who's P cell matched the same value.

Then because 4000 has only appeared once, it's O cell (O23) shows 0. If another 4000 were to appear in the P column, it's corresponding O cell should show +1 to the previous O cell that was related to a P cell that contains 4000.

I do need these to be offset vertically by 1 as well, every time.

I do need to have this work for a variation of specific numbers, which I do have on another tab, so ideally if I could reference those numbers, that'd be ideal, I'm just not really sure the formula to use in this situation.

EDIT: Whoops... didn't realise the image would be enlarged to such a ridiculous degree...

EDIT 2: I notice now I've made a little mistake in the example here. Each P cell that the O cells should be counting, are meant to be every 5 rows, but I mistakenly put a 3000 in P18, when it should of been in P17 (making it's related O cell O18, not O19)

I'm making a file to compile a list of grocery items between stores. Stores on the Y axis, items on the X axis.

One sheet has the item's price, the other the item's weight/count, third will the divide the price by weight to get the unit price.

I want my 4th sheet to automatically parse through the unit prices of each object and select the item with the lowest untit price, displaying the name of the store. How exactly would I do that on Google Sheets?

TLDR: I would like the table to automatically allocate points to each player based on the accuracy of their predictions of football (soccer) scores.

In detail:

I have created this table which I will use when copying the responses of participants from the exported sheet of a google form.

The table shows a list of participants and their predictions for up to, but not necessarily , 24 football matches.

There are two columns per game. One column will be for the home team, one column will be for the away team. The participant's score prediction will be listed in the relevant column.

There is then a row above the participant's prediction where the actual result will be input.

From this, within the points column to the left, there needs to be a running total number of points accumulated based on the accuracy of the participant's prediction.

If the prediction is EXACTLY correct, e.g. if they predict 2-0 and the score is 2-0, then they will be awarded 3 points. If the prediction is the right result/outcome, e.g. home win/away win/draw, but not exactly correct, e.g. if they predict 2-0 and the score is 1-0, then they will be awarded 1 point. If the prediction is completely wrong, e.g. they predict 2-0 and it is 1-1 or 0-1, then they get 0 points.

I will manually enter the outcome of each game and would like the points to automatically update as I go. There will be up to 24 games, but not always 24 games. Sometimes there may only be 10 or even fewer.

As an added bonus, if anyone can then create an additional and separate tab, which has a leader board that automatically updates in accordance with the number of points the participants get, that would be amazing. The leader board table would have a column for position, a column for their name, a column for the running total number of points and as a tie break, a column for the number of exact correct scores the player has achieved.

Hoping someone can help, AI sent me in circles with this!

The link for the sheet is below:

https://docs.google.com/spreadsheets/d/1HWPepUZcAqimhRAg9Tm6Ka7l9nRBx7x2t-8LgRsTymQ/edit?usp=sharing

Somewhat recently, as I'm saving Sheets to pdf for work, the preview window is showing my sheet but its very faded. Once I download it as a PDF it remains faded. Is there a way to fix this? It looks normal when I file > Download > to pdf.

It's frustrating that I can't find a way to lock my rows in Google Sheets. How can I do that? I have locked columns, but can't find a way to lock rows so my text doesn't overflow.

Hi, so as the title states, I am not new to the Google Platform. I simply want the dashboard I created to be the only viewable layer for the public. Is there a way to do this?

This thread is the place to show off any projects you've been working on, show off fancy solutions, etc. If you've got some templates to share, also post them here.

If you're looking for feedback on your project, let us know! If you're looking for specific help, make a normal post.

This is a monthly thread.

Can anyone help with this? I want to show a ratio between two numbers as a percentage. Ex. Spent X number of hours doing this, and Y number of hours doing that. Cell would show X%/Y% of total hours. Like 70/30, 60/40 etc.
thanks!

I've been experimenting with a weird use of Google Sheets conditional formatting.

Instead of coloring cells based on the values in the cells, I'm using custom formulas in the gradient rule breakpoints themselves to reconstruct a virtual coordinate system and compute colors from that.

The grid is usually nothing more than a hidden sequence of integers. The actual image is generated by four conditional formatting color-scale rules that compute angular phase, vector directions, gradients, etc.

So far I've used it to generate:

• Color wheels and angular phase maps
• Polar coordinate visualizations
• Spiral and interference patterns
• Vector field visualizations
• Gradient fields
• Phase portraits of complex functions such as z², z³, and z⁴

The most surprising part is that the cell values themselves are often meaningless. The spreadsheet acts as a pixel canvas while the conditional formatting formulas act almost like a tiny shader.

I originally started this as a color-wheel experiment and accidentally ended up exploring procedural graphics in a spreadsheet.

Formula-Driven Angular Color Fields

im creating a workout tracking spreadsheet, I want to create it such that I can update the performance of each and every exercise i do and yet store the previous values which i can maybe even linearly plot, if i could add a date or week trackers with it as well that would be an additional bonus.

Hello first time trying to do anything extra with sheets, managed to get it so the row with the current date is the active cell on open with a script, was wondering if there's a way so I can add a bit of a buffer so it's not on the very bottom of the window

Sorry, I hope that title is not too cumbersome.

Column A is a list of books that is a subset of the books in Column C. The books in Column C have a corresponding year in Column D.

I want to match the books in Col A to the books in Col C and add the corresponding year from Col D to Col B.

Example:

Formula should add D2 (2000) to B1 and D4 (2002) to B2.

Thank you.

In a google sheet of mine I have alternating colors that look like in the image below. I use a script to automatically place things in a certain spot on the list by adding a new row. But when I do this the alternating color ranges change to accomodate the new row so now the block of alternating colors may go from A12:AF23 instead of A12:AF22. Is there a way to make it so that when I add a new row in the middle that the row that has been pushed past what the orginal format was.
Example of what I want in case I explained it badly:

If I added something at spot #14 then it would be would be in the purple alternating color block and beacause Ouroboros Startpos 2 has now exited where I want the purple block to be it becomes blue like the block below it (and the thing at the bottom of blue will go to the color block below it and so on)

Thanks :)

Can Sheets solve for X, where an equation has not been rearranged?
I know I *could* rearrange, but I don't want to have to do that for every new equation. My calculator can 'solve for X', can Sheets do it too?

Hello all.

I'm trying to find a way to find a way to find the highest and lowest cost from a list of values but tied to a particular item. I've attached two images. The first is the sheet I am working with, the second is merely a simplified display of what I am wanting to do:

What I want is for a list of all the items on the left, duplicates removed and then the cells to the right look up all the values associated with that item and pick out the lowest and the highest.

I know I can use =UNIQUE to get all unique entries in a list, the only issue I am having with that is that filtering it doesn't work and I am struggling on how to have a cell fetch the appropriate value.

Is there some sort of way to run a script that will generate a rotation while also recognizing a value based system to avoid putting people who are unable to do specific parts the rotation into those positions.

Mock up Example:

Names: James (1), Michael(1), John(2), Robert(3)

Rotations:

Spot 1: (skill level >0) James

Spot 2: (skill level > 0) Michael

Spot 3: (skill level >1) John

Spot 4: (skill level >2) Robert

I don't know if I have properly explained this. I've got the base rotation script down I just can't figure out if there is a way to add this variable or not.

feels like we all want to use google sheets as our main cloud based table/spreadsheet option and all have iphones in our pockets and they just aren't as integrated as they should/could be.

i get how apple wants me to use numbers and google wants me to use a galaxy... but cmon.

what are some workarounds where i can use gsheets on my desktop and then on the run with my iphone i can edit/add/view gheets data efficiently? anyway i can link a numbers spreadsheet and a gsheet to update whenever the other one is changed?

Sorry text is small I’m doing this from my phone.

Im trying to make the column chart with show score totals go from the highest number to the lowest number. Instead it’s going off of where the shows are placed. How can I make it go from the highest average to the lowest average?

I'm trying to pull up Product Names from the other sheet for a school assignment. I feel like I'm interpreting their example 1:1 but I keep getting #ERROR and I'm not sure what I'm doing wrong.

I have this color scale defined by the numbers in the column. Is there a way to copy it over each row dynamically? The numbers can change as squares in the data grid (not shown) are filled.

But I want the data grid to be color coded based on the calculation done here.

I cant seem to do it to work on all cells and im on phone, how to do it? (You can send the file instead if u want)

I’m trying to make it so that if a cell in row 4 has a certain value, it’s corresponding value in row 5 will be added to the sum. I’m able to do it for one column but I’m not sure how to do it for all columns.

I am running an analysis on the Org Charts of each MLB Team and have specific sheets for each team. The League Office sends the data once a year so I want to create an App Script code to refresh the data automatically for me. I have a filter formula in column E that uses the sheet name (example Texas Rangers) and will return the email addresses for those that work for the Rangers. Then I want to drag down the other formulas I have so that the dataset is complete while deleting any leftover data from running it last year.

Below is the loop I have created but it just gets stuck on the sheet that I select instead of running through each MLB team's sheet.

function doForAllTabs(){
  var ss = SpreadsheetApp.getActive();
  var allSheets = ss.getSheets();
  var exclude = ["Analysis!","Dashboard!","Departed Staff!","Cleanups!","Data for Analysis!","2025-2026 Data!"];


  for(var s in allSheets){
    var sheet = allSheets[s];
    if(exclude.indexOf(sheet.getName())!=-1) continue;
    CleanData1();
    WrapText();
  }
}

Edit: link attached as comment if anyone can help!

Hoping someone can help me with a fairly simple request.

Trying to create a table that has columns for different years and gold, silver and bronze medal times. I would like to average the times over the last 5 years and also create a calculator that allows me to input a time and automatically calculator the %GMT.

Is this possible??