Tag Info

New answers tagged

1

Prepend the column letter and/or row number that you don't want to change with $. Let me illustrate: Let's say in cell B2 I have this formula =B1*A1 If I copy that to C2, what is pasted in C2 is =C1*B1 But that's not what I want. I always want to multiply against A1. If, instead, B2 was =B1*$A$1 Then when I copied to C2 I'd get =C1*$A$1 If I ...


1

If you have multiple sums in the formula, you need multiple SUM commands, one for each sum. That is, =F15*20+SUM(F16:F20)*25+SUM(F21:F23)*30 Have to add things up before multiplying them all by a common factor.


1

Try: =ARRAYFORMULA(transpose(split(concatenate(if(B2:D<>"", B2:D,)&char(9)),char(9)))) or: =ARRAYFORMULA(transpose(split(concatenate(B2:D&char(9)),char(9)))) or also: =ArrayFormula(transpose(trim(query(transpose(B2:D),,rows(A:A)))))


2

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 ...


1

Another way of doing it, is by using the AVERAGEIF formula. Formula =AVERAGEIF(A1:B1, ">0") Screenshot


1

I think you need the time() function. e.g: A1= hour B1 = minutes in C1, try: =TIME(A1, B1, 0)


1

Here's a function to do thisif a1>0 and b1>0 then average a1 and b1 else add a1 and b1 =if(AND(A1>0,B1>0),AVERAGE(A1,B1),add(A1,B1)) Obviously you'll have to plug in your own cells for a1 and b1 You can find all the google functions documentation here



Top 50 recent answers are included