CODEWITHSUNDEEP
libreofficelibreoffice-calc
Matija
Matija

Reputation: 2720

LibreOffice Calc: Substitute number with another number, but only inside a function?

I have a row of numbers that looks like this:

enter image description here

What I would like to achieve is to have a function that will SUM all the cells from B2:F2 and show them in G2, but function should replace all occurrences of number 1 and replace them with number 12. So that when someone is looking at the table he sees number 1, but function should internally use 12 instead of 1 for its calculations. I should get 48 in G2.

How could I achieve this in LibreOffice Calc? Is it possible to place multiple "variables" that would get replaced with different numbers during calculation? Lets say I would like to replace occurrences of 1, 2 and X, with number 12?

Thanks!

Upvotes: 0

Views: 301

Answers (1)

JohnSUN
JohnSUN

Reputation: 2539

For first case formula can be like as

=SUMPRODUCT(IF(B2:F2=1;12;B2:F2))

It's simple: "If cell value in B2:F2 is equal 1 then calculate it as 12 else as value from B2:F2"

For second case you can use CHOOSE() function or (it's better) LOOKUP() function

=SUMPRODUCT(IFERROR(LOOKUP(B2:F2;{1;2;<your X>};{12;12;12});B2:F2))

Upvotes: 2

Related Questions