formulas – How to show blank cells instead of 0s in Google Sheets?

I have 4 empty cells, and a formula in 4 other cells that show data from those cells. The formulas look like this –

=IF(B5 > C5, B5, C5)
=IF(B5 > C5, C5, B5)

==IF(B7 > C7, B7, C7)
=IF(B7 > C7, C7, B7)

The original 4 cells are all blank, and the type is all set to automatic. But the ones in row 5 show as 0s, and the ones in row 7 show blank. I want them to be blank until I put values in the original cells.

How can I change it so the values in row 5 return blank instead of 0s?

path finding – Java Game: A* Algorithm (searching only cells in front of character)

Game Type: grid tile map that is turned based

Directions allowed: Left, Forward, Right
(to reverse directions you must either use two lefts or two rights) – both left and right moves diagonal but change the face of the ship depending on original face)

Slots: Depending on ship size there are a certain number of slots for that particular ship for user to enter to allow the ship to move that many spots per turn (ie. 3 slots == 3 moves per turn)

Example:

Start Position: 2,2

Start face: North

Move Placed: Left

End Result: Position: 1,3 ; Face: West


Problem: algorithm uses all 8 tiles for path finding; but should only look for the tiles that are in front (depends on ship face)

Pathfinding calculation:

    private Comparator<Node> nodeSorter = new Comparator<Node>() {

        @Override
        public int compare(Node n0, Node n1) {
            if(n1.fCost < n0.fCost) return 1;
            if(n1.fCost > n0.fCost) return -1;
            return 0;
        }
        
    };

    public List<Node> findPath(Position start, Position goal){
        List<Node> openList = new ArrayList<Node>();
        List<Node> closedList = new ArrayList<Node>();
        Node current = new Node(start, null, 0, start.distance(goal));
        openList.add(current);
        while(openList.size() > 0) {
            Collections.sort(openList, nodeSorter);
            current = openList.get(0);
            if(current.position.equals(goal)) {
                List<Node> path = new ArrayList<Node>();
                while(current.parent != null) {
                    path.add(current);
                    current = current.parent;
                }
                openList.clear();
                closedList.clear();
                return path;
            }
            openList.remove(current);
            closedList.add(current);
            for(int i = 0; i < 9; i++) {
                if (i == 4)continue;
                int x = current.position.getX();
                int y = current.position.getY();
                int xi = (i % 3) - 1;
                int yi = (i / 3) - 1;
                int at = context.getMap().getTile(x + xi, y + yi);
                if(at == 1 || at == 2) continue; // ignore rocks
                Position a = new Position(x + xi, y + yi);
                double gCost = current.gCost + current.position.distance(a);
                double hCost = a.distance(goal);
                Node node = new Node(a, current, gCost, hCost);
                if(positionInList(closedList, a) && gCost >= node.gCost) continue;
                if(!positionInList(openList, a) || gCost < node.gCost) openList.add(node);
            }
        }
        closedList.clear();
        return null;
    }
    
    private boolean positionInList(List<Node> list, Position position) {
        for(Node n : list) {
            if(n.position.equals(position)) return true;
        }
        return false;
    }

Implementation:

@Override
    public void calculateRoute() {
        Position destination = new Position(3,3); // replace with cluster
        if(this.equals(destination)) {
            return;
        }based 
        path = context.getPlayerManager().findPath(this, destination);
        VesselFace face = getFace();
        if(path != null) {
            if(path.size() > 0) {
                int numberOfMoves = getVessel().has3Moves() ? 3 : 4;
                Position currentPosition = this.copy();
                for(int slot = 0; slot <= numberOfMoves; slot++) { //moves to enter
                    int positionIndex = (path.size() - 1) - (slot); //subtract slot to allow multiple moves
                    if(positionIndex < 0 || path.size() < slot) { // make sure it doesn't count too far
                        return;
                    }
                    Position pos = path.get(positionIndex).position;
                    Position left = MoveType.LEFT.getFinalPosition(currentPosition, face);
                    Position right = MoveType.RIGHT.getFinalPosition(currentPosition, face);
                    Position forward = MoveType.FORWARD.getFinalPosition(currentPosition, face);
                    if(left.equals(pos)) {
                        currentPosition.add(left.getX() - getX(), left.getY() - getY());
                        getMoves().setMove(slot, MoveType.LEFT);
                        switch(face) {
                            case NORTH:
                                face = VesselFace.WEST;
                                break;
                            case SOUTH:
                                face = VesselFace.EAST;
                                break;
                            case WEST:
                                face = VesselFace.SOUTH;
                                break;
                            case EAST:
                                face = VesselFace.NORTH;
                                break;
                        }
                    }else if(right.equals(pos)) {
                        currentPosition.add(right.getX() - getX(), right.getY() - getY());
                        getMoves().setMove(slot, MoveType.RIGHT);
                        switch(face) {
                            case NORTH:
                                face = VesselFace.EAST;
                                break;
                            case SOUTH:
                                face = VesselFace.WEST;
                                break;
                            case WEST:
                                face = VesselFace.NORTH;
                                break;
                            case EAST:
                                face = VesselFace.SOUTH;
                                break;
                        }
                    }else if(forward.equals(pos)){
                        currentPosition.add(forward.getX() - getX(), forward.getY() - getY());
                        getMoves().setMove(slot, MoveType.FORWARD);
                        switch(face) {
                            case NORTH:
                                face = VesselFace.NORTH;
                                break;
                            case SOUTH:
                                face = VesselFace.SOUTH;
                                break;
                            case WEST:
                                face = VesselFace.WEST;
                                break;
                            case EAST:
                                face = VesselFace.EAST;
                                break;
                        }
                    }
                }
            }
        }
    }

I use the switch statement and currentPosition.add() method so that when you place 3 moves for that particular turn; it knows where it should end up. Probably not best practice.

Statement that adds a move to the particular slot

getMoves().setMove(slot, MoveType.FORWARD);

Tiles that should be checked every turn based on ship face:

enter image description here

Sheets: Getting the slope of the 10 cells above current

I’d like a formula for Google Sheets, but I’m having trouble figuring it out.

Say I’m on B74, I want the equivalent of =SLOPE(B36:B63,A36:A63). However, I want something that will work even when I add a new row under 63. At which point, I want =SLOPE(B37:B64,A37:A64).

I used =AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-38,,28)) works perfectly to get the average value within this range, but I can’t figure out one for slope.

Google Script Sort Column Ascending Order with Blank Cells at Bottom

I have the below script running to sort column E in an ascending order. Each row is a different task and column E is the deadline. I have another piece of script running which deletes the deadline in column E once ‘Complete’ has been selected in another column. Hence, completed tasks will show empty cells in column E.

The deadlines are currently sorting from closest to furthest away, however E cells that are now empty are not moving to the bottom. I am a Scripts newbie and cannot see the issue. Any help to resolve this issue would be much appreciated.

function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh0 = ss.getSheetByName("2020");
  var sheet = ss.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 5;
  var tableRange = "A2:I998";

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
  }
  }

Google Sheets – Checking values for specific criteria based on another cell’s value

I have some raw data and I’m looking to create a “dashboard” of sorts that would list out the learner and whether or not they’ve completed a learning plan based on their worksite. In the “inputs” tab I’ve noted which learning plan (and thus which courses) is required per site. So, for example, if a learner’s worksite is “BP” then they should either have completed the learning plan titled ‘HC – Refresher Training – BP – V3 (Chinese Subtitles)’ or ‘HC – Refresher Training – BP – V3’ since those are the only two learning plans that can be assigned at the “BP” worksite per the inputs tab. I’m not really sure what steps to take to get to my end result, if that makes sense. Is there a way to set up this spreadsheet so that I am able to list out the learners and the progress of the entire learning plan (based on the inputs sheet) for each individual learner? I would also like this spreadsheet to be flexible. So, for example, maybe I’d like to display the progress of all learners with the worksite “BP” and list their progress with their current learning plan and a date (so that it can be sorted with the most recent dates listed first). My mind is just as flustered as this question so please don’t hesitate to ask follow up questions!

Quick Update: This formula sort of works

=IF(ISBLANK($G2),"",IF(AND(SUMPRODUCT(--($G2=Inputs!$J$2:$J$40))>0&SUMPRODUCT(--($A2=Inputs!$G$2:$G$40))>0&SUMPRODUCT(--($H2=Inputs!$H$2:$H$40))>0,$J2="Complete"),"Complete","Incomplete"))

G2 = worksite, A2 = learning plan, H2 = course title, J2 = training
status

The “inputs” reference is just a unique list so, essentially, the formula checks that the worksite, learning plan, etc, fall within values on the list (inputs tab) but the problem with this formula is it doesn’t necessarily identify if one of those courses isn’t complete. It’s only checking to make sure those courses listed are on the list.

graphics – Grid or TextGrid constant width cells

I am having problems with white spaces in lists displayed in Grid. The problem is that white spaces have less height and width compared to cells that contain text. I also tried none, None, None[], null, Null, Null[],nothing, Nothing. ” ” seems to be the only solution that is not visible. Spacings->{2,2} seems to be relative not absolute. it only adds padding to what is already there. how can I define constant height and width for all cells? and is there a better way to describe white space other than string ” “?

b={
{" "," ",38," "," "," "},
{" "," "," "," ",35," "},
{" ",19," "," "," "," "},
{" ",30," ",46," "," "},
{" "," "," "," "," "," "},
{" "," "," ",52," "," "}
};
Grid[b,Frame->All]

In Google sheets, how do I have a cell return the value of cell A, based on the values of cells B, C and D?

I’m trying to make a simple database that returns a value based on a number of different criteria.

Here is the Search Page they’ll use to access the data:

User search page

They choose from one or more criteria in row 2 (text types, up to three tags, or reading level); all other cells will be protected. I want their results to appear in a list from A3 down (I’ve already set up the sheet to populate the rest of the rows based on the data in the A column).

Here is the Database so far. I want the Search page to draw from column A, based on the criteria in the text types, tags, and/or reading level columns.

Database sheet

Any assistance would be greatly appreciated!

google sheets – Store first occurrence of “unique value” in the range cells (remove duplicates in text join)

Considering that some cells contain repeating values is there a way to get a result of unique occurrences.

For example from following range of cells:
enter image description here

Achieve the following output in target cell “9ll br1 8sd” (similar to what TEXTJOIN returns but avoiding duplicates)

Moving data from one range of cells to another with a script Google Sheets

First off, I wasn’t sure if this question should be in the webapps site or the programming one, but since this is primarily a google sheets question, I put it here.

Basically, in one sheet I have data in cells C14 through F15, so it’s 4 columns and 2 rows of data, 8 cells in total. With a script, I’d like to be able to “copy and paste” the values from these cells into B13:E14 on a different sheet.

I understand how to move values from one cell to another with a script, but I’m having trouble doing it with ranges of values.

Does anyone have any ideas?