Conditional function for Google Spreadsheets

Question

How do I create a conditional formula for Google Spreadsheets where:

If the value of cell A1 is an even number, I want cell B2 to display the value of A1 * 2
If the value of cell A1 is an odd number, I want cell B2 to display the value of A1 + 1

Here's a basic blueprint for what I want to do:

function isEven(input) {
    if(input % 2 == 0) {
        return true;
    }
    else {
        return false;
    }
}

function conditionalFormula(input) {
    if(isEven(input)) {
        //Make cell B1's value equal to A1 * 2
    }
    else {
        //Make cell B1's value equal to A1 + 1
    }
}

Do you want to run this function or call it from within the Spreadsheet? — Robin Gertenbach
– Robin Gertenbach, Commented Oct 18, 2016 at 7:59
@RobinGertenbach I want to call it from within the spreadsheet — Haque1
– Haque1, Commented Oct 18, 2016 at 8:06
In that case can't you use the formula approach? Otherwise calling the function from a cell you can (and must) let the function return the value, not set it from within the script — Robin Gertenbach
– Robin Gertenbach, Commented Oct 18, 2016 at 8:11
I want to use a script because it's a cleaner approach for me and I want to use that know-how to do other things — Haque1
– Haque1, Commented Oct 18, 2016 at 9:24
In that case you just need the function to return input+1 or return input*2 respectively. — Robin Gertenbach
– Robin Gertenbach, Commented Oct 18, 2016 at 9:40

bluemonki · Accepted Answer · 2016-10-18 08:01:16Z

3

Can you not just use a combination of the built in ISEVEN and IF statements to achieve this?

i.e in cell B1 write:

=IF(ISEVEN(A1),A1*2,A1+1)

You can then copy and paste it which should automagically increment the cell index.

answered Oct 18, 2016 at 8:01

bluemonki

913 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Suyash Gandhi Over a year ago

This is correct! No need to write any script when Google Spreadsheet has so many inbuilt formulas to achieve what you need.

Karl_S · Accepted Answer · 2016-10-18 12:20:43Z

0

If you want to run this down a column, use the ARRAYFORMULA function so you do not have to copy it to each cell:

=ARRAYFORMULA(IF(ISEVEN(A1:A),A1:A*2,A1:A+1))

will give you a 0 if there is no value

=ARRAYFORMULA(IF(ISNUMBER(A1:A), IF(ISEVEN(A1:A),A1:A*2,A1:A+1), ""))

Will only multiple the value if the cell in Column A is a number.

In the above examples, the formula goes in row 1 and applies to the entire row. Change the Range from A1:A to A2:A20 if placed in row 2 and working on rows 2 through 20. Both formulas using A1:A will put a value (a number or blank element) in every cell in the column which contains the arrayformula. The A2:A20 version will only put those values inside cells 2-20.

answered Oct 18, 2016 at 12:20

Karl_S

3,5922 gold badges24 silver badges38 bronze badges

Collectives™ on Stack Overflow

Conditional function for Google Spreadsheets

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related