Tag: cell

google sheets – Return a blank when cell reference is blank

This formula will work to keep blank cells from returning “0” to another cell. 

=IF( AND( ISBLANK(A1)=true , isblank(B1)=true ) , "dontsayzero")

Then, you can just remove the dontsayzero after you’ve tested it, but LEAVE THE QUOTES! Also, you can make things more sophisticated adding more “IF” and “OR” statements. Check this one out (remove carriage returns but not commas)

=
IF( AND(ISBLANK(A1)=true,ISBLANK(B1)=true) , "bothblank",
IF( OR(ISBLANK(A1)=true,ISBLANK(B1)=true) , "atleastoneblank" , 
IF( AND(ISBLANK(A1)=false,ISBLANK(B1)=false) , "neitherblank")))

This formula looks at the two cells and tells you their status. Should be pretty straightforward. This could be used if you had some data, but not enough to make the final calculation and didn’t want the zeroes or errors to mess with your further analysis. Just remove the stuff inside, leave the quote signs, and you’ve got a blank cell for any of the three situations. Moving further, if you want a different operation done in those three situations, remove the quote signs and insert your operation after the comma. There’s probably something more sophisticated out there as well. Good luck!

google sheets – How to Apply Conditional Formatting Based on Adjacent Cell Value for Entire Column?

I am looking to apply conditional formatting to all cells in a column (excluding the header) where a colour will be applied depending on if its value is greater, equal, or less than the adjacent cell.

Simple example:

A B
1 100 200
2 50 150
3 80 20
… … …

Here is a simple spreadsheet that is more complex implementing @Rubén’s solution:

https://docs.google.com/spreadsheets/d/1pl_UOyyE8r-JOcGWM2zsA2fFAbuvH3EUg7YnonaTZqY/edit?usp=sharing

Range B:B has the following custom formulas:

  • =B2>=A2 for green (if the cell on the right is greater than or equal to the cell on the left)
  • =B2<A2 for red (if the cell on the right is less than the cell on the left)

If the value in column B is greater than or equal to the value in column A, colour the B cell green. If the value in column B is less than the value in column A, colour the B cell red.

So rows 1 and 2 should be green, and 3 red.

I am familiar with how to do this individually on a per cell basis, but if I have hundreds of rows and need to do this over multiple columns I don’t want to manually do this hundreds of times. It looks like I need to use the Custom formula is on Conditional formatting rules, but I can’t figure out how to target a relative cell by position.

What I’ve tried:

  • Conditional formatting on one cell such as Great than or equal to $A2, then dragging this row down hoping the 2 increments. It doesn’t. This also wouldn’t work in scenarios where there are unique formulas or static values in the cells without going back and updating the cell values.
  • Using the Custom formula is, but unable to find the correct function to pair it with to again make that 2 value increment. Looked at MATCH and other descriptions of functions and didn’t see anything suitable for dynamic cell.
  • Looked at using the Script Editor, but this didn’t seem right and seemed like overkill for what I feel is pretty simple.
  • Googling, but somehow not finding anyone who’s wanted to do this before. Stack has questions that are close, such as Conditional Formatting based on other column that also use the MATCH function, but I wasn’t able to make this work.

How to export an output in a special cell in an excel file?

My general question is: How can I put each of my outputs (a number) in a special cell of a given sheet of an excel file? for example I have built an excel file (say results.xlsx) and I want to fill it by my outputs (say out(i)s). For example I want to put out(1) in B5 cell of first sheet of results.xls, out(2) in C10 cell of second sheet of results.xls and etc. How can this be done?

We can simplify the above problem: suppose I have built a table (say outs) using my outputs and export it as an excel file () as follows

out(1) = 1;
out(2) = 2;
out(3) = 3;
out(4) = 4;

outs1=Table(out(i), {i, 1, 4});
Export("results.xlsx", outs1, "Data")

this command gives me an excel file which has a row including values of out1.

I run another .nb file and do the same namely I build another table (outs2) as follows

out(5) = 5;
out(6) = 6;
out(7) = 7;
out(8) = 8;

outs2=Table(out(i), {i, 5, 8});

Now I want to put outs2 as second row of results.xlsx file. How?

google sheets – Display a list of names if today’s date equals a date in any adjacent cell on the same row

It is likely that your list can be generated in several ways (and probably more efficiently than the following answer). Please consider this as one alternative. I hope that I have not misunderstood your requirements; if so, then please let me know.

Insert this formula in Cell I3 (based on the sample data shown below):

=query({B3:H7},"select Col1 where Col2 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col3 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col4 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col5 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col6 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col7 = date '"&text(B1,"yyyy-MM-dd")&"'")

Note: cell B1 contains this formula:

=today()

Sample

Sample data

google sheets – Count of each number where the cell to the right is 1 number higher in an 8 column, 25 row grid

Table:

1  2  3  5  6  8  9 11
2  3  6  7  9 10 11 12
1  2  4  5  6  9 11 14

Output:

1 2: 2
2 3: 2
3 4: 0
4 5: 1
5 6: 2
6 7: 1
7 8: 0
8 9: 1
9 10: 1
10 11: 1
11 11: 1
12 13: 0

I’ve tried countif w/ AND, countifs, vlookup, filter, as well as various methods of concat. I haven’t found any method that seems to get me in the ballpark. I don’t think Sheets has any function similar to Excel”s (or was it 123?) @cellpointer {RIGHT}. Grrrr. I’m just looking for a starting point as nothing I’ve tried has gotten me in the right direction.

The closest I’ve gotten is

=AND(countif($K$20:$R$512, $AL3), countif($K$20:$R$512, $AM3))

(Where AL3 = 1 and AM3 = 2) (repeated down in secondary table to capture count for each combination)

How do I automatically add a number (say 100) to any input value in the same cell in Google Sheets?

How can I automatically add a number to any value entered into a cell? I want to be able enter any value into a blank cell, say “5” and have it automatically add, say 100 to that input. So I enter “5” and it becomes “105”. Is this even possible? Or is it too easy? I realize that just adding the number in my head before entering it is really simple, but now I have to know if I can be lazier.

The reason is, I have a line graph chart with two y axis on either side. One side is between 200 and 300, the other is between 1000 and 4000. I want to add a third series of numbers that would be between 0 and 100, but it won’t appear on the graph in either of the Y ranges. I’m thinking that, instead of remaking the graph, I just add 200 to any number entered in that series so it shows up on the graph. I don’t care about the accuracy of the numbers as much as the visual of the line over time. I realize how simple it is to add 200 to any number between 1 and 100, but I don’t want to have to explain this if I share this sheet.