Hello everyone, i have following table
| | | 2021 | | 2020 | |
|---+-------------+------+---+------+---|
| # | Shoes | 7000 | | 5500 | |
| # | Handbags | 2000 | | 1500 | |
| # | T-shirts | 1000 | | 500 | |
|---+-------------+------+---+------+---|
| | Total sales | | | | |
and trying to come up with formula to achieve following auto calculation / formatting
| | | 2021 | | 2020 | |
|---+-------------+-------+-------+------+-------|
| # | Shoes | 7000 | 70.0 | 5500 | 73.3 |
| # | Handbags | 2000 | 20.0 | 1500 | 20.0 |
| # | T-shirts | 1000 | 10.0 | 500 | 6.7 |
|---+-------------+-------+-------+------+-------|
| | Total sales | 10000 | 100.0 | 7500 | 100.0 |
Ideally i’d like to avoid hardcoding column numbers and reference them by odd / even (cause i will be adding more annual data), but i don’t see way to do it.
1, Firstly i tried
`@>$<<<..@>$> = if($# % 2 == 1, vsum(@<<..@>>), @@#$$#) :: @<<$<<<..@>$> = if($# % 2 == 0, ($-1/$>)*100, @@#$$#)`
but received error that 2 ranges try to set @ 3$5 field,
(i inserted @@#$$# in if statement cause it didn’t let me to leave it blank for “else do nothing”)
2, So i tried
@<<$<<<..@>$> = if($# % 2 == 1, if(vlen(@>..0) == 0, vsum(@<<..@>>), @@#$$#), ($-1/@>$-1)\*100)
but now i can’t set ;%.1f formatting for only percentage columns
Could someone please help?