Help with formula...

[Steve]

I have two tables with many rows and columns. I want to set up a third table
that will calculate either the numerical inc(dec) in each cell or the pct
inc(dec). The user can toggle between numbers and percents based on what
he/she puts in an input cell.

The formula is easy: (if inp="percent", (c5-b5)/b5, c5-b5). Can I set the
format in that formula? I want to format as numbers, no decimal places,
comma separating thousands for numbers and percent with 2 decimals for
percents.

Ideas?

 

[Kevin B]

Something along the lines of this might work for you:

=(if inp="percent", TEXT((c5-b5)/b5,"0.00%"), TEXT(c5-b5,"0"))

 

[MyVeryOwnSelf]

I have two tables with many rows and columns. I want to set up a

third table that will calculate either the numerical inc(dec) in each
cell or the pct inc(dec). The user can toggle between numbers and
percents based on what he/she puts in an input cell.

The formula is easy: (if inp="percent", (c5-b5)/b5, c5-b5). Can I
set the format in that formula? I want to format as numbers, no
decimal places, comma separating thousands for numbers and percent
with 2 decimals for percents.

This looks like a job for Excel's TEXT function. It’s documented in Excel's
built-in Help. Each of the two alternative branches in the IF statement
would use TEXT with the corresponding "format_text" argument.